6 ms·
Show HN: Npm module that makes easy to convert JSON to CSV
- danso 9y agoIn 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).
- c0nfused 9y agoTo answer your first question: A whole bunch of non progammer types use csv for everything. SAS and SPSS rule everything around them and they do input from csv. Source: I used to make good money doing the turn x to csv for academics all over. As to the last, I got nothing. It's how I would have rolled.
- kauegimenes 9y agoThis only happends if its a simple array, but if you have something like name: 'Robert', lastname: 'Miller', family: null, location: [{city: 'A'}, {city: 'B'}] you would get 2 csv lines one with location.city A and other with B
- robmiller 9y agoHey, why are you picking on me? :)
- kauegimenes 9y agoIt'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 9y agoMaybe 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"?
- codezero 9y agoCool, 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 9y agoCSV 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 9y agoCool, 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 :)
- kauegimenes 9y agoHere is all the logic related to this behavior: https://github.com/kauegimenes/jsonexport/blob/master/lib/core/escape-delimiters.js https://github.com/kauegimenes/jsonexport/blob/master/lib/co...
- dc2 9y agoFor 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 9y agoThanks for the tip, you are welcome to submit a PR if you want to =)
- d2xdy2 9y agoNot OP, but I just submitted a PR removing underscore as a dep. :)
- kauegimenes 9y agoThats awesome! thanks! PR: https://github.com/kauegimenes/jsonexport/pull/27 https://github.com/kauegimenes/jsonexport/pull/27
- kauegimenes 9y agoThis is now avaliable in v2.0.7 =) Dependencies None
- stdbrouw 9y agoConverting to CSV is mainly a server-side problem, not something you'd want to do in the browser, so this doesn't really feel like an issue to me.
- JoshGlazebrook 9y agoYeah this looks like it's only using helper functions like _.isFunction(), _.isNumber(), and _.extend().
- madamelic 9y agoThis is a new one. Most people want to go the opposite way. Either way, neat addition.
- nolok 9y ago> 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.
- TAForObvReasons 9y agoThe 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
- kauegimenes 9y agojson2csv require you to tell every header and don't work well with nested objects and require json2csv-stream for large json files.
- drunkcatsdgaf 9y agoFalse, headers are optional. Nested objects are still a problem though :(
- kauegimenes 9y agoThanks for clarifying
- cheapsteak 9y agoHere's a graphical comparison of downloads of popular json to csv conversion libraries - http://npmcharts.com/compare/json2csv,jsonexport,csv,fast-csv,xlsx,papaparse,json-2-csv http://npmcharts.com/compare/json2csv,jsonexport,csv,fast-cs... /shameless plug
- deleted 9y ago[deleted]
- billforsternz 9y agoOff 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.
- TAForObvReasons 9y agoIf you know the file is going to be used in Excel, you can force a delimiter with the `sep` directive. The first characters of the file should be sep=,<newline>
- sillysaurus3 9y agoBleh. That's so Microsoft. It took roughly ten seconds to find huge problems with this approach: https://stackoverflow.com/questions/20395699/sep-statement-breaks-utf8-bom-in-csv-file-which-is-generated-by-xsl https://stackoverflow.com/questions/20395699/sep-statement-b...
- TAForObvReasons 9y agoCSV is a mess. A while back we switched to exporting XLSX using the xlsx module https://www.npmjs.com/package/xlsx https://www.npmjs.com/package/xlsx
- deleted 9y ago[deleted]
- ng12 9y agoHow can CSV be a mess? The implementation is in the name. Excel is a mess.
- freezer333 9y agoNice! 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 9y agoThanks for sharing, you can also use jsonexport with rowDelimiter: '\t' for a xls compatible output.
- tambourine_man 9y agoJust yesterday I was looking for the other way around, CVS->JSON
- TeMPOraL 9y ago- Wrap the entire file with { and } - Wrap each line with [ and ] - eval the file ;).
- tambourine_man 9y agoIt wasn't neatly quoted, which I assume is a must for JSON, but yeah, I ended up going on a different path.
- BrandiATMuhkuh 9y agoConverting 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.
- kauegimenes 9y agoWhat you think about this syntax https://github.com/mmckegg/json-query https://github.com/mmckegg/json-query?
- trefn 9y ago(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).
- kmike84 9y agoFrom 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 9y agoIt 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.
- chid 9y agoThere's a similar command in pandas called json_normalize (http://pandas.pydata.org/pandas-docs/stable/generated/pandas.io.json.json_normalize.html http://pandas.pydata.org/pandas-docs/stable/generated/pandas...)