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.
