Updating Airtable Entries with CSV Data Using Python Scripts

Other Questions Airtable
1

Background

I’m working on a project where I need to read information from a CSV file and use it to modify existing entries in my Airtable database through Python.

My Table Structure

Here’s what the API documentation shows for updating records:

curl -X PATCH https://api.airtable.com/v0/APP_ID/TABLE_NAME \
  -H "Authorization: Bearer API_TOKEN" \
  -H "Content-Type: application/json" \
  --data '{
  "records": [
    {
      "id": "recABC123XYZ789",
      "fields": {
        "LastName": "Smith",
        "FirstName": "John",
        "StatusField": "Active",
        "EmployeeID": "E123456",
        "PhoneNumber": "0612345678",
        "MonthlySalary": 8000,
        "WorkDaysCount": 22,
        "StartDate": "2021-03-15",
        "AdvanceRate": 0.25,
        "EmailAddress": "[email protected]",
        "MaxAdvanceAmount": 4000,
        "MonthlyAdvanceLimit": 2,
        "FreeAdvancesPerMonth": 1,
        "ProcessingFee": 8
      }
    }
  ]
}'

What I Want to Accomplish

I only need to update the WorkDaysCount field, and I’ll use EmployeeID as my lookup key.

My CSV structure is simple:

EmployeeID,WorkDaysCount

Current Progress

Step 1 - Reading CSV data:

with open(csv_file, "rt") as file_handle:
    reader = csv.DictReader(file_handle)
    for index, row in enumerate(reader):
        csv_data[index] = row

Step 2 - Converting to JSON format:

formatted_data = json.loads(json.dumps(csv_data, indent=4))

Step 3 - This is where I’m having trouble. I need to transform my CSV data into the proper Airtable format:

update_payload = {
    "records": [
        {
            "fields": {
                "EmployeeID": "some_value",
                "WorkDaysCount": some_number
            }
        }
    ]
}

Possible Solutions I’m Considering

Option A - Build the structure by iterating through my data:

for index in range(len(formatted_data)):
    update_payload["records"][index]["fields"]["EmployeeID"] = some_value
    update_payload["records"][index]["fields"]["WorkDaysCount"] = some_value

Option B - Create a list first, then add it to the main structure

Final Step - Send the update request:

api_base = f"https://api.airtable.com/v0/{os.environ['app_id']}"

request_headers = {
    "Authorization": f"Bearer {os.environ['api_token']}",
    "Content-Type": "application/json",
}

def update_table(table_name, payload):
    api_url = f"{api_base}/{table_name}"
    response = requests.patch(url=api_url, json=payload, headers=request_headers)
    return response.json()

Can someone help me figure out the best way to structure my data for the PATCH request?

2

Had the same issue last week - you’re overcomplicating the CSV part. Just read it directly and build the payload in one go. The real gotcha is you can’t update by EmployeeID directly. You need to grab all records first to get the internal Airtable IDs. Something like record_id = next(r['id'] for r in existing_records if r['fields']['EmployeeID'] == csv_employee_id) then use that in your payload structure.

3

Had the exact same problem updating employee records from payroll CSVs. You’ve got to fetch existing records first to map your EmployeeID to Airtable’s internal record IDs, then batch your updates. Here’s what worked for me:

def update_from_csv(csv_file, table_name):
    # Fetch all records to create EmployeeID -> record ID mapping
    all_records = get_all_records(table_name)
    employee_map = {record['fields']['EmployeeID']: record['id'] for record in all_records}
    
    # Build update records
    updates = []
    with open(csv_file, 'rt') as f:
        for row in csv.DictReader(f):
            emp_id = row['EmployeeID']
            if emp_id in employee_map:
                updates.append({
                    "id": employee_map[emp_id],
                    "fields": {"WorkDaysCount": int(row['WorkDaysCount'])}
                })
    
    # Process in batches of 10
    for i in range(0, len(updates), 10):
        batch = {"records": updates[i:i+10]}
        update_table(table_name, batch)

Key thing: include both the Airtable record ID and only the fields you’re updating. Don’t put EmployeeID in the fields section - you’re just using it for lookup.

4

Your main problem is using EmployeeID as the field for updates. Airtable’s PATCH needs the actual record ID in the payload, not a custom field. You’ve got to fetch existing records first to grab those IDs.

Here’s how I handle this:

def get_record_mapping(table_name):
    # Get all records to map EmployeeID to record IDs
    api_url = f"{api_base}/{table_name}"
    response = requests.get(url=api_url, headers=request_headers)
    records = response.json()['records']
    
    mapping = {}
    for record in records:
        employee_id = record['fields'].get('EmployeeID')
        if employee_id:
            mapping[employee_id] = record['id']
    return mapping

def process_csv_updates(csv_file, table_name):
    id_mapping = get_record_mapping(table_name)
    
    with open(csv_file, 'rt') as file:
        reader = csv.DictReader(file)
        records_to_update = []
        
        for row in reader:
            employee_id = row['EmployeeID']
            if employee_id in id_mapping:
                record = {
                    "id": id_mapping[employee_id],  # This is crucial
                    "fields": {
                        "WorkDaysCount": int(row['WorkDaysCount'])
                    }
                }
                records_to_update.append(record)
    
    return {"records": records_to_update}

See how the record structure uses Airtable’s actual record ID, not the EmployeeID field? That’s what makes PATCH work.

5

Skip the CSV to JSON conversion - just work directly with the CSV data.

Here’s what I use in production:

import csv
import requests

def build_update_payload(csv_file):
    records = []
    
    with open(csv_file, 'rt') as file:
        reader = csv.DictReader(file)
        for row in reader:
            record = {
                "fields": {
                    "EmployeeID": row["EmployeeID"],
                    "WorkDaysCount": int(row["WorkDaysCount"])
                }
            }
            records.append(record)
    
    return {"records": records}

# Usage
update_payload = build_update_payload("your_file.csv")
response = update_table("your_table_name", update_payload)

Watch out though - Airtable caps PATCH requests at 10 records. Got more than 10 rows? You’ll need batching:

def batch_updates(records, batch_size=10):
    for i in range(0, len(records), batch_size):
        batch = records[i:i + batch_size]
        payload = {"records": batch}
        response = update_table("your_table_name", payload)
        print(f"Updated batch {i//batch_size + 1}: {response}")

Also heads up - you’re using EmployeeID as lookup but PATCH needs actual Airtable record IDs. You might need to fetch all records first to map EmployeeID to the real record IDs.

This video breaks down the REST API basics pretty well: