Updating Airtable Records with CSV Data Using Python

Other Questions Airtable
1

I’m trying to read information from a CSV file and modify existing records in my Airtable database using Python. My CSV contains employee data that I need to sync with my Airtable table.

Here’s my current table structure:

employee_data = {
    "records": [
        {
            "id": "rec123ABC",
            "fields": {
                "Name": "John",
                "LastName": "Smith", 
                "Status": "Active",
                "EmployeeID": "EMP001",
                "Phone": "555-0123",
                "Salary": 15000,
                "WorkDays": 22,
                "StartDate": "2022-01-15",
                "Email": "[email protected]"
            }
        }
    ]
}

I only want to update the WorkDays field using EmployeeID as my lookup key. My CSV file structure is simple:

EmployeeID,WorkDays
EMP001,20
EMP002,18

I’ve managed to read the CSV data:

import csv
import json

employee_records = {}
with open(csv_file, "r") as file:
    reader = csv.DictReader(file)
    for index, row in enumerate(reader):
        employee_records[index] = row

Then I convert it to JSON format:

processed_data = json.loads(json.dumps(employee_records, indent=2))

Now I’m stuck on transforming this data into the correct Airtable format for the PATCH request. I need help structuring the data properly before sending it to the API.

My target structure should be:

update_payload = {
    "records": [
        {
            "fields": {
                "EmployeeID": "EMP001",
                "WorkDays": 20
            }
        }
    ]
}

I’m considering two approaches but not sure which is better. Any suggestions on how to properly format this data for the API call?

2

You’re overcomplicating this. Skip the JSON conversion - you already have a dictionary.

Here’s what I do:

import csv

# Read CSV directly into the format you need
update_records = []
with open(csv_file, "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        update_records.append({
            "fields": {
                "EmployeeID": row["EmployeeID"],
                "WorkDays": int(row["WorkDays"])  # Convert to int if needed
            }
        })

# Your final payload
update_payload = {"records": update_records}

This builds your payload directly. Way simpler.

Watch out though - Airtable caps PATCH requests at 10 records. If your CSV’s bigger, batch it:

for i in range(0, len(update_records), 10):
    batch = update_records[i:i+10]
    payload = {"records": batch}
    # Make your API call here

Hit this limit during an employee sync last year. Wasted hours debugging it.

3

danwilson85’s right - you need those Airtable record IDs. It’s a two-step process: grab all existing records to map EmployeeID to record ID, then use those IDs for updates.

I hit this same issue syncing timesheet data. Here’s what worked:

# First, get existing records and build lookup
response = requests.get(airtable_url, headers=headers)
existing_records = response.json()['records']

employee_lookup = {}
for record in existing_records:
    emp_id = record['fields'].get('EmployeeID')
    if emp_id:
        employee_lookup[emp_id] = record['id']

# Then build update payload with record IDs
update_records = []
with open(csv_file, "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        emp_id = row["EmployeeID"]
        if emp_id in employee_lookup:
            update_records.append({
                "id": employee_lookup[emp_id],
                "fields": {"WorkDays": int(row["WorkDays"])}
            })

Skip the lookup step and you’ll create duplicates instead of updating. Yeah, the GET request adds overhead, but it’s the only way to do proper updates.

4

btw if you’re updating existing records, you need the actual Airtable record IDs, not just employeeID. Your payload should be {“id”: “rec123ABC”, “fields”: {“WorkDays”: 20}} for PATCH to work. Without the record ID, you’ll create new records instead of updating the ones you want.

5

Your CSV processing is way too complex. You’re reading into a dictionary, converting to JSON, then back to dict - just work with the CSV data directly.

I’ve been syncing with Airtable for 3 years and learned this the hard way. Your real problem isn’t data formatting - you’re missing the lookup step everyone mentioned. But there’s another approach using Airtable’s formula fields that’ll simplify things.

Add a formula field in Airtable that concatenates EmployeeID with some prefix, then use that for searching. You can hit the records endpoint with filterByFormula to find exact matches without downloading your entire table first.

with open(csv_file, "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        # Use filterByFormula to find the specific record
        filter_formula = f"{{EmployeeID}}='{row['EmployeeID']}'"
        # Then update that specific record

This scales way better than fetching all records upfront, especially with thousands of employees. API calls take longer but you won’t hit memory issues with large datasets.