Please find an updated version of this article at Migration from Airtable Bases to SeaTable - SeaTable
The procedure described in the new article leverages SeaTable’s native Python scripting engine and is much easier to use. Additionally, it can be used by every SeaTable user - cloud users and self-hosters.
How it works in general
how is the data retrieved
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.
how is the data written
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.
do I have to input records myself?
No. You’ll only have to work on the columns headers, that’s all!
does this script alter data in my Airtable base?
No. This script only reads from your Airtable base. You won’t lose any data with this script.
what you need to prepare
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.
How is each column type imported?
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 | Corresponding SeaTable Column Type | Notice |
---|---|---|---|
Attachment | file | File | Imported as files. |
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 {latitude, longitude} , you can import this to SeaTable as a text column and change its type to geolocation/lng_lat. |
Prerequisites for your computer
We recommend using this script under Linux/Ubuntu with Python 3.8 and pip3. If you don’t have these, install them with the following commands in your console:
First, update the system:
sudo apt update
sudo apt upgrade -y
Then install Python 3:
sudo apt install python3.8
And the Python package manager pip:
sudo apt install python3-pip -y
After the installation, you can use the following optional commands to check if the correct packages have been installed:
python3 --version
pip3 --version
Your system is are all set for the migration script if the output looks similar to/like this:
Python 3.8.10
pip 20.0.2 from /usr/lib/python3/dist-packages/pip (python 3.8)
Here are the steps
1. Install the SeaTable API dependencies
Run this command in your linux host:
pip3 install -U seatable-api
2. Download the two scripts:
The settings:
wget https://raw.githubusercontent.com/seatable/seatable-syncer/main/airtable-import/airtable_importer_settings.py
and the importing script:
wget https://raw.githubusercontent.com/seatable/seatable-syncer/main/airtable-import/airtable_importer.py
3. Modify the settings in the airtable_importer_settings.py
:
To modify a file in Linux/Ubuntu, you can use the built-in text editor nano by typing the following command in your host:
nano airtable_importer_settings.py
And follow the on-screen instructions to modify the file, save it and exit.
GET YOUR SEATABLE SERVER URL AND API TOKEN
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 airtable_importer_settings.py
:
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.
GET YOUR AIRTABLE API KEY AND BASE ID
Then, go to Airtable and get your API key and the ID of your base. Modify the airtable_importer_settings.py
accordingly:
airtable_api_key = '<your Airtable API key>'
airtable_base_id = '<your Airtable base ID>'
DEFINE THE TABLE NAMES
Now, let the script know which tables you are importing (replace “Table1” etc. with the names of your tables):
table_names = ["Table1", "Table2", "Table3"]
ADDRESS THE FIRST COLUMNS
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"),
...
]
DECLARE THE LINK COLUMNS
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 = []
IMPORT THE TABLE HEADERS
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
CHECK THE 10 DEMONSTRATIVE RECORDS
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.
FINAL STEP: IMPORT THE RECORDS
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.
Good to know
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:
the rows and columns are not in the original order
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.
empty columns could be missed
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!
views settings are not returned by the Airtable API
As the views are combinations of sorts, filters, groups, hidden columns etc., you can create the views manually after the import.
automatic columns should be created manually
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.
you can even import tables into an old base
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.
the script could raise an error while creating link columns
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.