Help

CSV Import Merge Issues

385 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jonathan_Gunne1
6 - Interface Innovator
6 - Interface Innovator

I have a list of records that's something like this. 

PROJECT NAMEPROJECT VERSIONCONTACTASSIGNEE
Apples1JoeBob
Apples 2JoeBob
Bananas1SallyJim
Oranges1JoeJudy

I receive a .csv report with the most recent information about the first three fields every week. The fourth field (assignee) lives only in Airtable. There are additional fields as well as notes and comments on the records that need to be maintained. 

The weekly report often includes changes. For example, a new "active project" may be added or something might change about the project, like the CONTACT field. 

Example .csv report:

PROJECT NAMEPROJECT VERSIONCONTACT
Apples1Joe
Apples 2Joe
Bananas1Jill
Oranges1Joe
Pears1Joe

What I'm trying to do is find a way to import this new .csv report and have Airtable update/merge the existing records that have changed (in this case, the CONTACT on Bananas project is now Jill) and add any records that didn't already exist (Pears project). 

The CSV Import extension only allows you to compare one field so the closest I've gotten to get this to work is to create a new formula field in Airtable that concatenates all relevant values:

PROJECT NAMEPROJECT VERSIONCONTACTASSIGNEEDupCheck
Apples1JoeBobApples1Joe
Apples 2JoeBobApples2Joe
Bananas1SallyJimBananas1Sally
Oranges1JoeJudyOranges1Joe

And I did the same in the report that I receive:

PROJECT NAMEPROJECT VERSIONCONTACTDupCheck
Apples1JoeApples1Joe
Apples 2JoeApples2Joe
Bananas1JillBananas1Jill
Oranges1JoeOranges1Joe
Pears1JoePears1Joe

This allows me to use the DupCheck fields to compare and it does find the records that change. However, I can't get them to merge records. It only creates new ones, which forces me to have to update records that have changed manually. In this example, the result would be

PROJECT NAMEPROJECT VERSIONCONTACTASSIGNEE
Apples1JoeBob
Apples 2JoeBob
Bananas1SallyJim
Bananas1Jill 
Oranges1JoeJudy
Pears1Joe 

I still have to comb through the results and find ones like the Bananas project, eyeball the changes, and then manually update the existing Bananas project with "Jill" as the new CONTACT, then delete the newly created Bananas project.

I suspect the reason the merge does not work is because I'm using a formula field to compare and Airtable obviously can't merge anything into that field. I need to map the DupCheck fields/columns to each other so it can use them to check for differences, but I don't need to actually bring in the DupCheck info from the .csv file. 

Is there a better way?

3 Replies 3

You've hit up against Airtable's native limitations regarding this issue.

I would recommend using Make's CSV file and Airtable sutomations to handle this.

I demonstrate how to do this exact thing in this episode of the BuiltOnAir podcast.

p.s. If you need to hire an expert Airtable consultant to help you with this, please feel free to contact me through my website: Airtable consulting — ScottWorld

I'll check it out, thanks. I've been trying my hardest not to leave Airtable and rope in another app.

One thing I just thought of but haven't tested yet, would be to create a new text field and have an automation copy the formula field (e.g., "Apples1Joe") into the text field (triggered by an update to the record, specifically watching that field I guess). Then I'd have a text field Airtable can use to compare with the csv concatenated field and it can go ahead and update it with the csv info upon merge. In my head that'll work. I'll test it if I get board today.

That sounds like that would work! The tricky part there would be making sure that the new text field is always up-to-date, but if it's up-to-date, that would be a good workaround to solve this problem!