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

11–20 of 459 posts

Re: Friends don't let friends export to CSV

#11

Post by dtech »

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 and has requires much less user explanation than telling individual users how to get Excel to correctly parse a CSV.

Re: Friends don't let friends export to CSV

#12

Post by Closi »

Of course if you only consider the disadvantages, something looks bad.

The advantages of CSV are pretty massive though - if you support CSV you support import and export into a massive variety of business tools, and there is probably some form of OOTB support.

Re: Friends don't let friends export to CSV

#13

Post by VMG »

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…

FTA:

* What does missing data look like? The empty string, NaN, 0, 1/1-1970, null, nil, NULL, \0?

* What date format will you need to parse? What does 5/5/12 mean?

* How multiline data has been written? Does it use quotation marks, properly escape those inside multiline strings, or maybe it just expects you to count the delimiter and by the way can delimiters occur inside bare strings?

And let me add my own question here:

what is the actual delimiter? Do you support `,`, `;` and `\t`?

Re: Friends don't let friends export to CSV

#14

Post by Macha »

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 literally

> The quotation mark "" is used...

But the reality is that some producers of CSVs, which you will be expected to support, will just blindly apply double quote escaping, and expect you to read:

The quotation mark " is used...

Or maybe you find a CSV producer in the wild (let's say... Spark: https://spark.apache.org/docs/latest/sql-data-sources-csv.ht...) that uses backslash escaping instead.

Re: Friends don't let friends export to CSV

#16

Post by TrackerFF »

The problem, as always, is that you deal with multiple data sources - which you can not control the format of. I work as a data analyst, and in my day-to-day work I collect data from around 10 different sources. It's a mix of csv, json, text, and what not.

Nor can you control the format others want. The reason I have to export to csv, is unfortunately because the people I ship out to use excel for everything - and even though excel does support many different data formats, they either enjoy using .csv (should be mentioned that the import feature in excel works pretty damn well), or have some system written in VBA that parses .csv files.

Re: Friends don't let friends export to CSV

#17

Post by ysris »

Not sure I understand right what this article is about. From my point of view, CSV is an easy way to export data from a system to allow an end user to import it in excel and work on this data. Apart if it's as easy with parquet to import in excel as with a CSV, I'm not sure this is not fixing a problem that doesn't exist. And making things more complicated.

Outside of the context of end user, I don't see any advantages in this compared to xml or json export.

Re: Friends don't let friends export to CSV

#18

Post by Black616Angel »

I never liked articles about how you should replace CSV with some other format while pulling some absolutely idiotic reasons out of their rear...

1. CSV is underspecified Okay, so specify it for your use case and you're done? E.g use rfc3339 instead of the straw-man 1-1-1970 and define how no value looks like, which is mostly an empty string.

2. CSV files have terrible compression and performance Okay, who in their right mind uses a plain-text-file to export 50gb of data? Some file systems don't even support that much. When you are at the stage of REGULARLY shipping around files this big, you should think about a database and not another filetype to send via mail. Performance may be a point, but again, using it for gigantic files is wrong in the first place.

3. There's a better way (insert presentation of a filtype I have never heard of) There is lots of better ways to do this, but: CSV is implemented extremely fast, it is universally known unlike Apache Parquet (or Pickle or ORC or Avro or Feather...) and it is humanly readable.

So in the end: Use it for small data exports where you can specify everything you want or like everywhere, where you can import data, because most software takes CSV as input anyway.

For lots of data use something else.

Friends don't let friends write one-sided articles.

Re: Friends don't let friends export to CSV

#19

Post by bell-cot »

nsjames wrote: post #2

I think the sad reality there is that it's become "the" format that users expect, and more importantly, it's what's integrated into the majority of peripheral services and tools. Like JSON.

Oh, yes. And even if you can convince the client that they're wrong, and you're right - with substantial client datasets, there's always a load of "data not as previously represented" records. Resolving what is going on with those tends to be vastly easier when you can say "look at record 1,234,567" and they can easily do that in their favorite & familiar software.

Re: Friends don't let friends export to CSV

#20

Post by barrkel »

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 and d/m/yy depending on the magnitude of the numbers. Hand-munging of data in Excel generally, so that sometimes the data is misaligned WRT rows and columns.

I've seen things in CSV. I once wrote an expression language to help configure custom CSV import pipelines, because you'd need to iterate a predicate over the data to figure out which columns are which (the misalignment problem above).

Post reply on HN