Python CSV export issue: Preserving commas for Airtable import

Other Questions Airtable
1

I’m having trouble exporting a Python table to CSV for use in Airtable. The problem is with commas in string fields. When I open the CSV in Excel, it looks fine. But Airtable doesn’t handle the commas well.

Here’s what happens:

name|surname|instruments|
john,smith,"violin,piano,guitar"| <- PROBLEM
jane|doe|flute

It should look like this:

name  | surname | instruments
------+---------+------------------
john  | smith   | violin, piano, guitar
jane  | doe     | flute

Is there a way to fix this when exporting the CSV from Python? Here’s my current code:

with open('music_data.csv', 'w', newline='') as file:
    writer = csv.writer(file, delimiter='|', quotechar='"')
    for i in range(len(names)):
        writer.writerow([names[i], surnames[i], instruments[i]])

Some instrument fields have commas. For example:

names = ['john', 'jane']
surnames = ['smith', 'doe']
instruments = ['violin, piano, guitar', 'flute']

Any ideas on how to solve this?

2

I’ve dealt with this exact issue before when working on a music database project. The key is to use the right CSV dialect that Airtable can understand. Here’s what worked for me:

import csv

with open('music_data.csv', 'w', newline='') as file:
    writer = csv.writer(file, dialect='excel')
    writer.writerow(['name', 'surname', 'instruments'])  # Write header
    for name, surname, instrument in zip(names, surnames, instruments):
        writer.writerow([name, surname, instrument])

This approach uses the ‘excel’ dialect, which is compatible with most spreadsheet applications, including Airtable. It automatically handles quoting and escaping of special characters, so you don’t need to worry about the commas in your instrument fields.

Also, make sure to include a header row in your CSV. Airtable uses this to determine column names, which can help with proper data interpretation during import.

3

try using csv.writer with quoting=csv.QUOTE_ALL. this forces quotes around all fields, which should help airtable interpret commas correctly. also, make sure you’re using ‘,’ as delimiter instead of ‘|’ when writing the csv. that might solve your problem!

4

I’ve encountered similar issues when importing CSVs to Airtable. The problem likely stems from using the pipe character as a delimiter. Airtable expects comma-separated values by default. Try modifying your code to use commas instead:

with open('music_data.csv', 'w', newline='') as file:
    writer = csv.writer(file, quoting=csv.QUOTE_NONNUMERIC)
    for i in range(len(names)):
        writer.writerow([names[i], surnames[i], instruments[i]])

This approach uses commas as delimiters and quotes non-numeric fields, which should preserve the commas within the instruments field. Airtable should then interpret the CSV correctly during import.