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 only the ‘Nombre de jours travaillés’ field in Airtable, using ‘CIN’ as the key.

I’ve already loaded the CSV data into a dictionary and converted it to JSON. Now I’m stuck on how to format this data for Airtable’s PATCH request.

The Airtable API expects this structure:

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

How can I transform my JSON data to fit this format? I’ve thought about looping through the JSON and building the structure, or creating a list of records and appending it to the data dict.

Once I have the right format, I plan to send the PATCH request like this:

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

Any tips on how to structure the data correctly would be really helpful!

2

hey, i’ve done this before! u can use a dict comprehension to make it faster:

airtable_data = {
‘records’: [{
‘fields’: {
‘CIN’: k,
‘Nombre de jours travaillés’: v
}
} for k, v in csv_dict.items()]
}

then just use ur update_airtable function. works like a charm!

3

I’ve dealt with a similar situation before, and here’s what worked for me:

First, you’ll want to transform your JSON data into the format Airtable expects. You can do this efficiently using a list comprehension:

airtable_data = {
    'records': [
        {'fields': {'CIN': cin, 'Nombre de jours travaillés': days}}
        for cin, days in your_json_data.items()
    ]
}

This creates the structure Airtable needs. Then, you can use your update_airtable function as planned:

response = update_airtable(table_name, airtable_data)

Make sure to handle any potential errors in the response. Also, be aware that Airtable has limits on how many records you can update in a single request (usually 10), so for large datasets, you might need to batch your updates.

4

Having worked with Airtable and Python extensively, I can offer some insights. Your approach is on the right track, but there’s a more efficient way to handle this.

Instead of converting to JSON first, work directly with your CSV data. Use the csv module to read your file, then create the Airtable-friendly structure in one go:

import csv

airtable_data = {'records': []}
with open('your_file.csv', 'r') as file:
    reader = csv.DictReader(file)
    for row in reader:
        record = {
            'fields': {
                'CIN': row['CIN'],
                'Nombre de jours travaillés': int(row['Nombre de jours travaillés'])
            }
        }
        airtable_data['records'].append(record)

This approach is memory-efficient and straightforward. Remember to handle potential data type issues, like converting string numbers to integers.

For large datasets, consider implementing pagination in your update_airtable function to respect Airtable’s rate limits. You might need to split your data into chunks and send multiple requests.