Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Friends don't let friends export to CSV

kaveland.no

Post reply on HN
Print view View original

41–50 of 459 posts

Re: Friends don't let friends export to CSV

#41

Post by danirod »

Friends don't let friends export to CSV -- in the data science field.

But outside the data science field, my experience working on software programming these years is that it won't matter how beautiful your backoffice dashboards and web apps are, many non-technical business users will demand at some point CSV import and/or export capabilities, because it is easier for them to just dump all the data on a system into Excel/Sheets to make reports, or to bulk edit the data via export-excel-import rather than dealing with the navigation model and maybe tens of browser tabs in your app.

Re: Friends don't let friends export to CSV

#42

Post by [deleted] »

barrkel wrote: post #20
rietta wrote: post #5

Or export to CSV correctly and test with Excel and/or LibreOffice. Honestly CSV is a very simple, well defined format, that is decades old and is “obvious”. I’ve had far more trouble with various export to excel functions over the years, that have much more complex third-party dependencies to function. Parsing CSV correctly is not hard, you just can’t use split and be done with it. This has been my coding kata in eve…

CSV is not well-defined. Data in the wild doesn't even agree that it's comma separated. String encoding? Dates? Formatted numbers? Booleans (T/F/Y/N/etc)? Nested quotes? Nested CSV!? How about intermediate systems that muck things up. String encoding going through a pipeline with a misconfiguration in the middle. Data with US dates pasted into UK Excel and converted back into CSV, so that the data is a mix of m/d/yy…

[deleted]

Re: Friends don't let friends export to CSV

#43

Post by JumpCrisscross »

cm2187 wrote: post #30
rietta wrote: post #5

Or export to CSV correctly and test with Excel and/or LibreOffice. Honestly CSV is a very simple, well defined format, that is decades old and is “obvious”. I’ve had far more trouble with various export to excel functions over the years, that have much more complex third-party dependencies to function. Parsing CSV correctly is not hard, you just can’t use split and be done with it. This has been my coding kata in eve…

One example that will kill loading a csv in excel beyond the usual dates problem. If you open in excel a csv file that has some large id stored as int64, they will be converted to an excel number (I suspect a double) and rounded. Also if you have a text column but where some of the codes are numeric with leading zeros, the leading zeros will be lost. And NULL is treated as the string "NULL". I am aware you can import…

> aware you can import a csv file in excel by manually defining the column types but few people use that

And what fraction of those users would be able to anything with another format?

> an extension of the csv format with one extra top row to define the type of each column

If the goal is foolproof export to Excel, use XLSX.

Re: Friends don't let friends export to CSV

#44

Post by frizlab »

rietta wrote: post #5

Or export to CSV correctly and test with Excel and/or LibreOffice. Honestly CSV is a very simple, well defined format, that is decades old and is “obvious”. I’ve had far more trouble with various export to excel functions over the years, that have much more complex third-party dependencies to function. Parsing CSV correctly is not hard, you just can’t use split and be done with it. This has been my coding kata in eve…

Excel will literally use a different value separator depending on the locale of the machine (if the decimal separator for numbers is a comma and not a dot, it’ll use a semicolon as a value separator instead of a comma).

Re: Friends don't let friends export to CSV

#46

Post by rietta »

dtech wrote: post #11
rietta wrote: post #5

Or export to CSV correctly and test with Excel and/or LibreOffice. Honestly CSV is a very simple, well defined format, that is decades old and is “obvious”. I’ve had far more trouble with various export to excel functions over the years, that have much more complex third-party dependencies to function. Parsing CSV correctly is not hard, you just can’t use split and be done with it. This has been my coding kata in eve…

Unfortunately that is only the case as long as you stay within the US. for non-US users Excel has pretty annoying defaults, such as defaulting to ; instead of , as a separator for "CSV", or trouble because other languages and Excel instances use , instead of . for decimal separators. A nice alternative I've used often is to constructor an excel table and then giving is an .xls extension, which Excel happily accepts a…

Localization is a thing. None of this is a show stopper. Subclasses and configuration screen for import and export.

Re: Friends don't let friends export to CSV

#47

Post by wruza »

barrkel wrote: post #20
rietta wrote: post #5

Or export to CSV correctly and test with Excel and/or LibreOffice. Honestly CSV is a very simple, well defined format, that is decades old and is “obvious”. I’ve had far more trouble with various export to excel functions over the years, that have much more complex third-party dependencies to function. Parsing CSV correctly is not hard, you just can’t use split and be done with it. This has been my coding kata in eve…

CSV is not well-defined. Data in the wild doesn't even agree that it's comma separated. String encoding? Dates? Formatted numbers? Booleans (T/F/Y/N/etc)? Nested quotes? Nested CSV!? How about intermediate systems that muck things up. String encoding going through a pipeline with a misconfiguration in the middle. Data with US dates pasted into UK Excel and converted back into CSV, so that the data is a mix of m/d/yy…

Intermediate systems like Excel will break anything, they aren’t constrained to CSV. Excel screws up at the level of a cell value, not at the file format.

Re: Friends don't let friends export to CSV

#48

Post by wruza »

Macha wrote: post #14
rietta wrote: post #5

Or export to CSV correctly and test with Excel and/or LibreOffice. Honestly CSV is a very simple, well defined format, that is decades old and is “obvious”. I’ve had far more trouble with various export to excel functions over the years, that have much more complex third-party dependencies to function. Parsing CSV correctly is not hard, you just can’t use split and be done with it. This has been my coding kata in eve…

> Parsing CSV correctly is not hard, you just can’t use split and be done with it. Parsing RFC-compliant CSVs and telling clients to go away with non-compliant CSVs is not hard. Parsing real world CSVs reliably is simply impossible. The best you can do is heuristics. How do you interpret this row of CSV data? 1,5,The quotation mark "" is used...,2021-1-1 What is the third column? The RFC says that it should just be l…

The RFC 4180 says:

5. Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.

Re: Friends don't let friends export to CSV

#49

Post by tracker1 »

I tend to prefer line delimited JSON myself, even if it's got redundant information. It will gzip pretty well in the data if you want to use less storage space.

Either that or use the ASCII codes for field and row delimiters on a UTF-8 file without a BOM.

Even then you're still stuck with data encoding issues with numbers and booleans. And that direct even cover all the holes I've seen in CSV in real world use by banks and govt agencies over the years.

When I've had to deal with varying imports I push for a scripted (js/TS or Python) preprocessor that takes the vender/client format and normalized to line delimited JSON, then that output gets imported. It's far easier than trying to create a flexible importer application.

Edit: I've also advocated for using SQLite3 files for import, export and archival work.

Re: Friends don't let friends export to CSV

#50

Post by Macha »

wruza wrote: post #48
Macha wrote: post #14

Earlier quoted context omitted.

> Parsing CSV correctly is not hard, you just can’t use split and be done with it. Parsing RFC-compliant CSVs and telling clients to go away with non-compliant CSVs is not hard. Parsing real world CSVs reliably is simply impossible. The best you can do is heuristics. How do you interpret this row of CSV data? 1,5,The quotation mark "" is used...,2021-1-1 What is the third column? The RFC says that it should just be l…

The RFC 4180 says: 5. Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.

The point is your clients and partner firms don't actually care what RFC 4180 says, they just expect you to deal with whatever their CSV library spits out.
Post reply on HN