9 ms·
Consider Using CSV
- pkstn 4y agoUse gzip for compressing. If you want to stream, use following syntax: [\n { ... },\n { ... },\n { ... },\n ...\n ]\n With this simple trick you can stream easily..
- brundolf 4y agoI worked at a company where we did this for some endpoints and it worked great. Our client app had to request enormous time-series datasets and using CSV cut a significant percentage off of the payload size. I recommend it if you have similar constraints
- revskill 4y agoSure! For example, for batch processing, CSV is always the default for me and the teams.
- ndsipa_pomu 4y agoAs much as I like and use CSV for database work, it has a problem with being poorly specified. The most common problems are when processing CSVs produced elsewhere which might not enclose text fields with quotes and thus have issues with data that includes commas and multi-line data.
- CharlesW 4y agoYes, I feel like this would've been more helpful generalized as "Consider DSV" (delimiter-separated values) than CSV specifically, because of the interop issues that often come up. I'd have also mentioned using Parquet.
- fatneckbeardz 4y agobut which delimiter. if you choose pipe ok, now you have to make sure nobody typed a pipe into the input field or spreadsheet, and you cannot store unix commands if you choose tab, ok, now people will get confused when they try to edit the text file to replace tabs with spaces, and now you have trouble putting code snippets into data fields because they have tabs. this is the problem and it's why xml/json exist. in my particular domain, tab separated works pretty well but in a general context of the world at large, i feel like JSON has reasons it exists.
- toast0 4y ago> but which delimiter Control characters. Like ctrl-A and stuff. Almost nobody has them in their data.
- elcritch 4y agoWell the obvious solution would be ASCII 0x1D (Group Separator)! Accept, no one actually uses those ASCII characters. Kind of bums me out that UNIX basically skipped out on them.
- kevinmgranger 4y agoIt's not a separator character, but at least vim and emacs acknowledge the page feed character. A pittance, I suppose.
- lelanthran 4y ago> It's not a separator character, Isn't it? I thought all the separator characters (0x1e, 0x1f, 0x1c) were specifically for delimiting records, fields and units. What are they for?
- majkinetor 4y agoWith gzip on web server the difference is not important at all. CSV in general is problematic as there is no standard (RFC 4180 is not). In certain contexts this surely can be good solution but definitelly not good in general scenario.
- CharlesW 4y agoAs Wikipedia puts it, "CSV is widely used to refer to a large family of formats that differ in many ways". If there's a canonical standard, it appears to be RFC4180: https://www.rfc-editor.org/rfc/rfc4180 https://www.rfc-editor.org/rfc/rfc4180
- majkinetor 4y agoIt appears, but its not. I have not found single program so far that conforms only to this RFC and nothing else. From the RFC itself: Status of This Memo This memo provides information for the Internet community. It does not specify an Internet standard of any kind. Distribution of this memo is unlimited.
- CharlesW 4y ago> I have not found single program so far that conforms only to this RFC and nothing else. Wouldn't that be impossible, given that parsers have to accept all kind of bizarro CSV flavors? Maybe more importantly, do you know of a single program or single CSV library that doesn't support reading or writing CSV as defined by the RFC?
- majkinetor 4y agoYeah, any of them. Just add new line in the "cell" and then go jump from the bridge.
- Karellen 4y agoAn "Internet Standard" is just a designation that has been given to an RFC that has been blessed in a certain way. See https://www.rfc-editor.org/ https://www.rfc-editor.org/ for more details, but the set of designations is: * Uncategorised * Historic * Experimental * Informational * Best Current Practice * Proposed Standard * Draft Standard * Internet Standard Once an RFC reaches "Internet Standard" it is given a special designation, e.g. STD-63 is the standards designation for RFC-3629: UTF-8 < https://www.rfc-editor.org/info/std63 https://www.rfc-editor.org/info/std63 >. See https://www.rfc-editor.org/standards https://www.rfc-editor.org/standards Being an "Internet Standard" is kinda special, but not especially so. For example, IMAP4, originally specified in RFC-3501 in March 2003, updated many times since, and revised in RFC-9051 in August 2021, is still a "Proposed Standard" without an STD designation, nearly 20 years and dozens of interoperable implementations later. "Rough consensus and running code" is how things get done. RFC-4180 is plenty good enough a "standard" for people to decide to interoperate over. They just have to decide to do so. (Note also that HTML5 is not an "Internet Standard" according to the IETF et al. The last version to get an RFC was HTML 2 in RFC-1866, designated "Historic". And interoperability was an issue for a while with later versions of HTML during the "Best viewed in Internet Explorer/Netscape Navigator" wars. To get interoperability like we eventually did, you don't need an "Internet Standard"; you just need implementers who want to interoperate, and are willing to favour it over lock-in, and even over strict backwards-compatibility.) (Also, the "and nothing else" clause in your comment confuses me. Why not support other formats/variants also? "Be liberal in what you accept" is certainly something that you probably want to avoid if you're designing a new format/protocol that no-one else is using yet, but if you're working with a decades-old format that was traditionally poorly-specified, with millions of documents out in the wild, it's probably the best way to allow existing users to move forward.)
- sheeeep86 4y agoYou could have the advantages of both worlds by having one json object per line. You could stream process, and you could structure more complex objects and have consistent escaping.
- ledauphin 4y agoand the "redundant" headers will get compressed away to nearly nothing over most transports.
- gugagore 4y agohttps://jsonlines.org/ https://jsonlines.org/
- nmz 4y agoYou could do that, you could also have a json that is not streamable. You can't guarantee how large a json object will be but you can guess that the csv will probable be.
- cpeterso 4y agoAnother alternative is a streaming JSON format like JSONL (newline-delimited JSON). You can parse one record/line at a time, but still have the structure and named fields of JSON. https://en.m.wikipedia.org/wiki/JSON_streaming https://en.m.wikipedia.org/wiki/JSON_streaming
- account-5 4y agoI think one of the issues it data types. JSON has them CSV doesn't, so this means your program needs to be aware of which columns are which data type and do the conversion where needed. It's similar to JSON Vs INI files for config files. On a different note I wouldn't nest JSON in a CSV column. I'd delimit with a pipe or something the split string on that. Much simpler if you're in control of the data.
- majkinetor 4y agoJSON also has schema that can be used to verify it.
- ajanuary 4y agoHow often is this a concern in practice? It’s a question I’ve been thinking about a bunch and the answer I keep coming back to is that most of the time, encoding the type in the data exchange format isn’t actually very useful. So I would be interested in use cases where it is. Are you just trusting that the types coming in are going to be the correct ones? What happens if someone sends you `{“foo”: “10”}` instead of `{“foo”: 10}`? Do you validate with a schema up front? In which case your code already needs to know what types it expects and can convert them. Or are you letter the incorrect types run through your system until it hits a type error at runtime somewhere?
- account-5 4y ago> How often is this a concern in practice? No idea really, but if you're using a JSON parsing library then that is going to automatically convert for the data types. Which, provided you trust the data, saves you a job.
- gugagore 4y agoThe only reason, in my eyes, to use CSV is to have easy interoperability with spreadsheet software. If you want streaming: https://jsonlines.org/ https://jsonlines.org/
- majkinetor 4y agoMeh. Excel compatibility really sux. And Excel is most used one by large. You really can't double click it effectivelly, as everything will be shown as generic type, so you have to mess up with wizard which is also half baked. I have to create tutorials for that for each service using it.
- Karellen 4y agoJSON lines looks kinda interesting, but the newline-delimited thing seems weird. It seems to me that you could write a JSON streaming parser that, if the outer element is an Array, reads and outputs/processes one Array element at a time as its JSON value. Yeah, you can't get the array length, and if there's a parse error somewhere down the line then you have to figure out how to deal with that (a non-streaming parser would have rejected the whole input), but that's kind of inherent in using a streaming parser. The upside is that you can work with any valid JSON. Sure, if you're interoperating with shell tools, and don't have `jq` available, newline-delimited JSON might be helpful. But on the other hand, just install `jq`, dummy!
- xwowsersx 4y agoI mean point well taken, but, as they acknowledged in the post themselves, CSV isn't suitable when you have a nested structure. And you almost always have/need a nested structure, no?
- akhmatova 4y agoCSV isn't suitable when you have a nested structure. As the post acknowledges right about where you stopped skimming. And you almost always have/need a nested structure, no? No.
- tremon 4y agoRelational databases have worked fine for decades without nested structures. The simple trick is to take the nested structure out of the entity and into its own table.
- sitkack 4y agoDid they? And all the databases I use regularly support nested structures, they are extremely expressive.
- 4y ago
- albertopv 4y agoWhat else do you use if you have to import millions of rows from a client or supplier without direct integration but sftp?
- CharlesW 4y agoOften, Parquet. https://parquet.apache.org/ https://parquet.apache.org/
- wenc 4y agoIt often surprises me that Parquet is not widely known outside of data engineering circles. Most software developers are still mucking around with CSV for large tabular data, which is absolutely the wrong format. Better developers use sqlite, which is less wrong but still wrong. Postgres is closer to the right answer, but for very large, typed tabular data, Parquet is the way to go. Parquet is a columnar format that is compressed, typed, efficient for columnar queries, append-friendly (though not rewritable), and is a natively supported format for Apache Spark. Parquet libraries are now widely available for most languages (didn't used to be the case, but now they are). I query Parquet files with DuckDB in Python and it blazes through GBs of data in seconds. At work, the canonical format is TSV (tab separated values) which despite being human-readable, is huge, inefficient to query and does not support data types. When I have to work with large TSV files (10GB or larger), I first convert them into 500MB Parquet files. The latter are faster, smaller and less prone to type errors. Because columnar formats like Parquets are indexed, I can do complex operations like joins, window functions, aggregations on them in a performant way, while any similar operation on TSV files will trigger a table scan each time. I recently ran a Spark job on a very large TSV file which took over 8 hours and timed out. A Spark job on the same data represented in Parquet completed in 5 minutes.
- bufferoverflow 4y agoThe author didn't compare gzipped/brottlied sizes. The author didn't think of any examples with even a bit more complexity. If you have 2-level object nesting, now what?
- dsmmcken 4y agoYou could also consider Kafka for streaming, and Parquet for batch.
- fellowniusmonk 4y agoDelimited formats performance can be exceptional, they can also be phenomenally terse and avoid the string tarpits of CSV and TSV if you just use these unicode characters. U+241D, U+241E, U+241F
- tremon 4y agoOr these characters, from the ASCII era: SOH (U+01), US (U+1F), RS (U+1E), GS (U+1D), FS (U+1C)
- teddyh 4y agoThose are not the unit/record/group separator characters! Those are the graphical symbols for the unit/record/group separator codes. The actual unit/record/group separator codes are in ASCII, as 'tremon' writes in a sibling comment.
- fellowniusmonk 4y agoI guess I shouldn't post comments when I'm terribly hungover. The fact remains, use those seperators, most developers don't even seem aware of them to any degree.
- majkinetor 4y agoSince this is about CSV, this is obligatory tool for larger ones: * https://github.com/antonycourtney/tad https://github.com/antonycourtney/tad
- sitkack 4y agoFor manipulating CSV from the terminal, check out https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
- speq 4y agoThere's a fork with new features: https://github.com/jqnatividad/qsv https://github.com/jqnatividad/qsv
- majkinetor 4y agoUnless you really need ultra performance, PowerShell is certainly much better option.
- sitkack 4y agoThis is amazing work. Thanks for bringing it to my attention. Hopefully it and xsv can be merged at some point in the future.
- elcritch 4y agoSometimes CSV is nicer. Still you can cut down on your JSON by formatting it as a similar header style: [ ["productId", "quantity", "customerId"], ["5710031efdfe", 1, "8fe96b88"], ["479cd9744e5c", 2, "526ba6f5"] ] This style also works well with jsonlines a sibling comment mentioned. Of course my favorite is MessagePack (or CBOR) using similar styles. MsgPack can be as small as gzipped JSON. :)
- thangalin 4y agoCSV is also great for importing external data into documents. My text editor, KeenWrite[0], includes an R engine and a CSV-to-Markdown function[1]. This means you can write the following in a plain text R Markdown document: `r#csv2md('filanme.csv')` The editor will convert Markdown to XHTML in the preview panel (in real time), then ConTeXt can typeset the XHTML into a PDF file in various styles.[2][3] This avoids spending time fighting with table formatting/consistency in certain word processors while storing the data in a machine-friendly format. (Thereby upholding the DRY principle because the data can have a single source of truth, as opposed to copying data into documents, which could go stale/diverge.) Using JSON would be possible, but it's not as easy to convert into a Markdown table. [0]: https://github.com/DaveJarvis/keenwrite https://github.com/DaveJarvis/keenwrite [1]: https://github.com/DaveJarvis/keenwrite/blob/main/R/csv.R#L35 https://github.com/DaveJarvis/keenwrite/blob/main/R/csv.R#L3... [2]: https://i.ibb.co/6FLXKsD/keenwrite-csv.png https://i.ibb.co/6FLXKsD/keenwrite-csv.png [3]: https://i.ibb.co/47h6zNx/keenwrite-table.png https://i.ibb.co/47h6zNx/keenwrite-table.png
- margarina72 4y agoyou may also simply add a format specification and return either csv or json depending on the need or the context. Most language would have what it needs to return either without much trouble.
- Pinus 4y agoCSV looks deceptively simple. It is far too easy to just write(','.join(whatever)), which sort of works, until it doesn’t, and then someone, sometimes I, has to sort out the resulting mess. PLEASE use a proper CSV library (Python comes with a CSV module in the standard library), or at least implement the entire format according to the RFC from the outset, even if you think you won’t need it!
- chaps 4y agoOh yes. CSVs are deceptively challenging especially if your use-case is from excel files to csv. Excel will happily convert a worksheet to csv, but it's a naive conversation. Headers that start on line 3, multi-line headers, inconsistent column counts, etc. It adds up really quickly!
- zem 4y agoI've also run into issues where I wrote some code that worked with csv input, and told users they could just export their data from excel. turns out excel doesn't export in utf-8 by default, we had some weird issues until we figured that out.
- MrJohz 4y agoAnother issue is passing those CSV files across international borders - a CSV file that works in the UK (commas as separators and a decimal point) may not be readable in Germany (semicolons as separators and decimal commas) without some configuration.
- kasajian 4y agoThis is a matter of developer education. The correct way to create and parse CSV files is to use a third-party library. They can get complicated. A field in a CSV can contain commas and quotes. In some cases, a single field can contain a line-feed, and you'll need to ensure the parser you use supports that. This would allow an entire CSV file to be embedded inside the field of a CSV field. At a minimum, a parser must support Excel's default parser logic. But, if you pick the right parser and generator, then you're ok with using it.
- panzerboiler 4y agoI usually prefer a binary encoding. More efficient on the wire, easier to parse and generate, and with no ambiguity. We have 2 control codes given to us by the teletype era that have the perfect meaning for this kind of data: 0x1E Record Separator 0x1F Unit Separator
- nmz 4y agoand because its a single byte, its fast, no need to tokenize. You also have 2 more the group separator and the file separator. so you could represent a tree with it.
- ARandomerDude 4y ago> It's only 77 bytes, with 29 for the header and 24 for each line. At 100,000 entries, this list would be 2.4 MB (that's ~63% less than the JSON). If size is really the issue but you still want schema enforcement protobuf is the way to go.
- saulpw 4y agoprotobuf is terrible! Now you have to rely on Google-scale tools to generate code for whatever language(s) you want to read or write the data in, and this becomes quite the encumbrance.
- out_of_protocol 4y agoI'd go with sqlite instead. Also, there are specialized formats like Parquet
- beached_whale 4y agoA constrained format based on JSONL with each record being a tuple of number/string/bool/null could better defined than CSV and looks almost like it. The benefit being, almost any json library could work with it, or could be made to one line at a time and it can be parallelized as newlines only exist as the delimiter. ["hello",5,false,1,2,2.334,null] ["world",12,true,1,2,2.334,null]
- sitkack 4y agoNo one uses that format for streamed json, see ndson and jsonl http://ndjson.org/ http://ndjson.org/ The size complaint is overblown, as repeated fields are compressed away. As other folks rightfully commented, csv is a mine field. One should assume every CSV file is broken in some way. They also don't enumerate any of the downsides of CSV. What people should consider is using formats like Avro or Parquet that carry their schema with them so the data can be loaded and analyzed without have to manually deal with column meaning.
- whateveracct 4y agoI quite like CSVs. I've used them to great effect at maybe every job I've ever had. xsv, sqlite, and Excel/LibreOffice provide useful tooling on top of them. I see a lot of complaining about "no standard" in this thread, but the way I've used them, it's been fine. I just use Haskell's cassava. If human produce them with Excel/LibreOffice, I never have issues on the ingestion end.
- spentu 4y agoI cannot count how many times CSV "format" has caused problems for me.. In my country the decimal separator is comma, instead of punctuation. This causes problems when importing and exporting with this "format". Just few weeks ago I had fun times working with API returning CSV in unknown encoding. Hopefully they will never make changes (you cannot always trust headers). Ah and i do love when CSV is missing headers and someone adds data into middle. Of course some of these issues can be avoided by doing the things "right". Sadly you cannot trust this in real life. People write ugly structures in JSON, but at least you can validate results..
- SillyUsername 4y agoHoly cow. If somebody asked me to support this format after you'd left the company I'd quit on the spot. This frankenformat is 100% premature optimization, non standardised, requires custom parsers (which are potentially inefficient and may negate the network performance from having to parse both json and csv) and is potentially very difficult to maintain and debug (no syntax highlighters or rest like posting tools) Just either use GRPC or JSON with regular network level gzip encoding.
- nathants 4y agoi had a lot of fun exploring the performance ceiling of csv and csv like formats. turns out binary encoding of size prefixed byte arrays is fast[1]. csv is just a sequence of 2d byte arrays. probably avoid if dealing with heterogeneous external data. possibly use if dealing with homogeneous internal data. 1. https://github.com/nathants/bsv/tree/55c90797283f5e37f91bbb6cdf60f0f187a33302/experiments https://github.com/nathants/bsv/tree/55c90797283f5e37f91bbb6...
- slotrans 4y agoPlease don't. CSV is one of the worst file formats ever conceived. Use (compressed) line-delimited JSON if you need a file of records.
- pcthrowaway 4y agoI'm definitely in the "Just use JSON for most things" camp, but I'm wondering, why would you ever choose CSV for interfacing microservices over protobuf? Isn't protobuf basically CSV but with good libraries at the interface point and standards around how to deserialize the streams?
- YmiYugy 4y agoI always thought CSV was just fine, until I had to ingest and export a bunch of CSV in my last project. The big problem is that CSV is not well defined and it's so deceptively simple that many don't bother to adhere to the spec that does exist. Just a few idiosyncrasies I found: Inconsistent character encoding. If you open or save a csv with Excel it will assume a Windows-1252 encoding. Since browsers deal exclusively with UTF-8, this get's really messy. The CSV I got didn't actually use a comma as a delimiter but a semicolon. Everyone seems to have conflicting options about whether strings should have quotes and if so, which ones. The CSV I had to deal with also came with a decimal comma, which screwed up even more stuff. My advice stay away from CSV as an exchange format. Use something that is well defined.
- WirelessGigabit 4y agoNo. Just no. The amount of times I've had issues with CSVs exported from a non-US locale is insane. They use semi-colon as separator, as for some weird reason they use the comma as the decimal point. Then there's the issue of encoding, as that is also not the same across locales. Then you get a CSV with the BOM characters up front or some French accents represented as ? because of incorrect encoding parsing / saving. At least JSON doesn't have any of these things. Standardized strings, and standardized number format.