Migrate An Airtable Base to SeaTable with this Script [Beta]

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.

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.

image

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.

image

image

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 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.
Email string Email 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.

Here are the steps

1. Install the SeaTable API dependencies

pip3 install -U seatable-api

2. Download the two scripts:

The settings:

wget https://github.com/seatable/seatable-api-python/raw/master/demo/airtable_importer_settings.py

and the importing script:

wget https://github.com/seatable/seatable-api-python/raw/master/demo/airtable_importer.py

3. Modify the settings in the airtable_importer_settings.py:

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.

2 Likes