How to Import Data into Airtable Without Duplicates

In this tutorial, you'll learn how to automatically remove duplicate records when you import data into Airtable. We'll use the free Data Fetcher extension and the REST Countries API to demonstrate how this works. However, you can use the same method with any other API or integration, like our Google Sheets Airtable integration.

Set up the Table

Let's get started.

1. Create a new table and name it "Countries".

2. Delete the "Assignee" and "Status" fields.

3. Rename the "Notes" field to "Code".

4. Add the following country codes under "Code".

• GB
• GB
• IT
• FR

We're including one duplicate country code in our data to demonstrate how Data Fetcher can remove duplicate records in Airtable.

The table is set up. Let's make an API request in Data Fetcher.

Add Data Fetcher to Airtable

1. Add the Data Fetcher extension to your Airtable base.

2. Sign in to Data Fetcher or sign up if you don’t have an account.

3. Once you've logged in, click Create your first request.

How to Import Data into Airtable Without Duplicates

Let's configure the request.

1. Select Custom under Application.

2. Rename the request to "Import European countries".

3. Copy and paste the following into the URL field:

https://restcountries.com/v3.1/region/europe?fields=name,cca2,population

With this URL, we'll import the name, two-letter country code, and population of European countries.

Tip: You can learn more about making external API requests in Airtable on our blog.

4. Click Save and Run in the bottom right corner.

Map the Response Fields to Your Table

After you complete the previous steps, Data Fetcher runs the request and displays the Response Field Mapping screen. On this screen, you'll select and configure the response fields to new or existing fields in your table.

Let's import the "Name", "Cca2" (two-letter country code), and "Population" fields.

1. Click Deselect All.

2. Select the following fields and map them as follows:

• Name common -> Existing field Name

• Cca2 -> Existing field Code

• Population -> New field Population

You'll need to scroll to the right to see the "Cca2" and "Population" fields.

Next, we'll turn on the settings that remove duplicate records in Airtable and learn how they work.

Update Records Based on a Unique Field

Data Fetcher has two write modes for adding data to an Airtable table: Append and Update. In append mode, Data Fetcher always adds response data to the end of a table without modifying existing records.

In update mode, it modifies existing records with the response data and adds or deletes records where necessary.

For update mode to run smoothly, we'll use the Update Based on Field(s) setting. This setting allows us to select a field with unique values, so Data Fetcher can correctly merge the response data with existing records in the table.

Let's turn the setting on:

1. Click the arrow icon at the top left of Response Field Mapping to open the settings sidebar.

2. Click Advanced settings

3. Under Update Based on Field(s), select Code. Country code is always unique to a country, so it's the perfect field to use to merge the response data with our table data. If you're using a different API, e.g. with IDs for each record, you should use that field instead.

4. Click Save and Run in the bottom right corner.

If you look at the table, you'll see imported data for all records except the duplicate "GB" record.

Data Fetcher updated the existing records without changing their positions and didn't import data for the duplicate record because Update Based on Field(s) is turned on.

If the setting was turned off, Data Fetcher would have imported the data into the table from top to bottom, starting with the first record and eventually overwriting the existing data. In general, you'll need to turn on Update Based on Field(s) if you don't want your existing data to be overwritten when importing data.

Note: Update Based on Field(s) works on your selected output view, not the whole output table. Make sure you choose an output view without any Airtable filters, otherwise Data Fetcher won't find the existing records!

We'll see how Data Fetcher handles duplicate records next.

Deleting Duplicate Records

Data Fetcher uses the Delete records not found in the API response setting to compare the records in the table's output view with those in the response data and remove any records that do not match. This setting works on duplicate records because Data Fetcher matches only one record in the table to a record in the response data.

Note: Delete records not found in the API response also works on your selected output view, not the output table. If you want to use this option on the whole table, you'll need to choose an output view without Airtable filters.

To turn on the setting:

1. Open the request in Data Fetcher, scroll down, then click Advanced settings.

2. Under the Update tab, turn on Delete records not found in the API response.

3. Bonus: before you run the request, add the country code for a non-European country like Japan (JP) to the top of the table.

4. Click Save and Run.

Watch the request as it runs, and you'll see Data Fetcher delete the duplicate record and the record, "JP", which it didn't find in the response.

Here's a summary of the steps we took to remove duplicate records when importing data in Airtable:

• Add Data Fetcher to your Airtable base.
• Create a custom request or use a predefined integration.
• Configure the request with the necessary parameters.
• Turn on Update Based on Field(s) using a field with unique values as an identifier.
• Turn on Delete records not found in the API response.
• Run the request.

That's all for this tutorial. Check out our blog to learn more about importing API data into Airtable.