7 ms·
Earth-1217: where every CSV file has two header rows: column names and units
- justsomehnguy 3y ago> where every CSV file has two header rows sigh Why people always try to make it harder? Name,string;Qty,units;Weight,kg;Volume,m^3;Just for the sake of it with spaces,string;No don't use comma in the header names - it doesn't make sense,string;
- readthenotes1 3y agoI think it has more to do with Excel properly recognizing the data type rather than humans doing so. It has been too long since I dealt with it to recall the troubles but I know there are things that should be easy that aren't since you can't tell Excel how to interpret the data.
- Kluggy 3y agoIn the fictional world where excel understands the second header row, it could just as easily understand a unit delimiter and units in a single header row. Either would make my day as a programmer though.
- 2nenaoki 3y agoSSV?
- bobbylarrybobby 3y agoWhy not do it the other way? Name;string,Qty;units so that if you just double click to open in excel, or use a naive reader that doesn't understand the format (which most won't), the column names are basically correct?
- cpeterso 3y agoThat’s a great idea and useful fallback behavior for file importers that don’t understand the type annotations. Stylistically, I suggest using a colon like many programming languages do for type annotations: Name:string,Qty:units
- secondcoming 3y ago> No don't use comma in the header names You just know that someone somewhere will do just that and you'll have to deal with it as best you can.
- candiddevmike 3y agoBut this will break all of my unquoted columns with semicolons in them!
- mjevans 3y agoEven a newline could be used per header to add a second 'line' that has the units. It makes sense to humans and computers could easily read that.
- demondemidi 3y agoThere's a simpler answer: if you need something more sophisticated than CSV, then don't use CSV. This attitude in young programmers kills me. It's the old "let's make one tool that does everything, and i (re)invented it!" attitude they get after a few years on the job.
- elzbardico 3y agoI am old too, but if anything I think that young programmers nowadays are not nearly as iconoclasts and rebels as they used to be. And I do miss that.
- demondemidi 3y agothat's because of the cambrian explosion of what being a "programmer" means today. there's no time to learn all the history anymore. at the start of the PC era it was possible to understand every microprocessor architecture on the market and how to program them and programming languages for PC applications were sparse (assembly was pretty much the only viable choice although some profitable applications were written in Pascal). i may be off base here but i think 40 years ago the majority of programmers knew 80-90% of the history and you just can't do that today unless your career is "historian".
- nullserver 3y agoThese days it’s massive challenge to stay on top of JavaScript and its frameworks. Never-mind anything else.
- croes 3y agoIsn't it the opposite? Instead of make something completely new that does everything expand the well known old by some useful features?
- demondemidi 3y agoMy 0.02$: For something as deeply embedded and controversial as CSV this won’t work. I say this because I’ve been watching the battle since the 1990s. CSV has a lot of problems, but it is only an issue if you try to make it do too much. For a current example look at JSON. The original BNR grammar fit on the back of a business card. It has major issues too. Yet it hasn’t successfully been replaced or augmented without creating even worse problems, like compatibility. Putting lipstick on a pig, like this suggestion for CSV, just creates incompatibility and even more headaches.
- SiempreViernes 3y agoImagine their face when they discover they actually live on Earth-1217: https://docs.astropy.org/en/stable/io/ascii/ecsv.html https://docs.astropy.org/en/stable/io/ascii/ecsv.html
- malkia 3y agoI suggest listening to this https://www.youtube.com/watch?v=OBmNu6lQU2o https://www.youtube.com/watch?v=OBmNu6lQU2o while reading :)
- Animats 3y agoColumn headers are a known issue in research data management.[1] Research projects tend to generate large amounts of data from instruments. The raw data is often archived. Years or decades later, someone may find something important in that expensively collected data. [1] https://libguides.d.umn.edu/c.php?g=307731&p=2176534 https://libguides.d.umn.edu/c.php?g=307731&p=2176534
- csrtraveler 3y agoOnly Microsoft can fix this; no enhanced CSV proposal will matter until it’s baked into Excel. TSV is marginally better, but not enough. CSV is the universal file format, and will be forever; nothing else is ever as readable and as portable. You can’t easily get JSON out of an Excel sheet, or easily read it in any text editor. CSV needs just a few tweaks to make it easier to avoid comma, double-quote, and newline issues that suck a bazillion dollars of productivity out of the economy each year with broken data imports. This is probably the lowest hanging fruit in all of technology: a small change to Excel to dramatically improve data processing / import-export for all humanity.
- frizlab 3y agoThe worst thing by far in Excel’s implementation of CSV is the separator is actually a semicolon when the computer’s locale uses a comma for separating the decimals in a number.
- no_wizard 3y agoI agree. Excel being so dominant in its market segments makes data interop harder for no other reason than they use proprietary default formats and don’t quite support CSV in a conformant way Not quite related but I once saw a proposal that file formats should be regulated to require documented interoperability. This change would foster a ton of competition and evolve standards as an industry to the benefit to all is the argument in a nutshell. If XLSX was documented for interop it would also go a long way in data independence
- zokier 3y agoYou mean like ECMA-376, ISO/IEC 29500? See also https://learn.microsoft.com/en-us/openspecs/main/ms-openspeclp/3589baea-5b22-48f2-9d43-f5bea4960ddb https://learn.microsoft.com/en-us/openspecs/main/ms-openspec... Some historical note: https://www.computerworld.com/article/2526864/microsoft-offers-office-2010-file-format--ballot--to-stop-eu-antitrust-pro.html https://www.computerworld.com/article/2526864/microsoft-offe...
- secondcoming 3y ago
- zokier 3y agoOr just use something like parquet that has both typed columns and allows arbitrary metadata for columns: https://mungingdata.com/pyarrow/arbitrary-metadata-parquet-table/ https://mungingdata.com/pyarrow/arbitrary-metadata-parquet-t...
- anon84873628 3y agoWell, sending data between machines isn't the problem. Presumably the reason for choosing CSV in the first place is to make it editable by humans in a basic text editor. People get in to trouble when they forget that or try to use it for other uses cases instead.
- zokier 3y ago> Presumably the reason for choosing CSV in the first place is to make it editable by humans in a basic text editor That's quite a presumption
- mbreese 3y agoYeah, I can’t think of a reason why I’d really want to hand edit a CSV file. I think the rationale behind using CSV is even more basic. The reason why CSV is so popular (and TSV, HTML and JSON for that matter), is that it is a human readable format. It is much easier to troubleshoot and debug data and data flows if you can open a file and see it in an editor. Especially when trying to move data between systems/people/groups. You don’t really need to edit the file, just read and mentally parse it if there is a problem. This is why the most popular formats are text based. It’s only when the verbosity of text is too great that we move to binary, such as for images and media (xpm excepted). For most other data, compression tends to be “good enough”.
- nicoburns 3y agoWhat I really want is a tabular format the enables formatting to be interpolated between sections of data with a clean separation of structured data and formatted human-readable content. It would need a new editor of course, but I reckon it could be popular if that editor was good.
- bradrn 3y agoThis sounds interesting, but I’m not sure I understand your proposal fully… what do you mean by ‘interpolating’ formatting?
- nicoburns 3y agoParticularly in a business context, I find that spreadsheets often need a bunch of supporting data that exists outside of the main tabular content: headings, introduction paragraphs, legends, etc. I would like a way to visually combine that with the tabular data while keeping the too distinct in the underling format such that: - Constraints (e.g. type or format constraints) could be applied to the tabular data - The non-tabular data is free-form word processor style It would also be be useful to be able to combine multiple tables with the same visual page.
- bradrn 3y ago> combine multiple tables with the same visual page In fact, this is why I asked — I’m actively working on an program which allows just this. In theory, it should be sufficiently flexible to allow for the kind of markup you mention… one more thing to look into!
- acheron 3y agoIf we’re imagining a parallel universe where things make sense, let’s get rid of CSV files altogether and use the ASCII field separator characters. https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_separators https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_...
- EvanAnderson 3y agoYes. I did a project ETL'ing data that used the ASCII field and record separators once. It was a joy to deal with compared to CSV.
- cpeterso 3y agoThis is definitely the Right Thing, but how well did other software, such as text editors, handle or render the field and record separator characters? That you can’t directly enter these characters on a keyboard would be a minor annoyance.
- EvanAnderson 3y agoThe file I used was being generated by a partner company's ERP system so I didn't really have to make any files myself. Having said that, I use a lot of Notepad++ on Windows and using the ALT-029, ALT-028, etc, syntax generated the right codes for direct keyboard entry. They're displayed by Notepad++ as inverse text "RS", "GS", etc.
- mjevans 3y agoIs there any reason a 'csv' file can't use E.G. \\002 2 0x02 STX (start of text) OR \036 30 0x1E RS (record separator) as the record separator instead of a comma? The big reason CSV uses commas is to be human readable and editable, but otherwise...
- ot1138 3y agoHa, we used to do this in the 90s with double pipes || and triple pipes |||.
- prpl 3y agojust associate some CSVW with it and don’t limit yourself to just units
- wolverine876 3y agoIs there an open source library for parsing CSVs that has matured to cover 99.9% of edge cases, errors, etc.? We've had decades to build one. Otherwise, many projects must be recreating this wheel.
- johnnyo 3y agoI don’t think so, because their are always ambiguities with CSVs Do they have a header? Is the data properly escaped? How are blank values at the end of the row handled? Etc, etc
- wolverine876 3y ago> their are always ambiguities with CSVs Right, that's the point of a good library, to handle the ambiguities via insightful coding solutions, accumulated over decades - enough solutions to make it worth using the library. Other ambiguities can be handled with Q&A which result in automated solutions: 'the text looks like either X or Y, which is it?', and then disambiguating the text based on the answer. Another benefit would be that, if the library became useful and widely used enough, it would become an unofficial standard: people would make CSV's compatible with the library and its output would become a norm. All a happy fantasy! :)
- johnnyo 3y agoInsightful solutions aren’t always correct solutions. Take a look at how Excel helpfully makes things dates or numbers, even when you don’t want them to be treated as such. There’s no “insightful” way to understand that a column of numbers really wants to be treated like text or that 20-DEC-82 is a product code vs a date.
- marcinzm 3y agoThere's plenty that can perfectly parse RFC 4180 since it's a very simple format. That's not, however, what people want in terms of parsing "CSV" files. They want to parse a file that contains data in some type of delimited format into some type of arbitrary format they envision. The delimiter may not even be a comma.
- somat 3y agoSo am I the only one who names my database columns like height_cm, speed_kmh, weight_kg
- deleted 3y ago[deleted]
- JohnBooty 3y agoNO! There are at least two of us! I've done this ever since NASA lost the Mars Climate Orbiter due to a unit conversion error. https://en.wikipedia.org/wiki/Mars_Climate_Orbiter#Cause_of_failure https://en.wikipedia.org/wiki/Mars_Climate_Orbiter#Cause_of_... I truly don't understand how and why people don't do this. Most people use pretty descriptive names now that every editor has autocomplete and big monitors are common. And yet, people never put the damn units in the identifier name. Would save so many bugs, and it would give engineers one less thing to juggle in their working memory.
- lmeyerov 3y agoEarth-1, where we are happily already sharing parquet and arrow files for years now that do that :) The subtle bit is most languages & these formats have data representation (int16, ..) but not actual unit (GB/s) and oh boy....
- davidbanham 3y agoI’d settle for Excel either not automatically reformatting dates when opening a CSV, or having the decency to put them back the way they were when saving it. Silently turning 2016-01-02 into 01/02/2016 should be considered a hate crime.