Exporting data out of Airtable


#1

Does anyone have experience with exporting their data and how it effects relational links, etc?

I am loving the features of the program but I am reluctant to enter all of my data into Airtable only for my employer to decide they no longer want to use the software.


#2

As I recall, relational links are exported as an array of, well, labels I guess one might call them, with each label in the array representing the primary field of the linked record. (That is, if you have a linked-record field that links to three records with the primary field equal to ONE, TWO, and THREE, the field once exported will be "ONE,TWO,THREE".) How easily that converts to another DBMS depends on the DBMS, I guess — but it seems pretty straightforward…


#3

I had a similar question. When I exported to CSV, the columns were comma-delimited, but sometimes there were commas in the Airtable cells, so it messed up my columns. This uses Excel outside of Airtable, of course, so the usual disclaimers apply from their end, I’m sure.

I figured out that I could export the CSV, and then use Power Query to bring the file into Excel. It looks like if Airtable sees a cell with a comma in it, it puts quotes around the cell’s contents. Ordinary Excel imports don’t know what to do with the quotes, and neither do Access imports. However, I found that I could enable Power Query, and then use the “data” tab to import the CSV. It then opens it in Power Query. I clicked the “Transform Data” tab at the bottom of the Power Query dialog, and then clicked the “Transform” tab at the top of the next dialog. There, I chose “Use First Row as Headers” and clicked “Close & Load” under the “Home” tab. It’s important to realize that the resulting table (“List” in Excel parlance) is live, so that if you refresh it, it will reload from the original file. Of course you can then copy and paste the data to sever the connection to the CSV file.

This isn’t a particularly elegant solution, but it does get information out. There’s still the problem of separating your data into separate rows if you want to break up a big table into a relational database, but at least your data’s there and readable.


#4

My last copy of Excel was on the PC the FBI decided to keep for itself (long story involving a housemate with lousy taste in men and an FBI agent who didn’t know which side of a piece of carbon paper goes on top; actually, I guess it also involves whatever sort of institutional dysfunction could lead to the FBI in 2015 still using loose sheets of carbon paper to make copies in the field). Accordingly, all my recent CSV import experience has been with LibreOffice, which smirks at quote-delimited text.

There appears to be a magic combination of delimiter specifiers and the way one holds one’s mouth that will cause Excel to simmer down and behave on importation; you may want to hunt around some to see if it will work for you. Failing that, there are a number of free/shareware/low-cost CSV editors available should you need to tweak the data before import. I’ve heard a lot of good things about Ron’s Editor – although the 1000-line limit for the free version seems a little parsimonious.