Yeah, I feel your pain. Importing any sizeable collection where target data types are critical is never going to work well because CSV files are really dumb; they’re just numbers and letters.
I haven’t tested this, but I’ve often wondered if there was a way to set up the fields and types and then import a CSV file hoping to get the data to transform in-flight into the proper data type values. Maybe someone here knows the answer to this question.
For my clients (where data types are really important), I set up the target tables and field types and formulas in advance. Then I thoroughly test the links, lookups, etc. with a few test data rows. Then – AND ONLY THEN – do I use the API to perform the import process. This leaves me with a fully functional solution the instant the data arrives. It is ready to test and prove it works.
Occasionally there are hiccups, but making adjustments in the API, clearing all the data from the tables, and running again is very easy as opposed to starting over from a CSV import that had to be modified to meet some requirements.
My success in importing has always come at the expense of detailed and methodical planning and fully testing the Airtable model before introducing the data. Lastly, with a known functional Airtable solution, the next step is to model the data to fit inside it during the API upload process. Only server-side code using the API is able to model the data to fit the desired target solution.
I was reading your post a second time and it occurred to me…
This means that each and every city or county must be verified to be in the correct county or state or the wheels will come off.
You should consider introducing US FIPS codes into your data architecture.
A city name plus the FIPS code for a county is a unique identifier since no county in the US has two cities of the same name. I have used FIPS for many geo-location projects and this is the best way to craft a unique identifier for cities.
Ideally, your “FIPS keys” should be of the format:
01001-Plattville (which would be Plattville in Autauga County, Alabama)
There are many advantages to using FIPS including (but not limited) to these -
- The code is really a concatenation of a 2-digit state code plus a three-digit county code which means you can know state and county separately which also means you can reverse-encode.
- These codes are managed by Uncle Sam, and it’s an open standard which means you can automate the inclusion and awareness of future towns and cities.
- County is yet another metric that tends to become valuable in any project or solution where location science is at its core.
- There are open and free FIPS web services that can be used for instant lookups, integration, etc. Or, the FIPS database can be freely integrated into your Airtable solution.
It feels like i’m mowing a golf course with a push mower here…
I think a better metaphor is mowing a sod farm with Barbie’s Cut’N’Curl beauty scissors. You need planning, architecture, and automation to mow 25,000 acres.