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

31–40 of 459 posts

Re: Friends don't let friends export to CSV

#31

Post by barrkel »

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…

On the topic of nested CSV, three approaches:

- treat it as a join, and unroll by duplicating non-nested CSV data in separate rows for every element in the nested CSV

- treat it as a projection, have an extraction operator to project the cells you want

- treat it as text substitution problem; I've seen CSV files where every line of CSV was quoted like it was a single cell in a larger CSV row

You get nested CSV because upstream systems are often master/detail or XML but need to use CSV because everybody understands CSV because it's such a simple file format. Good stuff.

Re: Friends don't let friends export to CSV

#34

Post by fragmede »

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…

Yeah but we're not in the dark ages of computers anymore. Export to Sqlite database instead.

Re: Friends don't let friends export to CSV

#36

Post by barrkel »

fragmede wrote: post #34
barrkel wrote: post #20

Earlier quoted context omitted.

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…

Yeah but we're not in the dark ages of computers anymore. Export to Sqlite database instead.

Sure, you tell the finance industry that. They have systems, the systems already produce CSV. They can sign a contract worth multiples of your salary if you can consume it. Do you want the money or not?

Re: Friends don't let friends export to CSV

#37

Post by rietta »

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 deliminator is a setting that can be changed. I never said hard code. This is an interesting exercise to give students, but I am struggling to look back in my mind through the last 25 years of line of business application development where any of this was intractable. My approach has been to leverage objects. I have a base class for a CsvWriter and CsvReader that does RFC compliant work. I get the business stake holders to provide samples of files they need to import or export. I look at those with my eyes and make sub-classes as needed.

And data type influencing is a fun side project. I worked for a while on a fully generic CSV to SQL converter. Basically you end up with regex matches for different formats and you keep a running tally of errors encountered and then do a best fit for the column. Using a single CSV file is consistent with itself for weird formatting induced by whatever process the other side used. It actually worked really well, even on multi gigabyte CSV files from medical companies that one of my clients had to analyze with a standard set of SQL reports.

Re: Friends don't let friends export to CSV

#38

Post by anymouse123456 »

Closi wrote: post #12

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.

This is the biggest win IME.

You have a (usually) portable transport format that can get the information into and out of an enormous variety of tools that do not necessarily require a software engineer in the middle.

I'm also struggling with such a quick dismissal of human readable formats. It's a huge feature.

What happens when there's a problem with a single CSV file in some pipeline that's been happily running fine for years? You can edit the thing and move on with your day. If the format isn't human readable, now you may have to make and push a software update to handle it.

Of course, CSV is a terrible format that can be horribly painful. No argument there.

But despite the pain, it's still far better than many alternatives. In many situations.

Re: Friends don't let friends export to CSV

#39

Post by [deleted] »

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…

[deleted]

Re: Friends don't let friends export to CSV

#40

Post by JumpCrisscross »

addandsubtract wrote: post #32
benob wrote: post #15

Can Parquet be read/parsed in almost every programming language with very little effort?

More importantly, can you open parquet files in Excel?

No, you have to convert it to CSV first [1]. Or install a driver [2].

[1] https://www.gigasheet.com/post/how-to-open-parquet-file

[2] https://www.cdata.com/kb/tech/parquet-odbc-excel-query.rst

Post reply on HN