>Excel hates CSV. It clearly means CSV must be doing something right.
Use tabs as a delimiter and excel interops with the format as if natively.
Use tabs as a delimiter and excel interops with the format as if natively.
Everyone uses , or ; as delimiters and then uses either . or , for decimals, depending on the source.
It shouldnt be so hard to auto-detect these different formats, but somehow in 2025, Excel still cannot do it.
If they don't then what you could do is create a simple script that just adds that line, and Excel will open the files without you having to hassle with making sure Excel interprets them correctly. Of course that's a bit more challenging if they use different separators, but you might be able to find an easy adaptation for your usecase like making a decision about which delimiter to declare based on the filename. Or you could try to analyze the header row to figure out which delimiter to use based on that.
For whatever reason, pipe seems to be support common in health care data.
Most arguments for or against one apply to all.
It is A CSV tool, readily available in the business world, that often works quite well.
And your argument about comma separators is wrong; the string
1,234
in a CSV file SHOULD mean "two values: 1 and 234", regardless of the local decimal separator. The number one thousand two hundred thirty-four is represented as
"1,234"
1,234
in a CSV file SHOULD mean "two values: 1 and 234", regardless of the local decimal separator."
Yes, I agree, it SHOULD mean that, but that is NOT what Excel does when the decimal separator is set to "," in the regional settings. Excel wrongly thinks it should apply the comma as decimal separator, and reads that number as 1 unit and 234 thousands.
Locale MUST NOT be used for data formats, but Excel does it anyway.
This problem doesn't manifest itself when you're using a locale which matches the CSV's separators. Consider yourself lucky if you're in that situation.