Problems with CSV Export - semi-colon vs comma


#1

Hi,

I really want to use Airtable for Data Entry but have the following problem. I’m not very technical so any replies should reply to me like I’m 5.

  1. Airtable exports CSV with comma separated values.
  2. The third party database I’m importing into only accepts semi colon separators.
    How do I transform the comma separators into semi-colon separators?

Related Notes:
I have access to both a PC and a Mac.
I live in Australia where we use commas as currency decimal separators in our system settings.
I tried just find/replace of the commas with semi-colons but of course this replaces all my non-separator commas in paragraphs into semi-colons. :slight_smile:
I thought maybe I could put my paragraphs in Airtable between quotation marks and then somehow use something to say “whenever there’s a comma inside a quotation mark, don’t replace it with a semi-colon.” But I don’t know how to do this. Anyone have any suggestions?


#2

You’re not likely to like my solution as it requires absolutely no semi-colons anywhere in any base table or field. That said, open the CSV file with Notepad and do a global change of all commas to the semi-colon.


#3

As I said, that solution changes all commas in the table, including commas in paragraphs. I only want the comma separators to change.

To anyone with the same problem, I’m currently adding a ; at the end of every excel field then doing a find and replace from ,; in the csv to ; but I’m sure Notepad++ must have an easier way…


#4

This regular expression worked for me.

(,)(?=(?:[^"]|"[^"]*")*$)

I found it at http://stackoverflow.com/questions/632475/regex-to-pick-commas-outside-of-quotes

I tested it at https://regex101.com/ as well as successfully using it on my Mac to convert an Airtable CSV export (with comma’s inside various field values) into a semi-colon delimited CSV that imported into Excel with no problems.

To use the regular expression pasted above, you’ll need a text editor capable of doing Regular Expression search and replace. On the mac, I use Text Mate, Sublime Text, or Text Wrangler (there are many others).