
Show HN: Npm module that makes easy to convert JSON to CSV - kauegimenes
https://www.npmjs.com/package/jsonexport
======
billforsternz
Off topic rant I need to get off my chest. Yesterday I troubleshooted a
bizarre problem. New versions of Excel don't necessarily understand the
simplest CSV files. Reason: They forgot that the C in CSV stands for comma,
and instead expect whatever character happens to be set as the 'delimiter'
character in the Windows regional preferences. Here in New Zealand, it seems
that character is semicolon instead of comma. For some reason. I think Excel
was finished circa 2000 and now they focus mainly on making it worse.

~~~
wolco
I'm curious why New Zealand would have a semi-colon as the default. Is there a
historical or cultural reason?

~~~
bitmedley
I checked and New Zealand actually uses a comma as the separator, not a semi-
colon. You can verify this by going to Control Panel -> Region -> Change to:
English (New Zealand) -> Additional settings... -> List separator

A semi-colon is generally used as the default list separator when the
region/locale uses a comma as the decimal separator for numbers. For example
Dutch (Netherlands) uses a comma for the decimal separator (ex. 3,14) whereas
in English (US) we use a decimal point (ex 3.14). If comma were used as the
default list separator in such a region then all floating point numbers would
need to be quoted (ex. "3,14") which would make the size of the CSV file
larger and also make the file less human-readable

~~~
billforsternz
Interesting. Certainly it was only a subset of customers who were complaining,
and in the office it was a subset of Excel installations which could reproduce
the problem. There's no doubt what the problem was and how to fix it. There is
a doubt about exactly which Excel and Windows combinations are problematic,
and how prevalent that combination is amongst New Zealand installations.

------
dc2
For a <5Kb library, I recommend removing the underscore dependency. You should
make this as lightweight as possible if you want a lot of adoption.

~~~
kauegimenes
Thanks for the tip, you are welcome to submit a PR if you want to =)

~~~
d2xdy2
Not OP, but I just submitted a PR removing underscore as a dep. :)

~~~
kauegimenes
Thats awesome! thanks!

PR:
[https://github.com/kauegimenes/jsonexport/pull/27](https://github.com/kauegimenes/jsonexport/pull/27)

~~~
kauegimenes
This is now avaliable in v2.0.7 =)

Dependencies

None

------
TAForObvReasons
The most popular NPM module for JSON to CSV conversion is json2csv:
[https://www.npmjs.com/package/json2csv](https://www.npmjs.com/package/json2csv)
[https://github.com/zemirco/json2csv](https://github.com/zemirco/json2csv)

~~~
cheapsteak
Here's a graphical comparison of downloads of popular json to csv conversion
libraries -

[http://npmcharts.com/compare/json2csv,jsonexport,csv,fast-
cs...](http://npmcharts.com/compare/json2csv,jsonexport,csv,fast-
csv,xlsx,papaparse,json-2-csv)

/shameless plug

------
codezero
Cool, I immediately wondered if you handled a lot of the tricky CSV things
like quotes and internal commas, it looks like it does a good job.

Comparing against Numbers (probably a bad comparison?) I am seeing one
slightly different result:

If I have a field that is "hehe"

It's encoded as ""hehe""

It looks like Numbers adds the enclosing quotes:

"""hehe"""

I only see enclosing quotes if there's a comma:

"hehe,hehe" -> """hehe,hehe"""

Anyhow, I'm not sure what the "correct" thing to do here is, if there is one,
just a heads up!

~~~
chc
CSV is pretty poorly standardized, but if you're going by RFC-4180, Numbers is
definitely right, due to the interaction of two rules:

* double quotes aren't allowed inside a field that isn't double-quoted

* double-quotes that do appear in a field have to be escaped by preceding them with another double quote.

So by RFC-4180, I'm pretty sure ""hehe"" shouldn't be possible, and the way to
represent "hehe" with the quotes is """hehe""".

~~~
codezero
Cool, yep, thanks for pointing out the RFC. I don't envy anyone making CSV
parsers and basically assume they won't work when I need to use them :)

------
danso
In the complex object example, what's the usecase for turning that nested
object into a multi-column CSV? I guess the overarching question is, why turn
a single object at all into a CSV? It's just as machine/human readable as the
JSON version of it, and there's no particular benefit in using either
structure as it's not data meant to be processed in bulk.

The complex array usecase is where an opinionated-type of conversion tool is
particularly needed, but I wonder why it behaves like this:

    
    
       name: 'Robert',
       lastname: 'Miller',
       family: null,
       location: [1231,3214,4214]
    
    

lastname,name,family.type,family.name,nickname,location
Miller,Robert,,,,1231,3214,4214

Why not have `location_1, location_2, location_3`, instead of having a single
location column? The latter implementation makes the data difficult to quickly
use in a program (like a spreadsheet).

~~~
kauegimenes
It's possible to implement the `location_1, location_2, location_3` as a
optional feature.

But i am not sure what name to use for this option.

~~~
traviscj
Maybe some variant on "Extract array elements to separate columns"? or did you
mean what to name the command line option? Maybe a "\--list-treatment="
option, which can be "concat" or "separate"?

------
kmike84
From a Python developer point of view, design is a bit surprising - why isn't
it created as two separate libraries:

1) csv writer (and reader?) which takes care of all csv dialects crazyness; 2)
a library which "flattens" nested objects/arrays?

(1) is not opinionated (besides a few API choices), it just has to be correct;
it doesn't make much sense to re-implement (1) everywhere.

(2) can be more opinionated, it is easy to disagree with design choices, there
is more room for personal preferences.

For example, in Python there is CSV stdlib module, and for (2) there are
libraries like
[https://github.com/scrapinghub/flatson](https://github.com/scrapinghub/flatson).
Why put both to the same library? Is it something ideomatic in node.js world,
with a deeper reason to design libraries this way (e.g. download size), or is
it just an oversight?

~~~
abritinthebay
It could be. The question is: why should it be?

Everything you said makes sense if those are the goals, but if they aren't...
well, why should they be?

The design you suggest would be done by some module authors for sure, but
there's no reason it _needs_ to be that design.

------
BrandiATMuhkuh
Converting JSON to CSV is a usable/good way when analysing NoSQL data. However
I'm trying try find since a while a data analysis/query tool for JSON data
itself. I saw that mixpanel made a query tool/language called JQL (JavaScript
query language) which seems nice. Also RQL (rethink query language) seems nice
too. But I can't​ find a simple tool in which I can import my JSON data and
query it. It should be similar to SQLite-browser. Simply import your CSV/JSON
file, and query it.

~~~
trefn
(I work at Mixpanel)

Honestly you could probably write a quick pipeline to dump your json data into
Mixpanel and then use JQL -- it would be a little hacky but if you have less
than a few million rows it shouldn't be too much work (and would still be free
at that volume).

------
chid
There's a similar command in pandas called json_normalize
([http://pandas.pydata.org/pandas-
docs/stable/generated/pandas...](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.io.json.json_normalize.html))

------
freezer333
Nice! If you want to do something similar with Excel xlsx this works:
[https://www.npmjs.com/package/jsonexcel](https://www.npmjs.com/package/jsonexcel)
Not sure how it handles huge arrays though...

~~~
kauegimenes
Thanks for sharing, you can also use jsonexport with rowDelimiter: '\t' for a
xls compatible output.

------
madamelic
This is a new one. Most people want to go the opposite way.

Either way, neat addition.

~~~
nolok
> Most people want to go the opposite way.

When you want to easily provide something excel/gsuite/... Will take in
without using an heavier excel compatible library, CSV can be quite decent.

------
tambourine_man
Just yesterday I was looking for the other way around, CVS->JSON

~~~
TeMPOraL
\- Wrap the entire file with { and }

\- Wrap each line with [ and ]

\- eval the file

;).

~~~
tambourine_man
It wasn't neatly quoted, which I assume is a must for JSON, but yeah, I ended
up going on a different path.

