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

101–110 of 459 posts

Re: Friends don't let friends export to CSV

#101

Post by Gabriel54 »

GuB-42 wrote: post #97

As a French, there is another problem with CSV. In the French locale, the decimal point is the comma, so "121.5" is written "121,5". It means, of course, that the comma can't be used as a separator, so the semicolon is used instead. It means that depending whether or not the tool that exports the CSV is localized or not, you get commas or you get semicolons. If you are lucky, the tool that imports it speaks the same…

If I'm not mistaken this is pretty universal outside of the US (and maybe the UK).

Re: Friends don't let friends export to CSV

#102

Post by rzzzt »

JumpCrisscross wrote: post #4

> One of the infurating things about the format is that things often break in ways that tools can't pick up and tell you about This line is emblematic of the paradigm shift LLMs have brought. It’s now easier to build a better tool than change everyone’s behaviour. > You give up human readable files, but What are we even doing here.

So you grab your favorite CllaumistraCoderPT-3.14-Turbo and ask the box to deduce CSV settings from the given example. It comes back with a set of characteristics others have brought up elsewhere in comments (field separator, decimal handling, quotes, date format, etc.)

How do you verify it? What happens next?

Re: Friends don't let friends export to CSV

#103

Post by wlesieutre »

PeterisP wrote: post #90
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.

You can do tab-separated "CSV" and it'll be much better, avoid the quoting and delimiter issues that somehow trip up something half the time, and pretty much all these tools have always supported that format as well.

If only the ASCII 31 "unit separator" were well supported

Re: Friends don't let friends export to CSV

#104

Post by zztop44 »

Gabriel54 wrote: post #101
GuB-42 wrote: post #97

As a French, there is another problem with CSV. In the French locale, the decimal point is the comma, so "121.5" is written "121,5". It means, of course, that the comma can't be used as a separator, so the semicolon is used instead. It means that depending whether or not the tool that exports the CSV is localized or not, you get commas or you get semicolons. If you are lucky, the tool that imports it speaks the same…

If I'm not mistaken this is pretty universal outside of the US (and maybe the UK).

You are mistaken. Probably more countries overall use a decimal comma, but the decimal point is used as convention in many countries, including China, India, Nigeria and the Philippines.

Re: Friends don't let friends export to CSV

#105

Post by baazaa »

Schemas are overrated. Often the source-system can't be trusted so you need to check everything anyway or you'll have random strings in your data. Immature languages/libraries often do dumb stuff like throwing away the timezone before adjusting it to UTC. They might not support certain parquet types (e.g. an interval).

Like I've recently found it much easier to deal with schema evolution in pyspark with a lot of historical CSVs than historical parquets. This is essentially a pyspark problem, but if everything works worse with your data format then maybe it's the format that's the problem. CSV parsing is always and everywhere easy, easier than the problems parquets often throw up.

The only time I'd recommend parquet is if you're setting up a pipeline with file transfer and you control both ends... but that's the easiest possible situation to be in; if your solution only works when it's a very easy problem then it's not a good solution.

Re: Friends don't let friends export to CSV

#106

Post by layer8 »

GuB-42 wrote: post #97

As a French, there is another problem with CSV. In the French locale, the decimal point is the comma, so "121.5" is written "121,5". It means, of course, that the comma can't be used as a separator, so the semicolon is used instead. It means that depending whether or not the tool that exports the CSV is localized or not, you get commas or you get semicolons. If you are lucky, the tool that imports it speaks the same…

There are tools to convert between the formats. Either you have a defined data pipeline where you know what you get at each step and apply the necessary transformations. Or you get random files and, yes, have to inspect them and see how to convert them if necessary.

It’s unfortunate that there isn’t a single CSV format, but for historical reasons it is what it is. It’s effectively more like a family of formats that share the same file extension.

Excel actually has a convention where it understands when there is a line

   sep=;
at the start of the file.

By the way, in addition to differing separators, you can also get different character encodings.

Excel does understand a BOM to indicate UTF-8, but some versions of Excel unfortunately ignore it when the “sep=“ line is present…

Re: Friends don't let friends export to CSV

#107

Post by sfRattan »

Gabriel54 wrote: post #101
GuB-42 wrote: post #97

As a French, there is another problem with CSV. In the French locale, the decimal point is the comma, so "121.5" is written "121,5". It means, of course, that the comma can't be used as a separator, so the semicolon is used instead. It means that depending whether or not the tool that exports the CSV is localized or not, you get commas or you get semicolons. If you are lucky, the tool that imports it speaks the same…

If I'm not mistaken this is pretty universal outside of the US (and maybe the UK).

Going by the Wikipedia article and included map, use of comma versus period as decimal separators is roughly an even split:

https://en.wikipedia.org/wiki/Decimal_separator

https://commons.wikimedia.org/wiki/File:DecimalSeparator.svg

Re: Friends don't let friends export to CSV

#108

Post by pixl97 »

fragmede wrote: post #59
barrkel wrote: post #36

Earlier quoted context omitted.

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?

In case it wasn't clear, I want software engineers in the finance industry to implement sqlite import and export in their various pieces of software, not too give up on lucrative, existing contracts, obviously.

The person in the fintec industry rarely decies "oh, I'm going to add this format no one is asking for". They'll get a specification requiring csv and start implementing it. Get fired halfway though, then someone on the other side of the planet will implement it incorrectly. The first 3 revisions won't meet the customers needs, but the customer can't move away anyway.

Re: Friends don't let friends export to CSV

#109

Post by thristian »

BenjiWiebe wrote: post #100
chasil wrote: post #72

Earlier quoted context omitted.

In a POSIX shell, I actually prefer to use the bell character for IFS. while IFS="$(printf \\a)" read -r field1 field2... do ... done This works just as well as anything outside the range of printing characters. Getting records that contain newlines would be a bit trickier.

I think IFS=$'\a' works too.

Only in bash and possibly other shells that extend the POSIX syntax, not in the basic POSIX standard.

Re: Friends don't let friends export to CSV

#110

Post by senknvd »

nradov wrote: post #66
aronhegedus wrote: post #6

My takeaway is that csv has some undefined behaviours, and it takes up space. I like that everyone knows about .csv files, and it's also completely human readable. So for <100mb I would still use csv.

If both parties implement RFC 4180 and use a consistent character set encoding then I don't think there are actually any undefined behaviors. But in practice a lot of implementations are simply broken, including those from major tech companies that ought to know better.

I don't think RFC 4180 differentiates between an empty string and a null value. As long as you add a check that all string columns are free of empty values before writing you should be good.

I think in polars it's

    df.filter(pl.col(pl.Utf8).str.len_bytes() == 0).shape[0] == 0
although there's probably a better way to write this.
Post reply on HN