Updating Airtable Records from CSV Using Python

Other Questions Airtable
1

I’m trying to update Airtable records using data from a CSV file in Python. My CSV has two columns: CIN and Nombre de jours travaillés. I want to update the Nombre de jours travaillés field in Airtable using CIN as the key.

I’ve read the CSV file and converted it to JSON, but I’m stuck on formatting it for Airtable’s PATCH request. The API expects a structure like this:

data = {
    "records": [
        {
            "fields": {
                "CIN": "A123456",
                "Nombre de jours travaillés": 22
            }
        }
    ]
}

How can I transform my JSON to match this format? I’ve considered iterating over the JSON and constructing a new structure or creating a list of records and appending it to a dictionary.

Once the data is formatted properly, I plan to send the PATCH request as follows:

def update_airtable(table, data):
    url = f"https://api.airtable.com/v0/{database_id}/{table}"
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Content-Type": "application/json",
    }
    response = requests.patch(url, json=data, headers=headers)
    return response.json()

Any help with the correct data formatting would be appreciated. Thanks!

2

I’ve tackled this exact problem before, and I can tell you it’s not as complicated as it seems. Here’s what worked for me:

First, make sure your CSV is properly loaded into a list of dictionaries. Then, create a function to transform the data:

def format_airtable_data(csv_data):
    return {
        'records': [
            {
                'fields': {
                    'CIN': row['CIN'],
                    'Nombre de jours travaillés': int(row['Nombre de jours travaillés'])
                }
            } for row in csv_data if row['CIN'] and row['Nombre de jours travaillés']
        ]
    }

airtable_data = format_airtable_data(your_csv_data)

This should give you the structure Airtable expects. The if statement in the list comprehension helps filter out any rows with missing data, which can cause issues.

One thing to watch out for: Airtable has a limit on how many records you can update in a single request (usually around 10). If you have a large CSV, you might need to split your requests into smaller batches. Just something to keep in mind as you scale up your solution.

3

I’ve dealt with Airtable API integrations before, and here’s an approach that might work for you:

First, ensure your CSV data is loaded into a list of dictionaries. Then, you can use a function to transform this data into the Airtable-friendly format:

def format_for_airtable(csv_data):
    return {
        'records': [
            {
                'fields': {
                    'CIN': row['CIN'],
                    'Nombre de jours travaillés': int(row['Nombre de jours travaillés'])
                }
            } for row in csv_data
        ]
    }

formatted_data = format_for_airtable(your_csv_data)

This should give you the correct structure for the PATCH request. Remember to handle any potential data type issues, especially when converting ‘Nombre de jours travaillés’ to an integer.

4

hey mike, i had a similar issue. try using a list comprehension to format ur data:

records = [{‘fields’: {‘CIN’: row[‘CIN’], ‘Nombre de jours travaillés’: int(row[‘Nombre de jours travaillés’])}} for row in csv_data]
data = {‘records’: records}

this should give u the structure airtable expects. Good luck!