Preserve commas in CSV export for Airtable using Python

Other Questions Airtable
1

I’m having trouble exporting a Python table to CSV for use in Airtable. The problem is with strings containing commas. When I open the CSV in Excel it looks fine but Airtable messes up the formatting.

Here’s what I’m seeing:

name|lastname|instruments|
john,smith,"guitar,drums,bass"| <-- PROBLEM
jane|doe|piano

Instead of:

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

My current export 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], lastnames[i], instruments[i]])

Some of my data has commas:

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

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

2

I encountered a similar issue when exporting data to Airtable. The problem lies in how Airtable interprets CSV files. Here’s what worked for me:

  1. Use a tab delimiter instead of a comma.
  2. Enclose all fields in double quotes, not just those with commas.

Try modifying your export code like this:

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

This approach should preserve your data structure when imported into Airtable. If you still face issues, consider using Airtable’s API for direct data insertion. It’s more reliable for complex data structures and eliminates CSV parsing problems altogether.

3

hey there! tried using pandas? it’s pretty good for handling csv stuff. here’s a quick example:

import pandas as pd

df = pd.DataFrame({'name': names, 'lastname': lastnames, 'instruments': instruments})
df.to_csv('music_data.csv', sep='|', index=False, quoting=1)

this should give u a csv that airtable can handle. good luck!

4

Have you considered using Airtable’s built-in CSV import settings? When importing your CSV, you can specify the delimiter and text qualifier. Set the delimiter to ‘|’ and the text qualifier to ‘"’. This way, Airtable will correctly interpret your CSV structure.

Alternatively, you could modify your Python code to use the pipe character as a delimiter:

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

This should generate a CSV that Airtable can parse correctly, preserving your comma-separated values within fields.