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

Why isn’t there a decent file format for tabular data?

successfulsoftware.net

Post reply on HN
Print view View original

171–180 of 355 posts

Re: Why isn’t there a decent file format for tabular data?

#171

Post by EdwardDiego »

dfdz wrote: post #128

I think CSV is a decent file format for tabular data. The author claims that CSV files are > difficult to parse efficiently using multiple cores, due to the quoting (you can’t start parsing from part way through a file). But I do not see why this is the case. Step 1: loop over file (in parallel) to determine indices of quote characters Step 2: loop over indices outside quote regions (in parallel) to determine indices…

Indeed, every distributed query engine I've used can easily parallelise CSV in the same file (so long as it's splittable, friends don't let friends gzip their data), with the option to ignore bad rows, log them, or throw your hands up and die.

Admittedly, all of them are Java based and use Hadoop libs for handling CSV, which makes sense, the Elephant ecosystem has spent years getting this stuff right.

Re: Why isn’t there a decent file format for tabular data?

#172

Post by [deleted] »

bezospen15 wrote: post #155
asperous wrote: post #126

Seems like the problem here is there is several high quality and well-developed formats, but the author and the commenters here dismiss them because of the different trade-offs they make. csv -- Simple for simple use cases, text-based, however many edge cases, feature lacking etc xlsx -- Works in excel, ubiquitous format with a standard, however complicated and missing scientific features sqlite -- Designed for relat…

Ther are tons of billion dollar companies that have entire systems utilizing csv and xlsx tubular data for mission critical processes lol

[deleted]

Re: Why isn’t there a decent file format for tabular data?

#173

Post by gnulinux »

habitue wrote: post #46
hermitcrab wrote: post #31

Earlier quoted context omitted.

No doubt binary formats like Parquet are the way to go for high performance with multi-GB datasets. Seems like total overkill if you have a few hundred or thousand rows of data though. Being able to create/edit/view stuff in a text editor and easily version it is very useful.

Do people really edit csvs in a text editor? It's horrific, the columns don't line up at all, empty cells are represented by a bunch of commas in a row (which, are you supposed to count all the commas?) And in terms of versioning, I have seen people commit diffs of csvs before, and they're equally unreadable. CSV is a plain text format, but that basically buys you nothing. As long as you're going to be loading it int…

I frequently edit CSVs by hand. As long as you understand your data, and stakes are low (e.g. it's a one off analysis not prod code) it's pretty easy and convenient.

Re: Why isn’t there a decent file format for tabular data?

#174

Post by mountainriver »

BFLpL0QNek wrote: post #144
losvedir wrote: post #123

Earlier quoted context omitted.

I've been pretty impressed with parquet lately. One thing I've missed is a way to group tables. Is there a standard for that? While parquet is generally column oriented it has support for metadata about tables of multiple columns. However, I'm not aware of any format that groups the tables , short of just zipping a bunch of files. For context, this would be for an application that passes sqlite files around. So natur…

You want to search for “DataFrame” libraries. Another commenter mentioned Spark, Panda’s is another popular one, not used it but think it’s lighter weight where Spark is more for large distributed computation even though can run locally. There’s a bunch of these tools which lets you treat parquet files as tables doing joins, aggregations etc.

Arrow is really the future here

Re: Why isn’t there a decent file format for tabular data?

#175

Post by wombatpm »

1vuio0pswjnm7 wrote: post #100
donatj wrote: post #78

> Columns are separated by \u001F (ASCII unit separator) > Rows are separated by \u001E (ASCII record separator) That's a nightmare to try to edit yourself in a text editor? I'd rather just have basically TSV, but with every value always quoted, always UTF-8. Quotes escaped with backslashes, backslashes escaped with backslashes, and that's it. Any binary allowed between the quotes. I deal with CSVs all day every day.…

"That's a nightmare to try to edit yourself in a text editor?" I have found these "unusual" separators are useful, e.g., I use ASCII file separator (FS). I use tr and sed to add/remove/change separators. If I had to edit a table interactively I would change the separator to something visible before editing. Thats said, in nvi(1) or less(1), US is displayed as highlighted ^_ and RS as highlighted ^^. It is not difficu…

In the printing world inkjet printers for industrial use use a file format that is all RS, GS, US, and FS characters. It had no line breaks instead it used a RS character at the beginning of a record. It would routinely break if people tried to open in a text editor. Nothing wants to deal a 300mb file consisting of a single line. Ended up writing my own library to manipulate the files and used a lot of tr, see, and awk in the command line. It was a pain only because modern editors have forgotten control codes

Re: Why isn’t there a decent file format for tabular data?

#176

Post by pishpash »

MobiusHorizons wrote: post #140
thayne wrote: post #118

Earlier quoted context omitted.

> That's a nightmare to try to edit yourself in a text editor? You just need a text editor that can support thia format.

So… not a text editor then, right?

A Unicode text editor is not an ascii text editor either.

Re: Why isn’t there a decent file format for tabular data?

#177

Post by mountainriver »

tpl wrote: post #170

Parquet is the answer, human readable is not a valid requirement for tabular data anyway. If you absolute must be human readable CSV is good enough.

You can also easily read it in and view it in a human readable format, I’ve never had a problem with that

Re: Why isn’t there a decent file format for tabular data?

#178

Post by pishpash »

hermitcrab wrote: post #57
quesera wrote: post #40

Earlier quoted context omitted.

I think CSV is crappy because commas are so common in real data. For almost all scenarios I've had to work with, I'd have been perfectly happy with TSV where literal Tab was a disallowed character. No escaping histrionics required.

Not being able to include Tabs and Carriage Returns in your data can be a problem though.

Unicode just needs a single special delimiter character that is only used as a delimiter.

Re: Why isn’t there a decent file format for tabular data?

#179

Post by marcosdumay »

gnulinux wrote: post #164
Diggsey wrote: post #106

Earlier quoted context omitted.

CSV has more problems than just quoting: one problem is that excel is so commonly used to open CSV files that various weirdnesses in excel have essentially become part of the CSV format. For example, if a value looks like a formula, then the only way to get excel to treat it as text is to put a single quote in front of it, "'=not a formula". This in turn means that its common for values in a CSV to begin with a singl…

I like the format description, thanks. Sounds like something I might implement some day for fun! The reason I still don't like this is because most of the time I'm sharing csv is to a jira ticket or an email, or to customer and customer insists it needs to be csv. When customer wants csv there is nothing I can do. But in other cases I see this CSV file. So it's visual clarity is important. CSV has all the problems yo…

When the customer wants csv, it's because they will open it on Excel, so anything about standardization or data quality is moot. They will have their data mangled in an application specific format.

You need to export to csv. But that doesn't mean you can't use an actually usable format for anything else.

Re: Why isn’t there a decent file format for tabular data?

#180

Post by zoomablemind »

leeoniya wrote: post #153
briHass wrote: post #150

Earlier quoted context omitted.

You are limited to the basic types (int, floating point, string, and blob), however. I can somewhat get behind the opinionated argument for not needing more specific types like most common language types, but not the lack of a date type.

> but not the lack of a date type. i've also found this to be truly bizarre. even more bizarre than not actually respecting (via coercing or error) to the specified type...why even have types, then?

What's so special about having a named type for datetime? User will still need to call functions to manipulate the dates. If only for the default display and import?
Post reply on HN