Consider Using CSV

CSV 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!

As 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.

With gzip on web server the difference is not important at all.<p>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.

CSV 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:<p><pre><code> `r#csv2md(&#x27;filanme.csv&#x27;)` </code></pre> 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&#x2F;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&#x2F;diverge.)<p>Using JSON would be possible, but it&#x27;s not as easy to convert into a Markdown table.<p>[0]: <a href="https:&#x2F;&#x2F;github.com&#x2F;DaveJarvis&#x2F;keenwrite" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;DaveJarvis&#x2F;keenwrite</a><p>[1]: <a href="https:&#x2F;&#x2F;github.com&#x2F;DaveJarvis&#x2F;keenwrite&#x2F;blob&#x2F;main&#x2F;R&#x2F;csv.R#L35" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;DaveJarvis&#x2F;keenwrite&#x2F;blob&#x2F;main&#x2F;R&#x2F;csv.R#L3...</a><p>[2]: <a href="https:&#x2F;&#x2F;i.ibb.co&#x2F;6FLXKsD&#x2F;keenwrite-csv.png" rel="nofollow">https:&#x2F;&#x2F;i.ibb.co&#x2F;6FLXKsD&#x2F;keenwrite-csv.png</a><p>[3]: <a href="https:&#x2F;&#x2F;i.ibb.co&#x2F;47h6zNx&#x2F;keenwrite-table.png" rel="nofollow">https:&#x2F;&#x2F;i.ibb.co&#x2F;47h6zNx&#x2F;keenwrite-table.png</a>

The only reason, in my eyes, to use CSV is to have easy interoperability with spreadsheet software.<p>If you want streaming: <a href="https:&#x2F;&#x2F;jsonlines.org&#x2F;" rel="nofollow">https:&#x2F;&#x2F;jsonlines.org&#x2F;</a>

Sometimes CSV is nicer. Still you can cut down on your JSON by formatting it as a similar header style:<p><pre><code> [ [&quot;productId&quot;, &quot;quantity&quot;, &quot;customerId&quot;], [&quot;5710031efdfe&quot;, 1, &quot;8fe96b88&quot;], [&quot;479cd9744e5c&quot;, 2, &quot;526ba6f5&quot;] ] </code></pre> 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. :)

I think one of the issues it data types. JSON has them CSV doesn&#x27;t, so this means your program needs to be aware of which columns are which data type and do the conversion where needed.<p>It&#x27;s similar to JSON Vs INI files for config files.<p>On a different note I wouldn&#x27;t nest JSON in a CSV column. I&#x27;d delimit with a pipe or something the split string on that. Much simpler if you&#x27;re in control of the data.

The author didn&#x27;t compare gzipped&#x2F;brottlied sizes.<p>The author didn&#x27;t think of any examples with even a bit more complexity. If you have 2-level object nesting, now what?

&gt; It&#x27;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&#x27;s ~63% less than the JSON).<p>If size is really the issue but you still want schema enforcement protobuf is the way to go.

I mean point well taken, but, as they acknowledged in the post themselves, CSV isn&#x27;t suitable when you have a nested structure. And you almost <i>always</i> have&#x2F;need a nested structure, no?

I 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&#x27;s so deceptively simple that many don&#x27;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&#x27;s really messy. The CSV I got didn&#x27;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.

I&#x27;m definitely in the &quot;Just use JSON for most things&quot; camp, but I&#x27;m wondering, why would you <i>ever</i> choose CSV for interfacing microservices over protobuf?<p>Isn&#x27;t protobuf basically CSV but with good libraries at the interface point and standards around how to deserialize the streams?

I 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:<p><pre><code> 0x1E Record Separator 0x1F Unit Separator</code></pre>

No one uses that format for streamed json, see ndson and jsonl<p><a href="http:&#x2F;&#x2F;ndjson.org&#x2F;" rel="nofollow">http:&#x2F;&#x2F;ndjson.org&#x2F;</a><p>The size complaint is overblown, as repeated fields are compressed away.<p>As other folks rightfully commented, csv is a mine field. One should assume every CSV file is broken in some way. They also don&#x27;t enumerate any of the downsides of CSV.<p>What people <i>should</i> 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.

Since this is about CSV, this is obligatory tool for larger ones:<p>* <a href="https:&#x2F;&#x2F;github.com&#x2F;antonycourtney&#x2F;tad" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;antonycourtney&#x2F;tad</a>

Holy cow.<p>If somebody asked me to support this format after you&#x27;d left the company I&#x27;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)<p>Just either use GRPC or JSON with regular network level gzip encoding.

A constrained format based on JSONL with each record being a tuple of number&#x2F;string&#x2F;bool&#x2F;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.<p>[&quot;hello&quot;,5,false,1,2,2.334,null]<p>[&quot;world&quot;,12,true,1,2,2.334,null]

What else do you use if you have to import millions of rows from a client or supplier without direct integration but sftp?

I cannot count how many times CSV &quot;format&quot; has caused problems for me..<p>In my country the decimal separator is comma, instead of punctuation. This causes problems when importing and exporting with this &quot;format&quot;.<p>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.<p>Of course some of these issues can be avoided by doing the things &quot;right&quot;. Sadly you cannot trust this in real life. People write ugly structures in JSON, but at least you can validate results..

No. Just no. The amount of times I&#x27;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.<p>Then there&#x27;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 &#x2F; saving.<p>At least JSON doesn&#x27;t have any of these things. Standardized strings, and standardized number format.

You 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.

I quite like CSVs. I&#x27;ve used them to great effect at maybe every job I&#x27;ve ever had. xsv, sqlite, and Excel&#x2F;LibreOffice provide useful tooling on top of them.<p>I see a lot of complaining about &quot;no standard&quot; in this thread, but the way I&#x27;ve used them, it&#x27;s been fine. I just use Haskell&#x27;s cassava. If human produce them with Excel&#x2F;LibreOffice, I never have issues on the ingestion end.

Please don&#x27;t. CSV is one of the worst file formats ever conceived. Use (compressed) line-delimited JSON if you need a file of records.

Another alternative is a streaming JSON format like JSONL (newline-delimited JSON). You can parse one record&#x2F;line at a time, but still have the structure and named fields of JSON.<p><a href="https:&#x2F;&#x2F;en.m.wikipedia.org&#x2F;wiki&#x2F;JSON_streaming" rel="nofollow">https:&#x2F;&#x2F;en.m.wikipedia.org&#x2F;wiki&#x2F;JSON_streaming</a>

I 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

i 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].<p>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.<p>1. <a href="https:&#x2F;&#x2F;github.com&#x2F;nathants&#x2F;bsv&#x2F;tree&#x2F;55c90797283f5e37f91bbb6cdf60f0f187a33302&#x2F;experiments" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;nathants&#x2F;bsv&#x2F;tree&#x2F;55c90797283f5e37f91bbb6...</a>

Delimited 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.<p>U+241D, U+241E, U+241F

Use gzip for compressing. If you want to stream, use following syntax:<p>[\n<p><pre><code> { ... },\n { ... },\n { ... },\n ...\n </code></pre> ]\n<p>With this simple trick you can stream easily..

Sure! For example, for batch processing, CSV is always the default for me and the teams.

you 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.

You could also consider Kafka for streaming, and Parquet for batch.