I have to say that when I first saw Airtable I thought, oh look – Excel online. It’s a little more than that and it’s been kinda’ cool to watch Airtable mature.
The thing I like about Airtable is that a lot of software – in fact most software, is a database, logic that puts data into the database and takes things out of the database, and then actions based on the data. If we’re interacting with operating system APIs, then that database might be in registry hives (Windows) or defaults domains (Mac). We like to overcomplicate what software does in our brains, but that’s the gist. And a lot of software can end up really just being a database with little actions or transforms to data.
In those cases, Excel is kinda’ like a simple database. We have sheets instead of tables and we have a grid of objects that visually at least looks very much like a database. Then along game Google Sheets and suddenly we could interact with data online concurrently. Then Microsoft Online and Numbers. All pretty straight forward. But Airtable goes a little further. We can use it like a Kanban board, make blocks in a single sheet, pivot tables, translations, and do much more. With the Page Designer we can actually make Airtable start resembling a web app.
But, as with that inventory tracking system we outgrew in VisiCalc, there’s definitely a point where we’ve taken any tool like this to its limit and we need a custom database, SaaS tool, or some other more advanced solution. And when this happens, it’s time to grab our data out of that Airtable Workspace.
To export data from Airtable, we’ll repeat a very simple task on each of the grids (or sheets if it were Excel). Simply open the sheet and click on the ellipses icon in the top bar.
From here, click on “Download CSV”. The .csv file will then download and appear in your Downloads directory. Repeat that process for each tab to get all the data in separate file. The files will open in most standard spreadsheet tools but specifically in the default tool to handle csv files (usually Excel on Windows or Numbers on a Mac).
This is where the power of having that data in centralized repository comes into play. Files can then be manipulated easily and imported into other tools. A few things to remember here:
- The order they appear in when using Airtable is the order they will appear in the csv
- Dragging objects between Kanban boards doesn’t export with the csv
- The export excludes any raw data that’s been filtered out, so use the filtering options in Airtable to constrain what’s being exported
We can then take the csv files and enrich them, edit them, etc. I’ve built a few for specific use cases I’ve had. For example, this project will look up an IP address we might have in Airtable and provide a geolocation: https://github.com/krypted/csviplookup or this project or the following are used to perform various machine learning tasks on those files:
- Recommend similar content: https://github.com/krypted/lightweightrecommender
- Assign a polarity score to the sentiment of the text in a given column: https://github.com/krypted/lightweightsentiment
- Apply a category to the data: https://github.com/krypted/lightweightcategorizer
- Tag data with one of the supplied tags in a training file: https://github.com/krypted/lightweighttagger
Those are just some examples but it’s much more common that we’ll be conforming the data into a specific format so it can be imported into another tool, like a custom software solution. Sometimes this is just changing the order of columns, or renaming columns so the data conforms to a specific template. Once done, the data is imported and magically appears in another tool. That data wrangling is always a little different, but we start with making sure there’s an available field and sometimes we work with a software vendor to extend the schema of the data they track to accommodate for what we might have in Airtable if possible – or choose to repurpose data into other fields. Hopefully the above projects help point anyone that needs to do so in the right directions!