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

[greg.butler]

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.

[Rob Horan]

If you're in a pinch see if you can open the file in Google Sheets and then export it to a form your Mac can use.

[greg.butler]

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

[greg.butler]

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

[Nic Brough -Adaptavist-]

Try surrounding the text of each field (with commas in it) with quotes, not the separators.

For example, going a bit William Shatner:

The quick, brown, fox, jumped over the, lazy, dog ; Mr flibble says 'time to die'

You've suggested

The quick"," brown"," fox"," jumped over the"," lazy"," dog ; Mr flibble says 'time to die'

But I think you need

"The quick, brown, fox, jumped over the, lazy, dog" ; "Mr flibble says 'time to die'"

[greg.butler]

Thank you for the quick response - the problem is that I can surround the comma using 'find and replace' in excel (12000 instances, 4000 issues). But its more tricky to surround the whole text and there are parts of the text (like tables) which would presumably not format correctly if encapsulated by speech marks?

[Nic Brough -Adaptavist-]

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.