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

21–30 of 459 posts

Re: Friends don't let friends export to CSV

#21

Post by yetihehe »

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…

For added fun, last column should be 1-2-2021.

Re: Friends don't let friends export to CSV

#22

Post by joncrocks »

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…

Indeed. It's like 'text file' - there are many ways to encode and decode these.

Add another munging the to list, ids that 'look like numbers' e.g. `0002345` will get converted to `2,345`. Better be sure to pre-pend ' i.e. `'0002345`

Re: Friends don't let friends export to CSV

#23

Post by vesinisa »

benob wrote: post #15

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

Exactly. The author even opens the article with the nice trivia that CSV has been in use since the 1970s. I don't think anyone disputes that CSV is a very primitive format. But I hope no one uses CSV because it is so performant or well-designed. It is used exactly because it is so universal, which makes the point of comparing against almost any other format moot unless they were around in the 1970s, too.

Re: Friends don't let friends export to CSV

#24

Post by bluenose69 »

benob wrote: post #15

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

I just tried it in R. The relevant package seems to be "arrow", so I did

    install.packages("arrow")
and then I did

    ?read_parquet
to get an example. I tried the example, and got the error message as follows. This sort of error is really quite uncommon in R. So my answer to the "with little effort" is "no", at least for R.

    > tf write_parquet(mtcars, tf)
    Error in parquet___WriterProperties___Builder__create() : 
  Cannot call parquet___WriterProperties___Builder__create(). See https://arrow.apache.org/docs/r/articles/install.html for help installing Arrow C++ libraries.

Re: Friends don't let friends export to CSV

#25

Post by fredguth »

VMG wrote: post #13
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…

What is the encoding of the text file? UTF8, windows-1252?

What is the decimal delimiter “.”, “,”?

Most csv users don’t even know they have to be aware of all of these differences.

Re: Friends don't let friends export to CSV

#26

Post by himinlomax »

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…

> well defined format

No.

Re: Friends don't let friends export to CSV

#27

Post by rrr_oh_man »

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…

> Unfortunately, that is only the case as long as you stay outside the US. For US users Excel has pretty annoying defaults, such as defaulting to , instead of ; as a separator for "CSV", or trouble because US instances of Excel use . instead of , for decimal separators.

Re: Friends don't let friends export to CSV

#29

Post by javcasas »

Black616Angel wrote: post #18

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 r…

For lots of data zip the csv. For REALLY lots of data, think something different.

Re: Friends don't let friends export to CSV

#30

Post by cm2187 »

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 a csv file in excel by manually defining the column types but few people use that.

I'd be fine with an extension of the csv format with one extra top row to define the type of each column.

Post reply on HN