+4I have a file that I receive from my customer that I want to import then set up for my team to use. The issue is that the table comes in organization that does not work well in the airtable environment. Is there a way to pull in the data as truly just data then pull to different tables to build how I want and link fields.
Thanks!
Best answer by Bill_French
I understand exactly what you mean.
ETL (big boy process for smart imports)
The data (as imported) doesn’t really match the data model in your Base design. This is likely the result of an integration pipeline that you cannot control, perhaps related to legacy information. This is a common ETL (export/transform/load) challenge that all organizations face from time-to-time.
Script Blocks
This relatively new and advanced feature in Airtable is ideal for ETL issues. You are essentially asking for a companion import utility that reads the raw import data and reclassifies and shapes the ingested data to fit the data architecture of the Base app. Furthermore, you probably want to avoid importing the raw data altogether as that process will likely contort data values. The better approach - use a block to read the import file and move (and transform) the values directly where they need to be in the base app’s data model while establishing the required relational properties.
This can be a daunting challenge because you may need to instantiate links between relationships (that do not exist in flat files like CSV documents). You may also have data normalization issues, capitals, lowercase, date formatting, and even number conversions to think about.
Depending on the ETL requirements, I tend to avoid recommending glue-factory solutions like Zapier and Integromat. If the requirements are really simple, give these tools a try. But… before you try anything, I would map out the requirements at least in an outline. This will help you determine the most ideal approach and will also help you implement a solution how ever you may decide to build it.
Note - there are a number of free script block tools in this community that openly publish source code and include some of the transformation processes you’ll need as well as the ability to ingest data indirectly via a script block.
+2Hey Zak,
Can you explain what you mean when you say “comes in organization that does not work well in Airtable environment”?
What type of file format is it? Can you save that file as a CSV file? If so, you can import it very easily into AirTable.
Please share details and maybe I can help.
Hey there! I’d recommend using Zapier or Integromat here, as it sounds like you are hoping to automatically import data. Have you tried either of these solutions?
I understand exactly what you mean.
ETL (big boy process for smart imports)
The data (as imported) doesn’t really match the data model in your Base design. This is likely the result of an integration pipeline that you cannot control, perhaps related to legacy information. This is a common ETL (export/transform/load) challenge that all organizations face from time-to-time.
Script Blocks
This relatively new and advanced feature in Airtable is ideal for ETL issues. You are essentially asking for a companion import utility that reads the raw import data and reclassifies and shapes the ingested data to fit the data architecture of the Base app. Furthermore, you probably want to avoid importing the raw data altogether as that process will likely contort data values. The better approach - use a block to read the import file and move (and transform) the values directly where they need to be in the base app’s data model while establishing the required relational properties.
This can be a daunting challenge because you may need to instantiate links between relationships (that do not exist in flat files like CSV documents). You may also have data normalization issues, capitals, lowercase, date formatting, and even number conversions to think about.
Depending on the ETL requirements, I tend to avoid recommending glue-factory solutions like Zapier and Integromat. If the requirements are really simple, give these tools a try. But… before you try anything, I would map out the requirements at least in an outline. This will help you determine the most ideal approach and will also help you implement a solution how ever you may decide to build it.
Note - there are a number of free script block tools in this community that openly publish source code and include some of the transformation processes you’ll need as well as the ability to ingest data indirectly via a script block.
+4Hey Zak,
Can you explain what you mean when you say “comes in organization that does not work well in Airtable environment”?
What type of file format is it? Can you save that file as a CSV file? If so, you can import it very easily into AirTable.
Please share details and maybe I can help.
Hi KBD,
When I am saying it is coming in a format that doesn’t work well I am basically saying that the columns from the report I receive do not work well in airtable. For Example I deal with Vehicles. Best way to uniquely identify I vehicle is the VIN number or my other option would be the case number. The data I get from my customer is a type of vehicle in column one with VIN or case number in the following columns. When I import the file that vehicle type becomes the locked column that is not unique leaving me with data that is hard to use.
Zak
+4I understand exactly what you mean.
ETL (big boy process for smart imports)
The data (as imported) doesn’t really match the data model in your Base design. This is likely the result of an integration pipeline that you cannot control, perhaps related to legacy information. This is a common ETL (export/transform/load) challenge that all organizations face from time-to-time.
Script Blocks
This relatively new and advanced feature in Airtable is ideal for ETL issues. You are essentially asking for a companion import utility that reads the raw import data and reclassifies and shapes the ingested data to fit the data architecture of the Base app. Furthermore, you probably want to avoid importing the raw data altogether as that process will likely contort data values. The better approach - use a block to read the import file and move (and transform) the values directly where they need to be in the base app’s data model while establishing the required relational properties.
This can be a daunting challenge because you may need to instantiate links between relationships (that do not exist in flat files like CSV documents). You may also have data normalization issues, capitals, lowercase, date formatting, and even number conversions to think about.
Depending on the ETL requirements, I tend to avoid recommending glue-factory solutions like Zapier and Integromat. If the requirements are really simple, give these tools a try. But… before you try anything, I would map out the requirements at least in an outline. This will help you determine the most ideal approach and will also help you implement a solution how ever you may decide to build it.
Note - there are a number of free script block tools in this community that openly publish source code and include some of the transformation processes you’ll need as well as the ability to ingest data indirectly via a script block.
Thanks Bill for the help. I am going to try this today.
Hi KBD,
When I am saying it is coming in a format that doesn’t work well I am basically saying that the columns from the report I receive do not work well in airtable. For Example I deal with Vehicles. Best way to uniquely identify I vehicle is the VIN number or my other option would be the case number. The data I get from my customer is a type of vehicle in column one with VIN or case number in the following columns. When I import the file that vehicle type becomes the locked column that is not unique leaving me with data that is hard to use.
Zak
Yep - that’s what I thought and there’s no easy way to overcome this short of completely reorganizing the inbound reporting format. “Legacy” is code for - never gonna’ change. :winking_face:
BTW - changing how columns are mapped into Airtable from import sources is certainly possible in Zapier and Integromat. I don’t have skills in these tools, I can only suggest these glue-factory tools do this fairly well. It’s all the other nuances of shaping your reports into a proper Airtable data model that you might encounter that makes the Script Block approach far more precise.
When I have had this issue, it might be consumer grade… I just work the data in Excel until its what is needed for import… then save out as a CSV so it strips the Excel file of the formulas, just takes the static data… then Import that. Not sure if that would work?
HTH!
R
When I have had this issue, it might be consumer grade… I just work the data in Excel until its what is needed for import… then save out as a CSV so it strips the Excel file of the formulas, just takes the static data… then Import that. Not sure if that would work?
HTH!
R
Yeah, unfortunately, @Zak_Kennedy doesn’t appear to have the ability to control the reporting format.
+2Hi KBD,
When I am saying it is coming in a format that doesn’t work well I am basically saying that the columns from the report I receive do not work well in airtable. For Example I deal with Vehicles. Best way to uniquely identify I vehicle is the VIN number or my other option would be the case number. The data I get from my customer is a type of vehicle in column one with VIN or case number in the following columns. When I import the file that vehicle type becomes the locked column that is not unique leaving me with data that is hard to use.
Zak
There are 2 options I can think of.
a) flip the columns in the CSV file before you import
b) add a blank first column to the file and then import.
post import, you can make the first column a formula that concatenates the vin and vehicle as a unique identifier.
I think option (a) should be good enough?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
