Cleaning a messy Excel Import for linked records

[GaymerTrav]

Hello everyone,

I'm looking for some advice/best practices for my situation.

My company has a weekly report that they dump into an excel but it's just screaming to be formatted as a database. I've started importing the data but there are some glaring issues once it's within Airtable.

Currently there is a {Name Field} formatted as "LastName, FirstName", but this field can vary as it relies on human input. I'm struggling with getting these individuals into their own table while also having Airtable recognize these names during the next import without creating fuzzy duplicates.

One saving grace is that each name has a unique ID number, and using this as the primary field makes this step very easy, but when we want to link that record in another table, we're unable to search by name and there are over 100 ID numbers to look through.

Is there any elegant solution to normalizing data such as this? If this was a one off import it would be a non issue, but this data will have to be imported each week and Airtable needs to be able to recognize the data in the linked fields.

Thanks for any assistance 🙇🏾‍♂️

[Saravanan_009]

To normalize the data and handle the varying formats of names in Airtable, you can use the following approach:

Primary Field as Unique ID: Keep the unique ID as the primary field in your table, as this will prevent any duplicate records when importing new data.

Name Field Handling: Create two separate fields: one for "First Name" and one for "Last Name".
Use a formula field to split the "Name Field" into "First Name" and "Last Name". For example, you can use LEFT() and RIGHT() functions combined with FIND() to split the names.

Linking Records: In other tables where you need to link records, create a linked field that connects to the unique ID but use a formula to display the name. You can do this by concatenating the "First Name" and "Last Name" fields.

Importing Data: When importing new data, set up an automation or a script to automatically format the names and link them to the correct records based on the unique ID.

This setup will help you maintain consistency and avoid duplicates while allowing you to search by name in linked records.

[ScottWorld]

You don’t necessarily need to switch your primary field to become the unique ID (i.e. the unique ID can be any field), but the unique ID should definitely be your unique identifier for each person.

One way that you can very easily automate the importing & updating of your data in Airtable is by using Make’s advanced automations for Airtable, as long as you export the data from Excel to a CSV file first.

I demonstrate how to use this technique step-by-step on this Airtable podcast episode.

— ScottWorld, Expert Airtable Consultant

[TheTimeSavingCo]

Hmm, you could try running an automation per imported record to attempt to link it to the correct contact via the ID number, but that's going to burn through a lot of runs and probably isn't tenable?

I'd recommend a script you ran once per import that'd help you link everything together, really

===
Really clunky, but what if you had two tables for Contacts?  The first table would have the primary field as the unique ID, and when you import the data you could just paste the unique ID into the linked field to the first table

The second table would have the primary field be the names, and you would populate this table via an automation that would run whenever a new record gets created in the first table

I.e.
1. Excel data imported
2. New unique ID is imported and pasted into the linked field to Contact Table 1, resulting in a new record being created
3. This triggers the automation to create a record in Contact Table 2, linked to the record in Contact Table 1

You could then pull the name data over from Contact Table 1 and use it in your primary field for searching purposes

If you've got a ton of contacts this wouldn't work either though as you'd hit the record limit