The structure of an Airtable base can be retrieved with the Airtable API, which enables the migration of an Airtable base into SeaTable. We have created a script to carry out this migration and have tested with various bases with all the data types that are available in Airtable and in SeaTable.
Generally, this works like a clock and saves a lot of effort compared to what you’d have to do to manually migrate the bases (e.g. export as CSV and then import). However, due to the limitation of the Airtable API, you might need to do some manual preparations to make the import perfect, but this script is definitely worthy of use, especially if you have hundreds, thousands, or even more rows in your Airtable base.
The Airtable API returns 100 rows of data per request. With this data, this script can read out the tables and column structures of all the data types, and the rows data as well.
Empty columns are not returned, which is a limitation of the Airtable API. Let’s say, for example, you have 103 rows in your Airtable base, and the Airtable API returns 100 rows of data and exactly misses those 3 rows where a column only has records in. That column will be missed.
The SeaTable API in comparison, by the way, returns all the columns, including empty ones, in the response object.
The script then writes these structures (tables, columns) into your SeaTable base and fills in the rows. It creates the tables and columns before filling the data and lets you manually adjust them before the real import. When configured, the script will also automatically add the links in the “Link to other records” columns.
No. You’ll only have to work on the columns headers, that’s all!
No. This script only reads from your Airtable base. You won’t lose any data with this script.
Your computer has to be able to run Python 3 and
pip3 and has internet connection. You must provide your Airtable API key, your Airtable base ID and your SeaTable server URL, as well as your SeaTable base API token.
The most column types of Airtable are the same as SeaTable, there are only minor differences, this following table shows you the exact differences of data types, how they are imported and how you can handle them:
|Airtable Column Type||Value||Relevant SeaTable Column Type||Notice|
|Attachment||File||File||Airtable only has an “attachment” type for both image and file. This will be imported both as file columns. You can, of course, change the column type to image before importing the rows.|
|Autonumber||number||Auto number||You have the choice: to change or not to change the column type before/after importing the rows. If not changed, it’ll be imported as text; if changed, the auto number will not be the same order as in Airtable, because the imported rows are not ordered (see below “Good to know”).|
|Barcode||barcode object||-||Can be imported as text or number. Text type is recommended, as numbers cannot start with “0” just in case.|
|Button||button object||Button||Buttons won’t be imported.|
|Checkbox||boolean||Checkbox||Imported as checkbox.|
|Collaborator||collaborator object||Collaborator||Collaborators are imported as multiple select options.|
|Count||number||Formula||Imported as number. As Airtable doesn’t have the type of link formula (but explicitly rollup, count, and lookup), these can only be added per hand.|
|Created time||string (ISO 8601 formatted date)||Created Time||Imported as date.|
|Currency||number||Number||Imported as number.|
|Date||string (ISO 8601 formatted date)||Date||Imported as date.|
|Duration||number||Duration||If imported as number in seconds, you can simply change the type to duration.|
|string||Imported as email.|
|Formula||varied outputs (depending on the formula)||Formula||The formula cannot be imported. The results are imported as text, number, or date.|
|Last modified by||collaborator object||Last Modifier||Last modifiers can be imported as text, or as single select options (see below “Single select”).|
|Linked record (Link to another record)||array of record IDs (strings)||Link other records||Imported as “Link other records” column.|
|Long text (With or without rich text formatting enabled)||string||Long Text||Imported as long text.|
|Lookup||array of numbers, strings, booleans, or objects||Link Formula||As Airtable doesn’t have the type of link formula (but explicitly rollup, count, and lookup), these can only be added per hand.|
|Multiple select||array of strings||Multiple Select||Imported as multiple select. The options that are not used in the Airtable base are not imported.|
|Number||number||Number||Imported as number. You can change the number format before or after the rows import.|
|Percent||number||Number||Imported as number. You can change the number format before or after the rows import.|
|Phone number||string||-||Can be imported as text or number. Text type is recommended, as numbers cannot start with “0” just in case.|
|Rating||number||Rating||Imported as number, and you can manually change its type to rating before or after the rows import. Pay attention to the scaling (total star number in the original base) as this is not imported.|
|Rollup||number or string||Link Formula||As Airtable doesn’t have the type of link formula (but explicitly rollup, count, and lookup), these can only be added per hand.|
|Single line text||string||Text||Imported as text.|
|Single select||string||Single Select||Imported as single select. The options that are not used in the Airtable base are not imported.|
|URL||string||URL||Imported as URL.|
|-||objects||Geolocation||The Airtable doesn’t have a geolocation type. If you have a text column with latitude-longitude data in the format of
pip3 install -U seatable-api
and the importing script:
First, you need the API token for your target base in SeaTable. You can do this with the web interface (refer to this article to find out how), or with the SeaTable API. Then modify these lines in the
server_url = '<e.g. https://cloud.seatable.io>' api_token = '<API token of your base>'
Attention! The base API token is not to be confused with your auth token or the base’s access token. Visit api.seatable.io for more details.
airtable_api_key = '<your Airtable API key>' airtable_base_id = '<your Airtable base ID>'
Now, let the script know which tables you are importing (replace “Table1” etc. with the names of your tables):
table_names = ["Table1", "Table2", "Table3"]
The Airtable API doesn’t specify which column is the first column. This is but very important, especially for linked records. So you’d need to let the script know which column is the first column in each of your tables:
first_columns = [ ("Table1", "first_column_name1"), ("Table2", "first_column_name2"), ... ]
To make sure the linked records are correctly imported, one more step has to be taken: specify the link columns and the tables they are linking to.
For example, you have a “Table 1”, and a “Table 2”, while there’s a link column “link1” in “Table 1” which links it to “Table 2”, etc., then you could modify the
airtable_importer_settings.py like this:
links = [ ("Table 1", "link1", "Table 2"), ("Table 2", "link2", "Table 3") ]
For each link, you only have to set up once in the script. That is to say, both in Airtable and SeaTable, when you link two tables, two link columns show up in both tables, but you only have to set up for one of those link columns in the script. The other one will be added automatically.
Nevertheless, if your base doesn’t have any link columns, you still need to modify the
airtable_importer_settings.py like this:
links = 
Now that you are ready with the
airtable_importer_settings.py, you can save it and close it. Execute the following command to import the table headers, and the script will retrieve 10 rows for each table as demonstrative records for you:
python3 airtable_importer.py --import-header
The script imports the table headers and 10 demonstrative records from your Airtable base. This is your chance to check the column headers, types, as well as existence. Again, as the Airtable API only returns 100 records, if for some airy columns the only records are not included in these 100 records, they won’t be imported until this step.
What you need to do now is:
- Check if all the columns are imported. If not, add missing columns per hand.
- Check if all the column types are correct. If not, adjust them according to the table above.
- Check the ten demonstrative records of each table to see if they look good to you.
You don’t have to delete or modify these 10 records: they will be automatically overwritten in the next step.
Finally, when you are satisfied with the demonstrative records, you can start the real thing by executing the following command:
python3 airtable_importer.py --import-rows
And that’s it! If you are not satisfied with the final result (e.g. forgot to add a missing column), you can delete the tables and repeat the above steps.
The Airtable migration script is still a beta. Everyone is welcome to test it, use it, and give feedback about it. We have already tested it with all the types and various bases, and here’s what we think you should also know:
We cannot retrieve the orders from the Airtable API. Of course, the Airtable API provides an option to list rows in a view which returns rows in the exact orders in that view, but we’d rather not do this to you, because:
- This would require you to define the view for each table;
- Records that are not in that view won’t be imported, which is definitely not your intention, right?
- You can order your rows per sorting, filtering, and grouping anyway.
As per the columns, if the order matters, you’d have to arrange them with the mouse.
The Airtable API doesn’t return a column if it’s empty. As the script takes the first 100 rows returned by the Airtable API, there’s a good chance that such a column will be missed. If one column is empty or only has 3 records in it, it won’t be a huge issue if you have to create this column manually before importing the rows. In comparison, the effort that this script saves is huge!
As the views are combinations of sorts, filters, groups, hidden columns etc., you can create the views manually after the import.
The script cannot create automatic columns like formula, link formula, buttons, etc. because their settings (e.g. formula, count/rollup/lookup settings, button functions etc.) are not part of the response of the Airtable API. Anyway, this shouldn’t be an issue for anyone: once you have created these columns manually, the content will be automatically filled for all your records.
As the tables are just added to an existing base, this doesn’t have to be a new base. Even in an old base with already many tables, you can use this script to add the tables from Airtable. The only thing you would like to avoid is the duplication of table names.
Sometimes during our test, the script could raise an error while creating link columns headers, like this:
However, it didn’t affect the result. If you encounter such an error message, check your link columns. If everything is OK, you can ignore this error. It’s only meaningful if the link columns cannot be created correctly.