21 ms·
Show HN: Transform a CSV into a JSON and vice versa
- brundolf 5y agoSlightly OT: I've realized that CSVs are dramatically more information-dense than the equivalent JSON, and actually make a pretty reasonable API response format if your dataset is large and fits into the tabular shape. They can be a fraction of the size, mainly because keys aren't duplicated for every item.
- sonthonax 5y agoShouldn’t really matter too much if the response is being compressed. If you’re rendering the table in the DOM, the response size is the least of your issues.
- brundolf 5y agoSometimes you fetch a large dataset and only show one page at a time in the DOM, or render it as a line in a chart or something. At a previous workplace we had CSV responses in the hundreds of megabytes.
- cerved 5y agoThat sounds incredible inefficient What was the rational for such enormous single payloads?
- hnlmorg 5y agoWithout knowing more about the application, I'd guess probably caching and/or scaling. If you only need 1 payload then that can be statically generated and cached in your CDN. Which in turn reduces your dependence on the web servers so few nodes are required and/or you can scale your site more easily to demand. Also compute time is more expensive than CDN costs so there might well be some cost savings there too.
- brundolf 5y agoThis was basically it. The dataset was the same across users so caching was simple and efficient, and the front-end had no difficulty handling this much data (and paging client-side was snappier than requesting anew each time)
- nly 5y ago70 GB CSV files aren't uncommon at my work. It's not really a problem since CSV streams well.
- ludocode 5y agoThis is the real answer. All of the other answers are suggesting various changes to the JSON structure to eliminate key repetition, but this is irrelevant under compression. Where it becomes relevant is if each record is stored as a separate document so you can't just compress them all together. Compressing each record separately won't eliminate the duplication, so you're better off with either a columnar format (like a typical database) or a schema-based format (like protobuf.)
- rovr138 5y agoTo parse it, you need to check the keys. If there can’t be other keys, you can just use an array which is stable on JSON and you can save on keys. So you just have an array of arrays. Or even a huge array and every X elements, it’s a new record. If each one has 2 keys, [ { key1: ‘a’, key2: ‘b’ }, { key1: ‘a’, key2: ‘b’ } ] Can become, [ [ ‘a’, ‘b’ ], [ ‘a’, ‘b’ ] ] Or just every 2 will be a new record, [ ‘a’, ‘b’, ‘a’, ‘b’ ]
- ludocode 5y agoBut why? Why save on keys when compression will nearly eliminate them for you?
- rovr138 5y agoCompression mainly helps with transmission. Trying to point out that the original structure allows for more flexibility. If you only cared about space, this compresses better anyway and uncompressed, it still occupies less space.
- OJFord 5y agoYeah, lists of objects are pretty crap, because they're almost always homogeneous but unenforcédly so; it's not just a size issue but a parsing (or not - usage) issue too. You could approximate CSV in a JSON response like: { "columns": ["a", ..., "z"], "rows": [[1, ..., 26], ..., [11, 2266]] } Or: { "a": [1, ..., 11], ... "z": [26, ..., 2266] } which I've never seen, but would save space, and sort of enforced in the sense that if you trust your serialiser for it as much as you trust an equivalent CSV serialiser, it's fine. (But the same argument could be made for more usual JSON object lists. Only arguable difference is that there's more of an assertion to the client that they should be expected to be homogeneous.)
- cowsandmilk 5y agoI’ll note, your second item here is a structure of arrays, which is often higher performance in practice when you are only interested in certain portions of the data. For this reason, your second structure is how I serialize code I’m interacting with in C.
- dmw_ng 5y agoHigher performance, much smaller compressed and uncompressed, accepted by a wide range of tools, e.g. Pandas, and often more convenient to parse from statically typed languages.
- mananaysiempre 5y ago... Also known as a very simple case of a “column-oriented database”, of which are several at various scales from Metakit[1] to Clickhouse[2]. It’s a neat way to have columns which are sparsely populated, required to accommodate large blobs, numerous but usually not accessed all at once, or frequently added and deleted. Nothing’s perfect, of course: you can’t stream records in such a format, so no convenient Unix-style tooling. [1]: http://www.equi4.com/metakit.html http://www.equi4.com/metakit.html [2]: https://yandex.com/dev/clickhouse/ https://yandex.com/dev/clickhouse/
- 1vuio0pswjnm7 5y agohttps://shakti.com https://shakti.com
- killingtime74 5y agoIt’s because it’s self describing right? If you look at protobuf, thrift, avro those are even denser
- anonytrary 5y agoIt would be much nicer for the consumer to just de-dupe the keys in your json than to serve an annoying format like CSV. Your JSON could basically be a matrix with a header row, there's nothing forcing you to duplicate keys. { header: [...columnNames], rows: [...values2DArray]}
- earthboundkid 5y agoMake rows 1 dimensional. You don’t need the second dimension, it’s implied by header length. Once you do this, the JSON gzips down to about the same size as CSV, according to the last time I tested this IIRC.
- dragonwriter 5y agoHeck, you could do a single 1-D list (no object), and just give the header count as the first element, which would be even more compact.
- earthboundkid 5y agoSmart idea.
- anonytrary 5y agoI edited-in the "2DArray" because I thought it was confusing... But you're right, just calculate offsets. The dominating term is still quadratic, and the term you mentioned is linear. It could be worth it for a scaled org like Google! I wonder which parses faster. I guess CSV does but then the consuming code would still have to parse the strings into JS primitives...
- earthboundkid 5y agoI haven't tested this, but my guess is that because the browser built in JSON.parse will be faster than whatever CSV parser you can write in JS just because it's precompiled to native code. Then the question becomes how long does it take to do the unpacking loop, but it should be pretty quick. I'd love it if someone did a benchmark though.
- quantumofalpha 5y agoIf you'd use a column-oriented format like {"col1":["a","b","c",...],"col2":[1,2,3...],...}, it's about the same density, no?
- rahimnathwani 5y agoThis is the default format used by pandas.DataFrame.to_dict() I usually need a less dense version, e.g. to send to a jinja2 template, so mostly use to_dict(orient='index').
- jpitz 5y agoHell is other people's CSVs.
- brianzelip 5y agoFYI, the view on small devices is pretty bad - the demo json output is almost unreadable without an awkward pinch + scroll. Compare this view to the same content on the Readme via GitHub.
- okumurahata 5y agoFixed.
- tyingq 5y agoWouldn't this need to allow upload/download of CSV to really meet the spirit of the title? Or maybe replace the references of CSV with "HTML Table"?
- okumurahata 5y agoYes, you are right. I added a button to allow CSV upload instead of only add data by copying/pasting.
- th0ma5 5y agoCSV is more of a rumor than a standard, plus JSON can have a tree structure. It is a fun idea to think about and may be useful in some narrow cases, but will fail in almost all but those most trivial of structures.
- amyjess 5y ago> CSV is more of a rumor than a standard This reminds me of something my boss at a previous job would say: "I am morally opposed to CSV." Why? Because we worked at an NLP company, where we would frequently have tabular data featuring commas, which means if we used CSV we'd have a lot of overhead involving quoting all our CSV data. Instead my boss preferred TSV (T = tab) as our preferred tabular data format, which was much simpler for us to parse since we didn't really deal with any fields that had \t in them.
- earthboundkid 5y agoLol, so instead of having an actually working solution (escaping), you had a still broken solution that just didn’t blow up as often so you could ignore it until it caused a crash.
- th0ma5 5y agoEscaping breaks often as well. The general problem is that the data is inline with the format. Parquet files or something that has clear demarcation between data and file format are more ideal but probably nothing is perfect or future proof. Or accepting of past mistakes either.
- quickthrower2 5y agoYou can write a perfectly isomorphic escaping printer/parser
- th0ma5 5y agoObligatory https://xkcd.com/927/ https://xkcd.com/927/ but also all systems would have to be proven correct or else it wouldn't work. "Forgiving" parsers are the norm and you can't rely on what they do deterministically.
- lettergram 5y agoCan this handle uploading csvs?
- okumurahata 5y agoFeature added. You could only add CSV data by copying/pasting on the table, but now you can upload a CSV file as well. https://github.com/erikmartinjordan/jsonmatic/blob/525b7fbc9d10b2756ada7f5b72efbc025a839ed0/src/UploadCSV.js https://github.com/erikmartinjordan/jsonmatic/blob/525b7fbc9...
- af3d 5y agoLooks a bit like adware IMO. The library appears to be drenched in analytics. Dependencies include: https://www.npmjs.com/package/web-vitals/v/0.1.0 https://www.npmjs.com/package/web-vitals/v/0.1.0 https://www.npmjs.com/package/@fingerprintjs/fingerprintjs https://www.npmjs.com/package/@fingerprintjs/fingerprintjs Harvesting user's data, most likely...
- MonaroVXR 5y agoHow did you figure this out?
- lcabral 5y agoThe page has a link at the bottom to the GitHub project where you check the dependencies...
- true_religion 5y agoYes but it’s not a library. It’s an entire website. It even uses Firebase.
- ianschmitz 5y agoThere's nothing wrong with web-vitals...
- hughcrt 5y agoThere's nothing wrong with web-vitals, and it's included in create-react-app, which the author used.
- true_religion 5y agoI agree, though for the sake of argument Facebooks tolerance for tracking and fingerprinting far exceeds anyone else’s on the internet so their stamp of approval for web vitals is meaningless.
- 5y ago
- earthboundkid 5y agoI wrote my own converter a few years ago, then ended up needing it again last week. It’s one of those things you don’t always need but it’s handy to have when you do. https://github.com/baltimore-sun-data/csv2json https://github.com/baltimore-sun-data/csv2json
- jabo 5y agoI recently heard about a tool called Miller that helps convert between JSON and CSV among other formats: https://github.com/johnkerl/miller https://github.com/johnkerl/miller mlr --c2j cat documents.csv > documents.jsonl Converts a CSV file to a JSONL file
- stevage 5y agoHow do you actually load CSVs into it?
- okumurahata 5y agoFeature added (see comment below).
- somishere 5y agoBuilt something similar on codepen quite a few years ago. Not sure where I came up with the format, seems a bit wild looking at the v. nice dot notation used here, but possibly more useful/efficient for variable data models, also takes into account data types: https://codepen.io/theprojectsomething/pen/OwppWW https://codepen.io/theprojectsomething/pen/OwppWW Note: click the Toggle Info to read the "spec" (groan) :)
- gspr 5y agoI'm sorry, but why is this a website?
- AnthonBerg 5y agoSo that we may have this discussion and get out of this strange rut that is the care and feeding of idempotent little formats. And to educate! To show each other. To make knowledge discoverable. That’s the reason websites like this are honestly a very good thing.
- okumurahata 5y agoThanks, AnthonBerg.
- AnthonBerg 5y agoKudos for doing the work! :bow: I see an appropriate beauty in your username representing an information propagation model – of modern waves in modern habitats. I do hope that my comment about a “rut” and “little formats” doesn’t disparage the work. I try to speak for enlightenment but sometimes I fall into lamenting the darkness.
- me_bx 5y agoWhy not? Some users don't like to install too many desktop applications and rather use simple web apps...
- luming 5y agoYou should use outline instead of border in your cell css.
- codetrotter 5y agoWhy? I read https://css-tricks.com/almanac/properties/o/outline/ https://css-tricks.com/almanac/properties/o/outline/ and it says > The outline property in CSS draws a line around the outside of an element. It’s similar to border except that: > 1. It always goes around all the sides, you can’t specify particular sides > 2. It’s not a part of the box model, so it won’t affect the position of the element or adjacent elements (nice for debugging!) > […] > It is often used for accessibility reasons, to emphasize a link when tabbed to without affecting positioning and in a different way than hover. I guess this is why you said outline should be used instead in this case.
- luming 5y agoAnd ::focus-within pseudo-class.
- 867-5309 5y agothis can turn an HTML table into JSON with the option to download the JSON, or it can turn JSON into an HTML table with no option to download a CSV -- where does CSV come into this? also, clearly javascript is a bit too ambitious for the job when e.g. PHP could provide the intended functionality with two lines of code: foreach($arrays as $values){echo implode(',', $values) . "\n";} echo json_encode($arrays); also, CSV is more for storing rigidly-structured uniform columns and rows, whereas JSON is more for storing loosely-structured varying objects, otherwise you're redeclaring column headings in every array, which wouldn't make much difference for gzipped transport but still wasteful and verbose nonetheless. column headings are usually the first line of a CSV
- laumars 5y agoIf you’re using JSON for tables then you’re much better off using jsonlines. It’s got a properly defined specification (unlike the wishy washy spec of CSV which every maintainer seems to implement differently) so you’re less likely to garble your data while still having all the benefits that CSVs do. Plus a lot of JSON marshaller will natively support jsonlines despite not advertising that functionality. Personally I’d recommend jsonlines over regular CSVs these days but I’ve had so many issues with CSV parsers being incompatible over the years that I’d welcome anything which offers stricter formatting rules. I’d definitely recommend you check it out. https://jsonlines.org https://jsonlines.org
- osullip 5y agoI run a software company and we have a challenge when it comes to these types of conversion tools. If there is any data that is a) not publicly accessible or b) contains personal information, I cannot authorise the use of a web based third party tool. There is just too much risk that some bad actor uses this as a method to soak up data. I would love to verify /validate that all of the processing is local and have some way to certify if this hasn't changed.
- jarofgreen 5y agoAt work we work an a Python Library to do this, and much more: PyPi: https://pypi.org/project/flattentool/ https://pypi.org/project/flattentool/ Source: https://github.com/OpenDataServices/flatten-tool https://github.com/OpenDataServices/flatten-tool Docs: https://flatten-tool.readthedocs.io/en/latest/ https://flatten-tool.readthedocs.io/en/latest/ It converts JSON to CSV and vice versa but also Spreadsheet files, XML ... It has recently had some work to make it memory efficient for large files. Work, BTW, is an Open Data Workers Co-op working on data and standards. We use this tool a lot directly, but also as a library in other tools. https://dataquality.threesixtygiving.org/ https://dataquality.threesixtygiving.org/ for instance - this is a website that checks data against the 360 Giving Data Standard [ https://www.threesixtygiving.org/ https://www.threesixtygiving.org/ ].
- contravariant 5y agoWhat are the advantages of this tool in comparison with e.g. pandas' json_normalize?
- jarofgreen 5y agoFlatten-tool has more options and functions than just that one pandas functions (But I haven't done a full comparison to all Pandas functions. I wasn't around when the tool was started so I can't say what analysis was done at the time.) For instance I note with interest their examples on nested data and arrays. We have various different ways you can work with arrays, so you can design user-friendly spreadsheets as you want and still get JSON of the right structure out: https://flatten-tool.readthedocs.io/en/latest/examples/#one-to-many-relationships-json-arrays https://flatten-tool.readthedocs.io/en/latest/examples/#one-... (Letting people work on data in user-friendly spreadsheets and converting it to JSON when they are done is one of the big use cases we have)
- nly 5y agojq's stream and fromstream functions can be used to flatten and unflatten JSON. I use it all the time at work for POs who want to see data in Excel https://jqplay.org/s/ub-WvXCcPn https://jqplay.org/s/ub-WvXCcPn ... from there it's just a row->column rotation to CSV.
- code-faster 5y agoI have a couple of open source CLI tools to do this: - https://github.com/tyleradams/json-toolkit/blob/master/csv-to-json https://github.com/tyleradams/json-toolkit/blob/master/csv-t... - https://github.com/tyleradams/json-toolkit/blob/master/json-to-csv https://github.com/tyleradams/json-toolkit/blob/master/json-...
- hmsimha 5y agoIt would make it much easier for users to visually parse the JSON section if you added `font-family: monospace` to the textarea element
- okumurahata 5y agoDone.
- darrenf 5y ago`jq` can transform CSV to JSON and vice-versa, especially for simple/naive data where simply splitting on `,` is good enough - and where you aren't too bothered by types (e.g. if you don't mind numbers ending up as strings). First attempt is to simply read each line in as raw and split on `,` - sort of does the job of, but it isn't the array of arrays that you might expect: $ echo -e "foo,bar,quux\n1,2,3\n4,5,6\n7,8,9" > foo.csv $ jq -cR 'split(",")' foo.csv ["foo","bar","quux"] ["1","2","3"] ["4","5","6"] ["7","8","9"] Pipe that back to `jq` in slurp mode, though: $ jq -R 'split(",")' foo.csv | jq -cs [["foo","bar","quux"],["1","2","3"],["4","5","6"],["7","8","9"]] And if you prefer objects, this output can be combined with the csv2json recipe from the jq cookbook[0], without requiring `any-json` or any other external tool: $ jq -cR 'split(",")' foo.csv | jq -csf csv2json.jq [{"foo":1,"bar":2,"quux":3}, {"foo":4,"bar":5,"quux":6}, {"foo":7,"bar":8,"quux":9}] Note that this recipe also keeps numbers as numbers! In the reverse direction there's a builtin `@csv` format string. This can be use with the second example above to say "turn each array into a CSV row" like so: $ jq -R 'split(",")' foo.csv | jq -sr '.[]|@csv' "foo","bar","quux" "1","2","3" "4","5","6" "7","8","9" And to turn the fuller structure from the third example back into CSV, you can pick out the fields, albeit this one is less friendly with quotes and doesn't spit out a header (probably doable by calling `keys` on `.[0]` only...): $ jq -cR 'split(",")' foo.csv | jq -csf csv2json.jq | \ > jq -r '.[]|[.foo,.bar,.quux]|@csv' 1,2,3 4,5,6 7,8,9 I don't consider myself much of a jq power user, but I am a huge admirer of its capabilities. [0] https://github.com/stedolan/jq/wiki/Cookbook#convert-a-csv-file-with-headers-to-json https://github.com/stedolan/jq/wiki/Cookbook#convert-a-csv-f...
- 0x008 5y agoThis is the kind of comment I came here for.
- me_bx 5y agoNice, I like the look of the editable table. Shameless plug: a similar solution, working all client side, not imposing to use a key as first column, and with options regarding CSV format. https://mango-is.com/tools/csv-to-json/ https://mango-is.com/tools/csv-to-json/
- oweiler 5y agoWhy doesn't the library transform the csv into a an array of json objects?
- aae42 5y agoi was wondering if there would be an option for this in the UI somewhere, but it doesn't look like there is i wonder if it requires a "key" column to serve as the dictionary key EDIT: i kind of wouldn't mind a HN discussion on which is better... ruby is my go-to scripting language, so i find this structure very natural, i've found when trying to do the same things with Go, i much prefer things to be structured more like an array of objects would be interesting to hear the merits of both
- robbiejs 5y agoIf anyone is looking at a free tool to quickly edit CSV data in an Excel-like editor, see https://editcsvonline.com https://editcsvonline.com
- sireat 5y agoCSV<->JSON is fundamentally an unsolvable problem because of mismatch in data hierarchies among them. Plus you have the type looseness for both and lack of standards for CSV. A trivial 2-D case is handled well by Python library such as Pandas. Here OP could be an alternative. When I say trivial I mean flat 2 dimensional data, such as you would get from Mockaroo or similar source. However in real life - data is messy. As you get into 3,4 and deeper hierarchies on JSON you can't really translate that into nice flat 2d CSV. Then you have missing keys, mixed up types and you end up rolling you own hand written converters.
- AbhyudayaSharma 5y agoYou can do this in Powershell cat file.csv | ConvertFrom-Csv | ConvertTo-Json
- ddgflorida 5y agoShameless plug - I wrote convertcsv.com and it supports about everything you can think of as far as format conversions. JSON, XML, YAML, JSON Lines, Fixed Width, ...