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,"guitar,drums,bass"| <- PROBLEM
jane|doe|piano

It should look like this:

name  | surname | instruments
------|---------|------------
john  | smith   | guitar, drums, bass
jane  | doe     | piano

I’m using this code to make the CSV:

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

Some of my data has commas:

names = ['john', 'jane']
surnames = ['smith', 'doe']
instruments = ['guitar, drums, bass', 'piano']

How can I fix this? Is there a way to export the CSV differently?

2

Have you considered using a different delimiter altogether? Instead of a pipe or comma, you could try using a tab character as the delimiter. Airtable generally handles tab-delimited files well, even with commas in the data.

Here’s how you could modify your code:

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

This should create a tab-separated file that Airtable can import correctly, preserving the commas in your instrument lists. Just remember to select ‘tab’ as the delimiter when importing into Airtable. It’s worked well for me in similar situations.

3

hey jackhero77, have u tried escaping the commas in ur instruments list? you could do somethin like this:

instruments = [‘guitar, drums, bass’, ‘piano’]

then use ur original code. this way the commas in the instruments field wont mess up the csv structure. hope this helps!

4

I’ve faced a similar issue when exporting data to Airtable. The problem lies in how Airtable interprets the CSV format, especially with nested commas. Here’s a workaround that worked for me:

Instead of using the csv module, try using pandas to export your data. Pandas handles quoting and escaping characters more reliably for Airtable imports.

Here’s a code snippet that should solve your problem:

import pandas as pd

data = {'name': names, 'surname': surnames, 'instruments': instruments}
df = pd.DataFrame(data)
df.to_csv('music_data.csv', index=False, quoting=1)

This approach quotes all non-numeric values, which helps Airtable correctly interpret fields with commas. Make sure you have pandas installed (pip install pandas) before running this code.

After exporting, your CSV should work seamlessly with Airtable’s import function, preserving the commas in your instrument list. Let me know if you encounter any issues with this method!