CSV Import Block: fixing Field Mappings

We’re making use of Airtable to help manage our delivery menu for bud.com. Airtable’s CSV Import Block helps to bring on new products for various regions into our overall catalog.

Now we’re running into an issue with field mapping in the CSV Import Block: Airtable mixes up the fields to match as we import different spreadsheet, and there’s no way to quickly reset the import system.

Details:

We import a CSV of items from Sacramento using the Airtable CSV Import block. We have matching field names for the import data, so the fields show up matching immediately - hurray! We are importing fast.

Then we go to import a CSV of items from Oakland. The Oakland fields are about 95% the same, but with a few different field names, and a different order to the fields.

For this second CSV, even if they have many overlapping field names, AirTable will suggest non-matches - ignoring direct matches.

So if we had matched these fields for a Sacramento CSV import:

Published <= Published
Weight (g) <= Weight (g)
Categories <= Categories

When we load the Oakland CSV, the field mapping suggestions would be:

Published <= Weight (g)
Weight (g) <= Categories
Categories <= Published

We have over 100 fields so there’s a lot of matching and rematching we’re doing here to avoid data mistakes during frequent data updates.

We were hoping to ask the Airtable CSV Import Block to just re-index all the fields, to look for matches as it seems to do on the initial import. But we can’t figure out how to do that:

If we toggle the individual fields off, and then back on, the mis-matches returned. If we quit our AirTable desktop app and restart, the mismatched field mappings are the same.

Here’s a feature wishlist for Airtable’s CSV Import Block to better manage field mappings:

  • Add a reset button to ditch cached field mappings and re-acquire field mappings based on the current file
  • Add a save / load function for field mappings
  • Allow find/search to be able to locate fields in the left column for matching
  • Show the CSV file name uploaded in the CSV import / field matching view so we can be reminded which data set we are importing as we scroll through the many fields

Maybe some of those things are already possible, I just don’t know how yet? Thanks for your time.

Welcome to the community, @Justin_Hall!

Here is your solution:

You can add multiple CSV import blocks to your base, and rename each one of them according to the type of CSV file you will be uploading. You can even put them on different dashboards, if you’d like.

Then, just use the appropriate CSV import block for the appropriate file. Each CSV block will remember its own settings.

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slight_smile:

3 Likes

These are really well-defined requirements and while it would be great to see the import Block include these in future updates, it could be a while and I have a hunch that no single developer could ever envision all of the nuances that would be impactful to your environment, or the vast possibilities that we all seem to encounter with import tasks.

Have you ever discovered an import tool that is perfect?

If someone asked me how to solve for “x” (where “x” is nearly every imaginable import theme, and many more that cannot be predicted), I would approach it from a data perspective. Indeed, the right approach is probably best discovered by abstracting everything about a data import recipe into a data model itself.

Hmmm… we need a database with an integrated scripting model that works with Airtable. Oh yeah - we have all that with the advent of Script Blocks. :slight_smile:

Each import recipe should be definable by anyone (where “anyone” is any information worker with an understanding of the import objective). A databased recipe dictionary would be ideal in a company with multiple locations and multiple import pathways from modern data sources that can be easily reshaped, to legacy sources that are rigid.

The beauty of a data-driven import solution is that changes, modifications, and subclassing can generally occur without making code changes. It abstracts all the moving parts of the solution to a configuration model whose behaviours are applied at import-time - literally, a late-binding approach.

Such a system would define and sustain the import “themes” as an actionable knowledge base of ways to move data into (and perhaps out of) Airtable bases. Each theme would identify the nature and author of the recipe.

There would be only one script block designed in a way to utilize the recipes as dynamic rules that anyone could use (straightaway), test (with a simulator), and fully document and manage as versions with utilization log analytics including who and when each recipe is used.

Indeed, all the items in @Justin_Hall’s wish-list could be met and lots more.

I would hesitate developing something like this as a Custom Script Block but I also wouldn’t rule it out. This is an app that falls into the “utility” category and it’s likely (at least in its early days) to require rapid change and feature enhancements. A Script Block implementation would make it far easier to start small and grow the tool over time, whereas, a Custom Script Block requires a far deeper investment in a framework, with deployment friction - simply not as agile.

If there are customers and/or Airtable developers who would like to partner with me to develop the coolest, easiest, and most agile import utility ever conceived, I’d be willing to help.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.