Logo

How to import a CSV into Airtable Using the API

Created on September 3, 2024

python
async

This function automates the process of importing a CSV file into Airtable using the Airtable API.

Setting Up Airtable Credentials

To use the Airtable API, you'll need the following credentials:

Base ID

This uniquely identifies your Airtable base.

  1. Open your Airtable base in a web browser.
  2. Look at the URL. It will be in this format: https://airtable.com/app??????/tbl??????/viwXXXXXX
  3. The Base ID is the app?????? part of the URL.

For example, if your URL is https://airtable.com/app12345678/tblAbCdEfGh/viwXyZaBcD, your Base ID is app12345678.

Table Name

The name of the specific table you're importing data into. This is simply the name of your table as it appears in Airtable.

Personal Access Token

This authenticates your API requests.

  1. Go to your Airtable account page
  2. Scroll down to the "API" section.
  3. Click on "Generate personal access token".
  4. Give your token a name that describes its purpose (e.g., "CSV Import Token").
  5. Set the token's permissions. For importing data, you'll need:
    • data.records:write scope to create new records
    • schema.bases:read scope to read the base schema
  6. You can add other scopes if needed for additional functionality.
  7. Choose which workspaces or bases this token can access. For security, it's best to limit access to only the necessary bases.
  8. Click "Create token".
  9. Copy the generated token immediately and store it securely. You won't be able to see it again.

Preparing Your Airtable Table and CSV Structure

  1. Create a new table in Airtable or use an existing one.
  2. Ensure that your table's column names match the headers in your CSV file.
  3. Structure your CSV file:
    • Use a standard CSV format.
    • Include a header row with column names.
    • Ensure data types in the CSV match the field types in Airtable.

Example CSV structure:

Name,Email,Phone
John Doe,john@example.com,123-456-7890
Jane Smith,jane@example.com,987-654-3210

Function Code

import csv
import requests
import os
from io import StringIO

def fetch_from_gdrive(gdrive_file_id):
    """
    To run this sample code in Codeupify we have to upload the file to a location that's accessible from 
    Codeupify's serverless environment. If you are going to run this code locally you can skip this method.

    To get the Google Drive file ID:
        1. Upload the file to Google Drive
        2. Share the file with "everyone" and copy the share link
        3. The file id is going to be the long string of numbers and letters - https://drive.google.com/file/d/[YOUR_FILE_ID]/view?usp=drive_link
    """
    gdrive_url = f'https://drive.google.com/uc?export=download&id={gdrive_file_id}'
    response = requests.get(gdrive_url)
    
    if response.status_code != 200:
        raise Exception(f"Failed to fetch file from Google Drive. Status code: {response.status_code}")
    
    # Save the CSV data to a file
    with open('/tmp/airtable_import.csv', 'w') as f:
        f.write(response.text)
    
    print("CSV data successfully saved to /tmp/airtable_import.csv")


def handler(request):
    # Get gdrive_file_id from the request body
    gdrive_file_id = request['body'].get('gdrive_file_id')
    if not gdrive_file_id:
        print("No Google Drive file ID provided.")
        return

    try:
        # Fetch and save data from Google Drive
        fetch_from_gdrive(gdrive_file_id)
    except Exception as e:
        print(str(e))
        return

    # Prepare Airtable API credentials
    personal_access_token = os.getenv('PERSONAL_ACCESS_TOKEN')
    base_id = os.getenv('BASE_ID')
    table_name = os.getenv('TABLE_NAME')
    API_URL = f'https://api.airtable.com/v0/{base_id}/{table_name}'

    headers = {
        'Authorization': f'Bearer {personal_access_token}',
        'Content-Type': 'application/json'
    }

    # Read from the saved CSV file and upload to Airtable
    with open('/tmp/airtable_import.csv', 'r') as f:
        csv_reader = csv.DictReader(f)
        for row in csv_reader:
            response = requests.post(API_URL, json={'fields': row}, headers=headers)
            if response.status_code == 200:
                print(f"Uploaded: {row}")
            else:
                print(f"Failed to upload: {row}, Error: {response.text}")

    print("CSV data successfully uploaded to Airtable")