Which delimiter should be used for lists of values inside a CSV file?

Question

I have a CSV file that has lists of values for some fields. They're stored in the database as HTML "ul" elements, but I want to convert them into something more spreadsheet-friendly.

What should I use as my delimiter? I could use escaped commas, pipes, semicolons, or pretty much anything else. Is there some kind of de facto standard people use for this?

Answer 1

It depends largely on the values you are trying to separate. For example, if you're expecting numeric values, it would be unwise to use comma or period as your delimiter. Same goes if the values expect hashes. or pipes

Use a character that has the least possibility of being used in the content. To do this, you might want to analyze your data first so you can easily decide what delimiter to use.

Answer 2

I always liked the TAB character as a separator - works well when importing into Excel too.

Answer 3

Quotes around the item is for this

123, hello, "a, b, c, d", 29

Answer 4

Pipes with quotes as text qualifiers have worked best for me. It makes it easier to read when you open the data file in NotePad++ too.