My semicolon delimited CSV export will not open properly on my Mac, only comma delimited.?

Question

I have successfully downloaded a CSV export (comma delimited) on my mac and opened the file but because there are lots of commas in the description fields I decided to try a semicolon delimited export instead.

However, the second file does not open correctly either in TEXT EDITOR or EXCEL - the fields are misaligned.

Tried to revert to the comma version by putting speech marks around the comma (",") but when I did that and imported, the speech marks showed up in the text.

Nic Brough -Adaptavist- · Answer

Don't do it with "find and replace", it's too primitive.  There's all sorts of ways you might do it, but there's actually a really simple trick I've used - set the export separator to "," rather than , then all you have to do is pad the beginning and end of each line with a "

With a "," separator, everything gets treated as content, it shouldn't affect any tables or selections.  You should, of course, check that none of the text contains a "," though.

greg.butler · Answer

Feels like it is something to do with the original export, as my colleague can export OK from his PC (not Mac) and it stays intact on google sheets.

But the file I export does not look OK in excel or google sheets. (Also, it looks wrong when I open his file on my mac but he tells me its something to do with PCs adding extra invisible characters in text data that Mac interprets as, I guess, delimiters..)

greg.butler · Answer

Makes sense. I did try but it still went awry in google sheets.