Preserving commas in CSV exports for Airtable using Python

Other Questions Airtable
1

Hey everyone! I’m having trouble with my Python script that exports data to a CSV file for Airtable. The issue is that Airtable doesn’t handle commas in string fields correctly when I upload the CSV.

Here’s what’s happening:

name|last_name|skills|
john,smith,"coding,writing,speaking"| <-- PROBLEM
jane|doe|marketing

It should look like this instead:

name  | last_name | skills                |
------|-----------|----------------------|
john  | smith     | coding, writing, speaking |
jane  | doe       | marketing            |

This is my current export code:

with open('data.csv', 'w', newline='') as file:
    writer = csv.writer(file, delimiter='|', quotechar='"')
    for item in data:
        writer.writerow([item['name'], item['last_name'], item['skills']])

Some of my data fields have commas, like the ‘skills’ column. How can I modify my export to keep these commas intact for Airtable? Any ideas would be super helpful!

2

I’ve faced a similar issue when exporting data to Airtable via CSV. The key is to ensure proper quoting of fields containing commas. Here’s what worked for me:

  1. Use the ‘csv.QUOTE_NONNUMERIC’ option when creating your CSV writer. This automatically quotes fields with non-numeric data.

  2. Set the quoting parameter to csv.QUOTE_MINIMAL. This quotes fields only if they contain special characters like commas or quotes.

  3. Use a comma as the delimiter instead of a pipe character, as Airtable expects comma-separated values by default.

Here’s how I modified the code:

with open('data.csv', 'w', newline='') as file:
    writer = csv.writer(file, delimiter=',', quotechar='\"', quoting=csv.QUOTE_MINIMAL)
    for item in data:
        writer.writerow([item['name'], item['last_name'], item['skills']])

This approach should preserve the commas within your fields while ensuring Airtable can correctly interpret the CSV structure. Hope this helps!

3

I’ve encountered this issue before when working with Airtable imports. The problem lies in the delimiter you’re using. Airtable expects comma-separated values, not pipe-separated ones. Here’s what I suggest:

Change your delimiter from ‘|’ to ‘,’ in your CSV writer. Also, use csv.QUOTE_ALL to ensure all fields are quoted, preventing any issues with commas within fields.

Modify your code like this:

with open('data.csv', 'w', newline='') as file:
    writer = csv.writer(file, delimiter=',', quotechar='\"', quoting=csv.QUOTE_ALL)
    for item in data:
        writer.writerow([item['name'], item['last_name'], item['skills']])

This should resolve your problem and allow Airtable to correctly interpret your CSV file, including fields with commas. Let me know if you need any further clarification.