Importing CSV With Common ID Field

[Marco]

I use proprietary software in my line of work that allows me to export client data into a CSV file. One of the fields in this CSV file is called Household ID. Let’s call this CSV file “Household Assets”.

In airtable, I already have a table called “Households” with has the exact same Household ID field and number for each household.

Is there any way to import the CSV into its own table in airtable and automatically link both tables given they already have a common field, Household ID?

This would be useful as I could simply import once per month all my household asset updates and somehow track the trend and progress of my client assets.

Any ideas how I might be able to accomplish this? It would be great if I could even somehow automate this process too.

[Aron_Korenblit]

Hi Marco,

That’s super simple luckily. After you’ve imported the CSV with household IDs, simply turn the field with the household IDs into a linked record! Airtable will do the rest!

[Marco]

That’s actually not working for me right now. I have my “Households” table already with a HH ID field with the appropriate number for that household. I then imported the CSV into it’s own table. It too has a field called HH ID with the value of those fields matching what’s in the Households table. When I change the field type to Linked and link it to my HOUSEHOLDS table, nothing really happens. The numbers change to show that it’s linked but it’s not really pulling the data properly.

When I click on the linked HH ID, all the Household fields are blank.

[Marco]

Looking at this more closely, I just realized what it has done is created all new rows within the table itself instead of linking the existing rows based on the HH ID. That’s not the desired effect. I was hoping there would be a way to link the rows in the IMPORT table to the HOUSEHOLDS table using the existing HH ID records/rows.

[Justin_Barrett]

For that auto-linking to work, the Household ID will need to be the primary field in the [Households] table. The fact that Airtable made new records leads me to think that your {Household ID} field is elsewhere in the table, not the primary field.

While non-primary fields can be reordered by dragging and dropping them, this won’t work if you want to move another field into the place of the primary field. You’ll have to copy whatever data is currently in the primary field into a new field, then copy the contents of {Household ID} into the primary field. Then the links coming from your other table should connect properly.

[Marco]

Okay that makes sense. The challenge for me is that not every household has a household ID.

Household ID is something that I use only for households that are clients. It’s an ID that is actually generated by my firm when someone becomes a new client.

Therefore, I would end up with other households in the table where I would have to create a fictitious household ID just to be able to use it as a primary field. I’m also wondering if there are any other consequences of side effects to using the household ID as the primary field instead of the current one which is simply the household name.

[Justin_Barrett]

One possible way to allow for households that both do and don’t have these unique IDs is to leave the household ID in its own non-primary field in your [Households] table, and make the primary field a formula. If a given household has an ID, the primary field will be that ID. If not, it will be the specified household name (from whatever other field currently has that name). Just spitballing with guessed field names, the primary formula might look like this:

IF({Household ID}, {Household ID}, {Household Name})

Households with their own ID will link correctly to your incoming data in the other table, and those that don’t won’t be affected.

[Abdulrahman_Alz]

We made a CSV importer for Airtable that adds rows to Airtable based on a unique field. Every time it runs, it checks whether or not there are any rows that exist in the CSV but not Airtable based on the value of the unique field.

miniExtensions

Automatically Import CSVs into Airtable - miniExtensions

Keep your Airtable and a CSV file in sync by scheduling this extension to run regularly. Supports up to 10 CSVs, and supports scheduling and webhooks.

[ajaymatharoo]

this is so frustrating to not have, wonder why is no one talking about this