Hacker News Reader

Comment by ex3ndr | Hacker News Reader

Any alternatives to CSVs? In our startup we are using of JSON object on each line but may be there is a more established format for doing this?

View on HN

It really depends on the purpose. For scientific computing, for instance, CSV is used as a "standard" for quick tables of numbers. Sufficiently "normal" files can be read by almost all scientific tools (speaking of libraries such as numpy, systems such as Mathematica, Maple, or ecosystems such as R, basically even C++ and Fortran allow this task to be implemented in a couple of lines).

However, also in this context CSV has major drawbacks, for instance not defining column headers, comment lines, number formatting. A proper drop-in replacement in my subject is HDF5 (https://www.hdfgroup.org/) which is mostly used for being a binary representation of n-dimensional tables with couplings to major programming systems.

However, I never heard of HDF5 outside of science. Therfore I make this example here.

I usually reach for SQLite if CSV is creaking under the strain and it's a part of the pipe I control on both ends. I've heard good things about HDF5 for big flat tables, but only when I was in grad school. I haven't tried it.

If they're going to put it in Excel, just use their formats[1], [2].

[1]: https://news.ycombinator.com/item?id=15476951

[2]: https://www.google.com/search?q=simple+xlsx+writer

If it's something you're streaming, using a binary messaging format is always faster/better. I tend to use Kx's format since a lot of my messages go from to their systems, but Erlang's binary term format is good too -- even if you're not using Erlang.

XML, JSON, any specified format is a better machine-to-machine format than CSV.

I've seen the JSON-object-per-newline pattern a few times, but why not use a JSON array then? Then the whole file remains valid JSON.

This is just easier to implement line-by-line processing than hacking into JSON parsers for this.

In my experience the reason for a JSON object per line is because a tool can then split the entire file by newlines and have a list of objects to start parsing/processing in parallel, which avoids having to parse the entire file up front to get usable data, and lets the tool start processing things while some data is still being parsed.

My favorite is pipe-delimited, though that would depend on your data of course. Pipes are never used with the data I deal with, and would be a "typo" if they were. I do check for them on import and export, replacing them with %pipe; on any free-form text fields.

My data sets aren't that huge, but sometimes do get up to mid 10Ms though frequently is only in the 100Ks.

I know it's not a perfect solution but may work for you too.

> Any alternatives to CSVs?

Depends on the context. If it's data-exchange and the data is going to be edited by humans, just take excel files directly as input. That'll save you tons of trouble.

Note that this only works if you're working with small amounts of data. CSV with 5 million lines are no issue at all, just don't try this in Excel.

5 million lines CSV hand-edited by users? I can only see everything going wrong with it.

Difficult to answer this question when we don't know your use case.

Reply on news.ycombinator.com