Update a table with data from an Excel file

Hi ​@JLuc_Neve,

If you export the data from that Excel file as a CSV file, there are 2 different ways for you to accomplish what you’re looking to do:

1. Manual way of doing this:
   
   You can use Airtable’s CSV Import extension to import your data into your existing Airtable table.
   
   The extension allows you to “overwrite” your new values over the old values, as long as you match (aka “merge”) your records based on a pre-existing column, such as the “Department” column.
    
2. Automatic way of doing this:
   
   You can automate this process by using Make’s CSV integrations along with Make’s Airtable integrations.
   
   I give step-by-step instructions on how to do this on this Airtable podcast episode.
    

   If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread. For example, here is one of the ways that you could instantly trigger a Make automation from Airtable.

Hope this helps!

If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Hm do the Excel file and the Airtable data have a shared unique identifier for the Customer?  e.g. C120 or something that exists in both datasets?

If so, then Airtable’s CSV Import extension’s your best bet:

https://support.airtable.com/docs/csv-import-extension

You’d toggle on ‘Merge with existing records’ and then select that unique ID for the customer, and here’s a screenshot from the docs for how that’d look:

https://support.airtable.com/docs/csv-import-extension#configure-settings-and-field-mappings

If not, then I’d probably recommend trying to identify a value that is mostly similar on both datasets, importing the Excel as a CSV on a new table, and then attempting to manually link the two tables together I’m afraid

Hello Scott & Timesavingco,

Thank you for your reply.

I'm trying with the “CSV Import extension,” and it ALMOST works.

I'm having trouble specifying the “merge field.”

There is an automatic number in the table, but CSV Import doesn't recognize this format. And if I choose the “Names” field, it causes problems for customers with the same name because it replaces all of their data with that of the first customer.

How can I choose the right “merge field”?
 

Thanks,

​@JLuc_Neve 

The merge selection comes up higher on the screen than what your screenshot shows.

Check out the screenshot that Adam posted above.

- ScottWorld, Expert Airtable Consultant

Hmm, without access to both datasets it’s really hard to help I’m afraid.  My advice would be to find a unique identifier that exists in both Airtable and Excel

Any chance you can use the customer emails?

---

What field type is the ‘Num’ field in Airtable and what kind of data is it on Excel?  Is that unique and correctly assigned to the same customers on both datasets?  

If ‘Num’ is a number type field, try changing it to text, and if it’s text try changing it to a number type field instead and see if that helps

Thank you for your messages.

The “Num” field, which is a unique key, is in automatic number format.

It appears that this format is not supported as a “merge” field, even though it is the one that identifies the records.

I downloaded the CSV from the table, then modified and added a few records (without changing the structure), and when I try to re-import the updated CSV into Airtable and specify that the “merge” field is Num, it tells me that this format is not supported.

i can’t choose “Num” in the list.

Thank you.

Your Num field isn’t supported because you have it set as an autonumber in Airtable. Autonumbers in Airtable aren’t editable (and so your CSV can’t fill in anything there). If you change it to a regular number field in Airtable, then you can bring in your ID numbers.

In addition to that mentioned by ​@DisraeliGears01, when converting such field into a Number field do make sure that you format the field in the same way the numbers on the Excel file are formatted (periods and commas) to avoid issues.

For further context:

 

Mike, Consultant @ Automatic Nation

Hm, yeah that’s weird, seems we can’t pick Autonumber fields to be Merge fields.  Try duplicating your Autonumber field and converting it to a Number field and selecting the new Number field as the Merge field?

Thank you very much for your answers.

That is indeed where the problem lies.

So I have a solution that works (by duplicating the automatic number as a standard number).

That said, the fact that the “autonumber” primary key cannot be used for the “merge” seems strange to me, because that is precisely what it is used for. And having to duplicate it is a source of human error. airtable should update this. But maybe I don't fully understand.

Have a nice day.

Translated with DeepL.com (free version)