How to modify Airtable entries using CSV data in Python

Other Questions Airtable
1

I need help with reading CSV data and pushing updates to Airtable records through Python.

My database structure looks like this (from API documentation):

# Sample structure for update request
update_payload = {
    "records": [
        {
            "id": "rec123ABC",
            "fields": {
                "Name": "John",
                "LastName": "Doe", 
                "Active": "Yes",
                "ID_Number": "B222222",
                "Phone": "0123456789",
                "Monthly_Pay": 15000,
                "Working_Days": 22,
                "Start_Date": "2022-01-15",
                "Advance_Rate": 0.25,
                "Contact_Email": "[email protected]",
                "Max_Advance": 3750,
                "Monthly_Advances": 2,
                "Free_Advances_Monthly": 1,
                "Request_Fee": 8
            }
        }
    ]
}

I want to update only the Working_Days field by matching the ID_Number value.

My CSV contains:

ID_Number,Working_Days

Here’s what I’ve done:

  1. Load CSV content:
with open(csv_file, "r") as file:
    reader = csv.DictReader(file)
    for index, row in enumerate(reader):
        csv_data[index] = row
  1. Convert to JSON format:
processed_data = json.loads(json.dumps(csv_data, indent=2))
  1. Need help here - how do I structure this data for the API call?

I know the final structure should be:

api_data = {
    "records": [
        {
            "fields": {
                "ID_Number": "value",
                "Working_Days": value
            }
        }
    ]
}

I’m thinking about either creating an empty template and filling it, or building a list first then adding it to the main object.

Finally, I’ll send the PATCH request:

base_url = 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"{base_url}/{table_name}"
    response = requests.patch(url=api_url, json=payload, headers=request_headers)
    return response.json()

What’s the best way to transform my CSV data into the proper structure for the API?

2

first, pull your existing records using a GET request to get their id fields. then, use filterByFormula with your ID_Number to match. combine that with your CSV data before making the PATCH request to update those records!

3

Yeah, you’ve got to fetch the existing records first since Airtable needs the record ID for updates. Here’s what I do: Pull records using the Airtable API with filterByFormula to match your CSV ID_Numbers. Map each ID_Number to its record ID. Then loop through your CSV and build the update payload by matching ID_Numbers to record IDs. The tricky bit is getting your payload structure right - each record needs both the Airtable record ID and the fields you’re updating. Your CSV-to-dictionary approach works fine, just merge it with the record IDs from Airtable. I batch updates in groups of 10 since that’s Airtable’s PATCH limit. Don’t forget to handle CSV entries that don’t exist in your base.