Problems with CSV Export - semi-colon vs comma

[Jessica_Getty]

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. :slightly_smiling_face:
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?

[Tech_Tylercivic]

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.

[Jessica_Getty]

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…

[Chester_McLaugh]

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).

[Anca_Luca]

Hello Jessica,

very late answer, I know, but in case anyone else comes across this.

CSV is actually a rather complex format in terms of special characters and where they can be used and how, regardless of how simple it may seem on a first look. With a few exceptions, Airtable will export a rather valid CSV, taking care of escaping commas or newlines / carriage returns in the cells, etc.
Although the regex above will probably very much do the job (haven’t tested it), manipulating a CSV with a regular expression may be dangerous. Then again, manipulating anything with regular expressions may be dangerous ( see here for more Jeffrey Friedl's Blog » Source of the famous “Now you have two problems” quote ).

So, the best to make such a conversion would be to use a software that can read or interpret the CSV as tabular data and then re-save it, as tabular data as well, but with a different separator. LibreOffice Calc is an excellent, open and free software that manipulates very well CSV as tabular (spreadsheet) data, for both input and output and with lots of options when it comes to separators and special characters (both for reading a CSV and for writing a CSV). For the 5-year old in you: open the file with LibreOffice Calc - if in doubt, validate standard options, hit Save as if it looks good, choose a .csv filename, check the “Edit filter settings” in the save dialog (so that it asks you what separator to save with), and then choose semicolon for “field delimiter”.