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

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.

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

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.

3 Likes

Hi and thank you for this script and SeaTable.

I’ve been working on an import from AirTable and have some modifications to the script, but could also use some help / suggestions from you as I’m not as familiar with Python as I am other languages.

Improvement to File Uploads

First off, an improvement I made to the file uploads that prevents skipping uploads when there is an error (usually a [Errno 429] {“detail”:“Request was throttled. Expected available in 3 seconds.”} from Seatable). This is the modification I made to the upload_file function. The same could likely be done to the outer exception handling to prevent failure:

    def upload_file(self, item, file_type):
        """file_type must in ['image', 'file']
        """
        try:
            # download from airtable
            name = item['filename']
            url = item['url']
            response = requests.get(
                url=url, headers=self.airtable_api_headers, timeout=60)
            content = response.content
            # upload to seatable
            isUploaded = False
            while isUploaded is False:
                print('[Info] Uploading : ' + item['filename'] + '; ' + item['url'])
                try:
                    file_info = self.base.upload_bytes_file(name=name, content=content, file_type=file_type)
                    isUploaded = True
                except Exception as fileUploadException:
                    print('[Warning] Upload file error (waiting 30 seconds):' + item['filename'] + '; ' + item['url'], fileUploadException)
                    # Generically wait 30 seconds, but this could be parsed from the exception
                    time.sleep(30)
            return file_info
        except Exception as e:
            print('[Warning] download file error:' + item['filename'] + '; ' + item['url'], e)
            return None

Lookup Fields / Select Single / Multiple

An area I’d like to request assistance with is the way lookup fields and select fields are imported. All of them seem to get imported as select and lookup fields no matter what they actually are get turned into multiple selects with duplicate selected values. We use lookup and select fields quite a bit so this creates a lot of cleanup work.

Even a lookup field from another table like “First Name” gets turned into a multiple select with multiple values.

Could you assist me with an improvement to that logic or:

  1. Help me understand where in the script I can access both the Airtable select values and the Seatable ones to dedupe the select values and at least prevent the duplicates from being added.
  2. Help me understand where in the script I can see what airtable is giving for the lookup fields so we could optionally ignore them or at least keep their type correctly.

I’m happy to help further improve the script, though my time on this import is limited so I’d like to do these contributions now while I have the time.

Thanks again for a fantastic product!

This sounds like a good idea.

I can only partially reproduce your problem:

  • A single select column in my Airtable base is imported as text.
  • A lookup column in my Airtable base is imported as multiple select.

Converting a text column in a single select column is done in no time and does not require cleanup. Can you double-check if your single selects are really imported as multiple selects?

When it comes to Airtable’s lookup columns, the table above states:

@Daniel: Three comments:
1.) Does it make sense to add @Wheels’ improvement to the import script?
2.) Airtable’s single selects should be imported as single selects in SeaTable. This is also what Karlheinz’ tables states. But in fact, single selects are imported as text. (I tested with different tables. The result was always the same.)
3.) Could the import script be improved so as to ignore count, lookup and rollup columns in Airtable bases? This way, it would be clear that they have to be created manually after the import.

Thanks for the response. After further looking, the single selects are imported as text when the demo rows are loaded (–import-header). When I import everything after (–import-rows), that is when they get converted to multi-select with duplicated values. This happens to lookup columns as well (which we can cleanup of course as I planned to, but figured I’d like an option to ignore them at least, if not formulas, etc. as well).

Further looking seems to show a pattern that lookups are imported as multi-select no matter what with the values duplicated like so, but normal single select fields do seem to stay text on my latest import.
image

Sorry for any confusion as I’m still trying to recognize the pattern too :rofl:

As mentioned, I’m willing to contribute to the script if I can get any pointers on where and how since I’m new to both APIs.

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.

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.