4 ms·
There's a current debate on my team if we should accept semicolon delimited files as CSVs. You can't even rely on commas in CSVs. I would say any format that
by mulmen 6y ago
There's a current debate on my team if we should accept semicolon delimited files as CSVs. You can't even rely on commas in CSVs.
I would say any format that is compatible with Excel is not suitable for archival. Ever open a file with numeric values with more than say, 20 (I forget the actual cut off) digits? Excel converts to scientific notation and loses precision. When you change the format there is data loss. The length data is preserved but not the full precision.
- arethuza 6y agoI saw a problem recently where a file had a .csv extension but was actually colon delimited. The data contained fields with large quantities of text so a CSV parser happily parsed the file splitting lines on commas in the text and didn't complain so the problem wasn't noticed until much later in the processing of the data.
- dspillett 6y agoOne of our clients internally uses an odd text file format with ~ as the field delimiter and a fixed-width based header & footer containing metadata. And still sometimes send such files with names ending .csv. ~ is a better choice for field separator though, it is far less commonly used in most datasets and natural languages. | can also make sense for the same reason, and visually looks like a column divider as an extra bonus.
- Pamar 6y agoThis is a common problem when you receive csv from someone working in a different country: comma is a decimal separator in some cases (e.g.: Germany) so Excel will automatically switch to semicolon for separators. So now your colleague in the UK office (or even someone who simply prefers using Windows in with US locale, or, as in my case, got a UK laptop because the German keyboards has some layout differences to what they consider "normal"...) double clicks on the "German" .csv... Excel will show (most of the time) every line as a single FIELD. So you have to close it and use Data Import. We just moved to the latest version of Office 365 at the job and it still works like this (even if the import action can usually figure out that semicolon is the separator without me having to manually specify it...).
- yoz-y 6y agoAs long as you make the Filene extension .scsv Jokes aside, we had that debate in our team way back and decided to follow the rfc and only use commas. The format is often abused, better not create more tools that do so.
- mulmen 6y agoTotally understand and respect that decision. In our case these files come from third parties. We ingest that data, grind it up, sprinkle on our own blend of 11 herbs and spices and serve it to customers. Rejecting that data because it doesn't strictly conform to the RFC makes our product less appetizing. Or we can hold our noses and accept any consistent delimiter while working with the data providers to follow the standards.
- im3w1l 6y agoDo not sniff the format. Instead override the field separator based on source.
- arp242 6y agoIn hindsight, semicolons are the better choice, given that commas are common in many languages as decimal separators. I don't know where there's never been some effort to create a "CSV format specification"; for example the first line could indicate quoting, delimiter, etc. style used in the file. Guess it's never been big enough of a problem for people to take action. "Good enough" (or rather, "not bad enough").
- mulmen 6y ago> In hindsight, semicolons are the better choice, given that commas are common in many languages as decimal separators. Or a pipe. Or the unprintable “field separator” character. > I don't know where there's never been some effort to create a "CSV format specification"; for example the first line could indicate quoting, delimiter, etc. style used in the file. Fundamentally CSV or any delimited format requires agreement between parties for interchange. At that point standards don’t really help because the parties can just agree to anything that works. > Guess it's never been big enough of a problem for people to take action. "Good enough" (or rather, "not bad enough"). Exactly.
- mavhc 6y agoIt's very annoying that ascii includes file and record separator characters and csv still exists
- dspillett 6y agoThe problem there is those code-points are usually non-printable, making the resulting file less human-readable which is often a stated desire for a data transfer file format. Some text editors will show those code points, but others will completely omit them (or worse, reject them as invalid).
- mavhc 6y agoIf they were something people used then editors would support them