4 ms·

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC.

> standardize Convert a CSV file to one that conforms to RFC-4180. Very glad to see this here, hopefully it'll help with increasing adoption of the RFC. I find one of the biggest pains of working with CSV is that all too often, someone gets Excel involved, and then Excel absolutely butchers the file. Off the top of my head: It strips leading 0's (even if quoted), converts big numbers to scientific notation, sometimes interprets non-date values as dates, and when saving, keeps the mangled values, doesn't preserve quotes, and uses regional settings -- to the point in some locales, it becomes a "semi-colon separated file"! Even just opening and then saving a .csv (without editing) can totally mangle it to the point it's unusable. Sometimes Excel can't even open a file saved by Excel without mangling it even more. [1] https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180

Waterluvian 7y ago
That is hilarious, amazing, sad, and begging for a blog post of someone walking through the nightmare you described.
- Maxious 7y ago
  One in five genetics papers contains errors thanks to Microsoft Excel https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...
  - jimnotgym 7y ago
    Blame the calculator...'misuse of Microsoft Excel'
  - bronzecarnage 7y ago
    Nature has the highest percentage of affected papers. Huh.
Twirrim 7y ago
At a previous company, someone decided to experiment with putting a comma in a text field submitted to an internal service that for some godforsaken reason spat out CSV files that a lot of other teams consumed. I forget the exact entry in the field but it was something like "I wonder, how does this handle commas". Turns out it didn't handle them well. It didn't escape them or anything. Out spits this line in the csv format that has more columns than expected and everything from the that field onwards was off by one. Most teams were lucky, their code barfed when presented with a line with too many fields. Some teams were unlucky and it didn't. How unlucky _they_ were was somewhat varied based on how much data validation they did. That caused a big headache for a lot of people trying to recover from the actions of the genius who thought doing that against the production endpoint vs the test endpoint. That incident pretty much solidified my reputation in the team as some kind of weird voodoo type of chaos monkey, because my shifts _always_ had a significantly higher sev2 rate caused by things failing that I'd not touched or had any influence over.
- tmpz22 7y ago
  Better an issue raised by a engineer then that same issue caused by an end user :shrug:
  - Twirrim 7y ago
    The engineer technically was an end user. It was a service that could get called as an indirect result of customer action, but without any customer supplied information passed along, which is part of the reason it didn't do any input clean-up (at least according to the post-incident report). They assumed they could trust people not to screw things up. To be fair, it had been running for a long time that way without anyone doing that to them.
    - tmpz22 7y ago
      All good, I hope my comment didn't come off as snarky because I have 110% been in similar environments!
- rusk 7y ago
AlexMax 7y ago
I cannot for the life of me find the blog post where I learned about this, but I learned long ago that the only reliable way to get Excel to open a CSV file with Unicode in it - regardless of whatever ancient version and OS they're using - is to use tab separators and encode as UTF-16LE with a leading BOM.
- wantoncl 7y ago
  It isn't just Unicode, Excel has been like this for every version I can remember, and I've used v5.0, possibly earlier versions (1992/93). Whenever you have the option, use tab-delimited if it ever has to go into Excel reliably.
  - jimnotgym 7y ago
    For ecommerce sites it is so common to want to go from Excel to UTF8 csv that a lot of people use Libre/Open Office to open and save the Excel file. Then you can do safe things like double-quoting all fields, and choosing your encoding. Perhaps more systems should have Excel upload rather than insisting on UTF-8 csvs that the most common spreadsheet software has never been able to produce?
    - turbinerneiter 7y ago
      Maybe the most common spreadsheet software should get it's act together and support this simple standard?
      - mhd 7y ago
        Excel is mostly responsible for wrecking any attempt of "standard" CSV, not that there ever was a chance. If I remember correctly, non-Anglo CSVs using a semi-colon as a delimiter is fully to blame on it.
        rusk 7y ago
        Its not just non-Anglo .. semicolon is often used because data often contains commas, and it’s just easier to swop your delimiter than encapsulate your fields properly ... even then it’s just a simple matter to use the alternative delimeter, or convert by a simple search and replace (once youve added encapuslation to the relevant fields of course). Though CSV directly implies commas I find it helpful to consider it a synonym for “delimited text”. The specifics of the delimiter don’t bother me so much so long as the format is consistent.
andrewkdinh 7y ago
Funny enough, this is the exact problem I had today at work. The easiest way to fix this problem is to add a tab character (“\t”) somewhere within the value (most likely at the end) and Excel will not format it. For example: “value\t”. Another way is “=“”value”””

Waterluvian 7y ago

That is hilarious, amazing, sad, and begging for a blog post of someone walking through the nightmare you described.

Maxious 7y ago
One in five genetics papers contains errors thanks to Microsoft Excel https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...
- jimnotgym 7y ago
  Blame the calculator...'misuse of Microsoft Excel'
- bronzecarnage 7y ago
  Nature has the highest percentage of affected papers. Huh.

Twirrim 7y ago

At a previous company, someone decided to experiment with putting a comma in a text field submitted to an internal service that for some godforsaken reason spat out CSV files that a lot of other teams consumed. I forget the exact entry in the field but it was something like "I wonder, how does this handle commas". Turns out it didn't handle them well. It didn't escape them or anything. Out spits this line in the csv format that has more columns than expected and everything from the that field onwards was off by one. Most teams were lucky, their code barfed when presented with a line with too many fields. Some teams were unlucky and it didn't. How unlucky _they_ were was somewhat varied based on how much data validation they did. That caused a big headache for a lot of people trying to recover from the actions of the genius who thought doing that against the production endpoint vs the test endpoint. That incident pretty much solidified my reputation in the team as some kind of weird voodoo type of chaos monkey, because my shifts _always_ had a significantly higher sev2 rate caused by things failing that I'd not touched or had any influence over.

tmpz22 7y ago
Better an issue raised by a engineer then that same issue caused by an end user :shrug:
- Twirrim 7y ago
  The engineer technically was an end user. It was a service that could get called as an indirect result of customer action, but without any customer supplied information passed along, which is part of the reason it didn't do any input clean-up (at least according to the post-incident report). They assumed they could trust people not to screw things up. To be fair, it had been running for a long time that way without anyone doing that to them.
  - tmpz22 7y ago
    All good, I hope my comment didn't come off as snarky because I have 110% been in similar environments!
rusk 7y ago

AlexMax 7y ago

I cannot for the life of me find the blog post where I learned about this, but I learned long ago that the only reliable way to get Excel to open a CSV file with Unicode in it - regardless of whatever ancient version and OS they're using - is to use tab separators and encode as UTF-16LE with a leading BOM.

wantoncl 7y ago
It isn't just Unicode, Excel has been like this for every version I can remember, and I've used v5.0, possibly earlier versions (1992/93). Whenever you have the option, use tab-delimited if it ever has to go into Excel reliably.
- jimnotgym 7y ago
  For ecommerce sites it is so common to want to go from Excel to UTF8 csv that a lot of people use Libre/Open Office to open and save the Excel file. Then you can do safe things like double-quoting all fields, and choosing your encoding. Perhaps more systems should have Excel upload rather than insisting on UTF-8 csvs that the most common spreadsheet software has never been able to produce?
  - turbinerneiter 7y ago
    Maybe the most common spreadsheet software should get it's act together and support this simple standard?
    - mhd 7y ago
      Excel is mostly responsible for wrecking any attempt of "standard" CSV, not that there ever was a chance. If I remember correctly, non-Anglo CSVs using a semi-colon as a delimiter is fully to blame on it.
      - rusk 7y ago
        Its not just non-Anglo .. semicolon is often used because data often contains commas, and it’s just easier to swop your delimiter than encapsulate your fields properly ... even then it’s just a simple matter to use the alternative delimeter, or convert by a simple search and replace (once youve added encapuslation to the relevant fields of course). Though CSV directly implies commas I find it helpful to consider it a synonym for “delimited text”. The specifics of the delimiter don’t bother me so much so long as the format is consistent.

andrewkdinh 7y ago

Funny enough, this is the exact problem I had today at work. The easiest way to fix this problem is to add a tab character (“\t”) somewhere within the value (most likely at the end) and Excel will not format it. For example: “value\t”. Another way is “=“”value”””