And here we are, 60 years later still struggling to work out where a record ends...
And if they had that key on their keyboard then you'd have the comma problems all over again: What if a ASCII record seperator shows up in the field?
Modifying Csvs with notepad is rife. I'd wager more than using any one particular code editor.
A few years back while working on something that unavoidably used large quantities of CSV data we would sternly warn people not to use Excel, but people still did it ("But I just looked at it!").
I do a fair amount of work with companies that do "EDI" over CSV (or worse CSV-like - think 2 CSVs jammed together with different formats, no headers, no support for escaping or quoting) and fixed width documents. I can absolutely assure you that humans do open these files in notepad far more often than I'd like.
Often one of the main reasons they don't use things like X12, ASCII separators, etc. is because a "human needs to open it at some point" was a prevailing business decision some number of years ago (think "what happens if the IT system fails? how can we still ship stuff even in a complete emergency") and now it's baked into their documented process so deeply its like shouting into the wind to alter things. Third party warehouses are the worst at this.
In a business context, this happens far more often than you may expect. Sure you can build a custom platform to validate and make people connect to a form connecting to a database - but Excel is a great user interface and everyone knows excel.
A funny incident - we were struggling to build a complex workflow in KNIME where at some points we need user input. Nothing out of the box was great - tools either assume a dashboard paradigm or a data flow paradigm - nothing In between.
One of our creative folks came up with the solution of writing to a CSV and getting KNIME to open it in excel. The user would make changes and save, close excel and continue the workflow in KNIME. Even completely non technical people got it.
Fun fact, Excel will truncate numbers beyond 15 digits unless prefixed with a single quote mark.
It does this without any notification or warning when you save.
https://genomebiology.biomedcentral.com/articles/10.1186/s13...:
“A programmatic scan of leading genomics journals reveals that approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions.”
That paper is from 2016, at least 12 years after the problem was first identified.
If you intended the data to be a string value, then it should have been enclosed in quotes.
That's not part of any CSV specification I've seen, including RFC4180.
Adding a ' before it should work, but for data extracted from arbitrary systems you'll have few guarantees of the sort. Formatting the cells in Excel to display the full value instead of the truncated value also works from memory, but you won't always know ahead of time if this happened later in the file.
In our case it was often mailing identifier barcodes so any loss of precision made them entirely useless.
point is, being not human readable and having no keyboard key, you can reasonably expect those special separators not to.
I suppose I'd consider such special-separated files (26 to 29 from memory?) to be machine generated and machine readable only, not intended as human readable without a bit of extra software or eg. a special emacs mode
The whole article pretty well rams home the fact there is no standard B because there is no standard which is adhered to.
But you’re correct that I was using the word “standard” imprecisely to mean something more like “file extension” rather than an IETF RFC.
As much as I wish that beauty or usability was the primary indicator of market success, the simpler explanation that explains all of these (including CSV) is: Microsoft Office supported them.
Microsoft’s binary Excel format is not XML -- you’re thinking of its replacement. It was COM/OLE or some such.
It's basically use the right tool for the job. For pixels that's a binary format. For mostly text, text itself works pretty well.
Anyone can edit a csv. I often have. An important feature.
The only real stumper in the CSV format is why they didn't use \ as the escape in strings like everyone else. Probably some good reason.
OTOH the ability to just look at it... that I've found very valuable and agree there.
Do people usually compose CSV data by hand? I thought they would use a program like Excel, enter or generate their data in separate cells and then save the file as a CSV. There's no reason why a program like Excel (or any other program for that matter) couldn't use the record separator instead of a comma as a delimiter when generating or consuming such files.
Also, is not necessary to use excel, and a lot of the issues brought up in the article wouldn't be the case with a different delimiter character. Editors could easily be updated to make entering that delimiter character easier to enter by mapping it to a key like tab whenever a file like that is opened.
It just seems like a problem that could be solved, but can't be due to inertia.
Markdown (the original one by John Gruber) does not include any syntax for tables. Other implementations have included it, but there is no standard. As far as I know, using the pipe character to separate columns is indeed quite common, but it is not the only way.
I do a lot of troubleshooting using CLI tools like grep and wc.
I prefer the | pipe character as a delimiter - easy to see, not part of common speech or titles, and enterable via keyboard. Yes, it can exist in the field but less likely.
I think it's like cryptography. Why bother to roll your own when there are people who are cleverer (certainly than me. I don't know about you) who've already put a lot of effort in to this, so just use one of the well tested standard libraries and don't mess with it
They do. People write code to create all sorts of bastardized abominations of “csv” or tab delim or whatever. It’s why the featured article gets reposted every few years. You can define a standard for csv files, but then Excel does it’s own thing and here we are.
Given the inability to standardise on line endings "\n" "\r\n" or "\r" I don't think we can standardise on using unit or record separators
The Library of Congress lists CSV files as one of its preferred formats for archival datasets: https://www.loc.gov/preservation/resources/rfs/data.html
It's made worse by the number of applications where they will see spaces reformatted willy nilly (for instance web forms eating their line breaks.
From there explaining there are spaces and tabs, and that both can look the same on screen but they are different is just asking for trouble outside of our circles.
it's asking for trouble in our circles too. Imagine getting a tab-sep file, opening it up in an editor and having it automatically convert to 4 spaces "because", then sending it back without checking something that doesn't look wrong.
One actually looks like a table the other is a garbled mess.
But users can see the commas in CSV, and they can trivially enter them. Yeah, the result is messy.
The lesson here is that the separator control characters should have been visual and had a visual indicator on keyboard keycaps to indicate how to enter them. Because they aren't and didn't, they are essentially useless for text.
EDIT: I do happen to know how to enter these on *nix systems. The ascii(7) man page tells you how:
034 28 1C FS (file separator) 134 92 5C \ '\\'
035 29 1D GS (group separator) 135 93 5D ]
036 30 1E RS (record separator) 136 94 5E ^
037 31 1F US (unit separator) 137 95 5F _
- non printable character - no single keyboard key - intended for systems, not people - etc
A lot of the crufty edge cases are artifacts of whatever people had to deal with in 1995 to get data into spreadsheets. We all got used to pushing data around with CSV because the people consuming the data needed it.
As a fun exercise write a CSV-like spec but using those ASCII chars.
table = [row.split(unit_sep) for row in data.split(rec_sep)]