57 ms·

Friends don't let friends export to CSV

• mrozbarry 3y ago
  Use the right data format for the right data. CSV can be imported into basically any spreadsheet, which can make it appealing, but it doesn't mean it's always a good option. If you want csv, considering a normalization step. For instance, make sure numbers have no commas and a "." decimal place. Probably quote all strings. Ensure you have a header row. Probably don't reach for a CSV if: - You have long text blobs with special characters (ie quotes, new lines, etc.) - You can't normalize the data for some reason (ie some columns have formulas instead of specific data) - You know that every user will always convert it to another format or import it
• nsjames 3y ago
  I think the sad reality there is that it's become "the" format that users expect, and more importantly, it's what's integrated into the majority of peripheral services and tools. Like JSON.
  • pyr0hu 3y ago
    Yeah, clients always expect CSV (or sometimes XLSX), but if I tell them that I'll send parquet data, they will ask if I'm having a stroke or something because they don't know what is parquet and how could they use it. CSV is just too simple and "user-friendly".
  • bell-cot 3y ago
    Oh, yes. And even if you can convince the client that they're wrong, and you're right - with substantial client datasets, there's always a load of "data not as previously represented" records. Resolving what is going on with those tends to be vastly easier when you can say "look at record 1,234,567" and they can easily do that in their favorite & familiar software.
    • 2devnull 3y ago
      Moreover, I myself like being able to open broken csv files in a text editor, to find nulls and other problematic junk.
• JumpCrisscross 3y ago
  > One of the infurating things about the format is that things often break in ways that tools can't pick up and tell you about This line is emblematic of the paradigm shift LLMs have brought. It’s now easier to build a better tool than change everyone’s behaviour. > You give up human readable files, but What are we even doing here.
  • jitl 3y ago
    We’re doing data pipelines. I would rather my data pipeline go 10x faster with Parquet than be able to human read a 30gb CSV file.
    • th0ma5 3y ago
      The vast majority of the thread is missing this point. The CSV abuse is out of control! Lol
  • MilStdJunkie 3y ago
    I think this article is causing a kerfuffle because it's hitting two different audiences very differently. Honestly the article title should have been "Friends don't let friends use CSV for data pipelines". Because when I'm wrangling data from a human - a human who is stubbornly defending their own little island of business information like their employment depended on it[1] - a CSV is about as good as I am gonna get. I had a bear of a time just convincing people to put their data in a delimited format, instead of a table inside a powerpoint presentation, or buried in sixty levels of Access joins, or in an SVG. I need data from "what is scroll wheel" sort of users. If I am working system to system? That's a different requirement, a requirement that is apparently where the OP author is coming from. [1] Because it kind of does. Having a unique platform is one of those priceless keys to being skipped in the thrice-yearly layoff rituals. Unfortunately, that means anyone approaching saying words like "integration" or "API" are shot on sight.
  • rzzzt 3y ago
    So you grab your favorite CllaumistraCoderPT-3.14-Turbo and ask the box to deduce CSV settings from the given example. It comes back with a set of characteristics others have brought up elsewhere in comments (field separator, decimal handling, quotes, date format, etc.) How do you verify it? What happens next?
• rietta 3y ago
  Or export to CSV correctly and test with Excel and/or LibreOffice. Honestly CSV is a very simple, well defined format, that is decades old and is “obvious”. I’ve had far more trouble with various export to excel functions over the years, that have much more complex third-party dependencies to function. Parsing CSV correctly is not hard, you just can’t use split and be done with it. This has been my coding kata in every programming language I’ve touched since I was a teenager learning to code.
  • dtech 3y ago
    Unfortunately that is only the case as long as you stay within the US. for non-US users Excel has pretty annoying defaults, such as defaulting to ; instead of , as a separator for "CSV", or trouble because other languages and Excel instances use , instead of . for decimal separators. A nice alternative I've used often is to constructor an excel table and then giving is an .xls extension, which Excel happily accepts and has requires much less user explanation than telling individual users how to get Excel to correctly parse a CSV.
    • rrr_oh_man 3y ago
      > Unfortunately, that is only the case as long as you stay outside the US. For US users Excel has pretty annoying defaults, such as defaulting to , instead of ; as a separator for "CSV", or trouble because US instances of Excel use . instead of , for decimal separators.
    • rietta 3y ago
      Localization is a thing. None of this is a show stopper. Subclasses and configuration screen for import and export.
      • Macha 3y ago
        And then you get a business analyst at your client going "I just hit export in our internal tool, what's this delimiter that you're asking me about in the upload form? Google Sheets doesn't ask me to tell them that"
• aronhegedus 3y ago
  My takeaway is that csv has some undefined behaviours, and it takes up space. I like that everyone knows about .csv files, and it's also completely human readable. So for <100mb I would still use csv.
  • nradov 3y ago
    If both parties implement RFC 4180 and use a consistent character set encoding then I don't think there are actually any undefined behaviors. But in practice a lot of implementations are simply broken, including those from major tech companies that ought to know better.
    • senknvd 3y ago
      I don't think RFC 4180 differentiates between an empty string and a null value. As long as you add a check that all string columns are free of empty values before writing you should be good. I think in polars it's df.filter(pl.col(pl.Utf8).str.len_bytes() == 0).shape[0] == 0 although there's probably a better way to write this.
      • nradov 3y ago
        Well I would consider differentiation between empty string versus null as simply being out of scope for CSV rather than undefined behavior. It was never intended as a complete database dump format.
    • thayne 3y ago
      And the application doesn't try to convert the cells into non-string data types like numbers, dates, etc.
      • nradov 3y ago
        Converting strings into other data types is out of scope for CSV, not really undefined behavior. The type conversions happen at a later stage of the import process.
• jjgreen 3y ago
  An article promoting parquet over CSV. Fair enough, but parquet has been around for a while and still no support in Debian. Is there some deep and dark reason why?
  • Cheer2171 3y ago
    What do you mean there is no parquet support in Debian? Data formats should be supported in userspace and there are plenty of parquet libraries and userspace tools an apt-get away. There is exactly as much support for tar in Debian as there is for parquet.
    • jjgreen 3y ago
      You have searched for packages that names contain parquet in suite(s) bookworm, all sections, and all architectures. Sorry, your search gave no results https://packages.debian.org/search?suite=bookworm&searchon=names&keywords=parquet https://packages.debian.org/search?suite=bookworm&searchon=n...
      • wizzwizz4 3y ago
        By the same logic, there's no Photoshop Document support – but GIMP and Krita both support it.
        • jjgreen 3y ago
          You have searched for photoshop in packages names and descriptions in suite(s) bookworm, all sections, and all architectures (including subword matching). Found 9 matching packages. Package abr2gbr bookworm (stable) (graphics): Converts PhotoShop brushes to GIMP 1:1.0.2-5: amd64 arm64 armel armhf i386 mips64el mipsel ppc64el s390x Package gimp bookworm (stable) (graphics): GNU Image Manipulation Program 2.10.34-1+deb12u2: amd64 arm64 armel armhf i386 mips64el mipsel ppc64el s390x : https://packages.debian.org/search?suite=bookworm&section=all&arch=any&searchon=all&keywords=photoshop https://packages.debian.org/search?suite=bookworm&section=al...
• thepra 3y ago
  I use JSON for import/export of user data (in my super app collAnon), it's more predictable and the toolings around it to transform into any other format(even csv) is underappreciated, imo.
• deleted 3y ago
  [deleted]
• addminztrator 3y ago
  Good thing I don't have friends then because I'm exporting to csv, it's simple and it works
• Closi 3y ago
  Of course if you only consider the disadvantages, something looks bad. The advantages of CSV are pretty massive though - if you support CSV you support import and export into a massive variety of business tools, and there is probably some form of OOTB support.
  • anymouse123456 3y ago
    This is the biggest win IME. You have a (usually) portable transport format that can get the information into and out of an enormous variety of tools that do not necessarily require a software engineer in the middle. I'm also struggling with such a quick dismissal of human readable formats. It's a huge feature. What happens when there's a problem with a single CSV file in some pipeline that's been happily running fine for years? You can edit the thing and move on with your day. If the format isn't human readable, now you may have to make and push a software update to handle it. Of course, CSV is a terrible format that can be horribly painful. No argument there. But despite the pain, it's still far better than many alternatives. In many situations.
    • chasil 3y ago
      In a POSIX shell, I actually prefer to use the bell character for IFS. while IFS="$(printf \\a)" read -r field1 field2... do ... done This works just as well as anything outside the range of printing characters. Getting records that contain newlines would be a bit trickier.
      • BenjiWiebe 3y ago
        I think IFS=$'\a' works too.
        • thristian 3y ago
          Only in bash and possibly other shells that extend the POSIX syntax, not in the basic POSIX standard.
• TrackerFF 3y ago
  The problem, as always, is that you deal with multiple data sources - which you can not control the format of. I work as a data analyst, and in my day-to-day work I collect data from around 10 different sources. It's a mix of csv, json, text, and what not. Nor can you control the format others want. The reason I have to export to csv, is unfortunately because the people I ship out to use excel for everything - and even though excel does support many different data formats, they either enjoy using .csv (should be mentioned that the import feature in excel works pretty damn well), or have some system written in VBA that parses .csv files.
• ysris 3y ago
  Not sure I understand right what this article is about. From my point of view, CSV is an easy way to export data from a system to allow an end user to import it in excel and work on this data. Apart if it's as easy with parquet to import in excel as with a CSV, I'm not sure this is not fixing a problem that doesn't exist. And making things more complicated. Outside of the context of end user, I don't see any advantages in this compared to xml or json export.
• benob 3y ago
  Can Parquet be read/parsed in almost every programming language with very little effort?
  • vesinisa 3y ago
    Exactly. The author even opens the article with the nice trivia that CSV has been in use since the 1970s. I don't think anyone disputes that CSV is a very primitive format. But I hope no one uses CSV because it is so performant or well-designed. It is used exactly because it is so universal, which makes the point of comparing against almost any other format moot unless they were around in the 1970s, too.
  • bluenose69 3y ago
    I just tried it in R. The relevant package seems to be "arrow", so I did install.packages("arrow") and then I did ?read_parquet to get an example. I tried the example, and got the error message as follows. This sort of error is really quite uncommon in R. So my answer to the "with little effort" is "no", at least for R. > tf<-tempfile() > write_parquet(mtcars, tf) Error in parquet___WriterProperties___Builder__create() : Cannot call parquet___WriterProperties___Builder__create(). See https://arrow.apache.org/docs/r/articles/install.html for help installing Arrow C++ libraries.
    • 2devnull 3y ago
      Arrow going back and forth between r/python can be a catch too iirc.
  • addandsubtract 3y ago
    More importantly, can you open parquet files in Excel?
    • JumpCrisscross 3y ago
      No, you have to convert it to CSV first [1]. Or install a driver [2]. [1] https://www.gigasheet.com/post/how-to-open-parquet-file https://www.gigasheet.com/post/how-to-open-parquet-file [2] https://www.cdata.com/kb/tech/parquet-odbc-excel-query.rst https://www.cdata.com/kb/tech/parquet-odbc-excel-query.rst
• Black616Angel 3y ago
  I never liked articles about how you should replace CSV with some other format while pulling some absolutely idiotic reasons out of their rear... 1. CSV is underspecified Okay, so specify it for your use case and you're done? E.g use rfc3339 instead of the straw-man 1-1-1970 and define how no value looks like, which is mostly an empty string. 2. CSV files have terrible compression and performance Okay, who in their right mind uses a plain-text-file to export 50gb of data? Some file systems don't even support that much. When you are at the stage of REGULARLY shipping around files this big, you should think about a database and not another filetype to send via mail. Performance may be a point, but again, using it for gigantic files is wrong in the first place. 3. There's a better way (insert presentation of a filtype I have never heard of) There is lots of better ways to do this, but: CSV is implemented extremely fast, it is universally known unlike Apache Parquet (or Pickle or ORC or Avro or Feather...) and it is humanly readable. So in the end: Use it for small data exports where you can specify everything you want or like everywhere, where you can import data, because most software takes CSV as input anyway. For lots of data use something else. Friends don't let friends write one-sided articles.
  • javcasas 3y ago
    For lots of data zip the csv. For REALLY lots of data, think something different.
  • HayBale 3y ago
    2. You would be surprised, especially on the science/university level in stat, health or bioinfo. Unfortunately a lot of people go with the path of least resistance and use excel propertiary format or csv for everything. Like NHS with their post covid data due to excel limitations or gene name conversion problems in sci journals. Same happens with stupid amount of laboratory management things or bioinformatics tools. Honestly obviously the article is biased but we should at least think about moving away from csv in non customer facing fronts. Small files? Json Big files? SQLlite or parquet.
  • alserio 3y ago
    I agree with your other points but the first point misses the mark. Even you specify a format, you cannot use the file for exporting data between systems and organizations if they don't all agree on that format. CSV does not have a reasonable way to encode that is using a specific spec. I can open your data with my tools and silently misinterpret it. But if you are only exporting data between yourself, that's another story.
• deleted 3y ago
  [deleted]
• aabbcc1241 3y ago
  An alternative is to export to sqlite file
• rossvor 3y ago
  Friends don't let friends export to CSV [for my specific use case]
• danirod 3y ago
  Friends don't let friends export to CSV -- in the data science field. But outside the data science field, my experience working on software programming these years is that it won't matter how beautiful your backoffice dashboards and web apps are, many non-technical business users will demand at some point CSV import and/or export capabilities, because it is easier for them to just dump all the data on a system into Excel/Sheets to make reports, or to bulk edit the data via export-excel-import rather than dealing with the navigation model and maybe tens of browser tabs in your app.
  • xupybd 3y ago
    Exactly Excel is the UI they know. This trumps every technical argument you can come up with. People don't want to throw out 20 years of experience with a tool to use your custom UI.
  • mrgoldenbrown 3y ago
    But why the half measure of csv when it's just as simple to use a library to export to an actual excel file (which is really just xml) , which will properly preserve your data and make the business users happy.
• PLenz 3y ago
  Everything reads it, everything writes it. CSV is the one true data format to which everything else will eventually be converted to by users.
• tracker1 3y ago
  I tend to prefer line delimited JSON myself, even if it's got redundant information. It will gzip pretty well in the data if you want to use less storage space. Either that or use the ASCII codes for field and row delimiters on a UTF-8 file without a BOM. Even then you're still stuck with data encoding issues with numbers and booleans. And that direct even cover all the holes I've seen in CSV in real world use by banks and govt agencies over the years. When I've had to deal with varying imports I push for a scripted (js/TS or Python) preprocessor that takes the vender/client format and normalized to line delimited JSON, then that output gets imported. It's far easier than trying to create a flexible importer application. Edit: I've also advocated for using SQLite3 files for import, export and archival work.
• fifilura 3y ago
  "Friends don't send parquet files to analysts who wants them in their spreadsheet program"
• deleted 3y ago
  [deleted]
• johnea 3y ago
  I've written CSV exports in C from scratch, no external dependencies required. It's "Comma Separated Variables", it doesn't really need anymore specification than that. These files have always imported into M$ and libre office suites without issue.
  • nradov 3y ago
    It needs more specification than that. Have you read RFC 4180?
  • nmz 3y ago
    oh boy. here's where it breaks - supporting "" (single) - Supporting newlines in "", oops, now you can't getline() and instead need to getdelim() - Supporting comments # (why is this even a thing) - Supporting multiple "" in a field - Escaping " with "" or \" - length based csv, so all fields are seekable. It's a mess, which one's your csv?
    • outop 3y ago
      The vast majority of CSVs do not have strings which include either quotes or newlines. No CSV I have ever encountered has comments.
      • eviks 3y ago
        So you're fine with a lot of bugs in the case of a vast minority?
        • outop 3y ago
          Well, most code that loads CSVs is intended to work with certain files from certain sources, and not with all the CSVs that have ever existed. So yes, I am happy with code that works for a subset of files. There are thousands of applications which work with CSVs and they all do exactly this.
          • 3y ago
• cheald 3y ago
  "Okay, but how do I open it in Excel?"
• kembrek 3y ago
  If one could export to Parquet from Microsoft Excel, I think this would be a goer. Until such time, it seems likely many will stick with CSVs.
  • nmz 3y ago
    If you had the option to use whatever you want, and you're already sacrificing not using a text editor, why would you use parquet instead of the myriad of choices.
  • efxhoy 3y ago
    v1 or v2?
• prepend 3y ago
  CSV is very durable. If I want it read in 20 years, csv is the way to go until it’s just too big to matter. Of course there are better formats. But for many use cases friends encourage friends to export to CSV.
  • josephg 3y ago
    Eh. I much prefer to produce and consume line delimited JSON. (Or just raw JSON). Its easy to parse, self descriptive and doesn't have any of CSV's ambiguity around delimiters and escape characters. Its a little harder to load into a spreadsheet, but in my experience, way easier to reliably parse in any programming language.
    • VHRanger 3y ago
      If you send someone JSON there's no guarantee the data is tabular, or even formatted, though
      • ravenstine 3y ago
        Better something that can be parsed than not parsable at all?
        • forgetfreeman 3y ago
          If you require parse logic to import even the most trivial data export you've failed at several tasks concurrently.
    • brian-bk 3y ago
      JSON(newline delimited or full file) is significantly larger than csv. With csv the field name is mentioned once in the header row. In JSON every single line repeats the field names. It adds up fast, and is more of a difference than between csv to parquet.
      • josephg 3y ago
• 2devnull 3y ago
  “the use case where people often reach for CSV, parquet is easily my favorite” My use case is that other people can’t or won’t read anything but plain text.
• _trampeltier 3y ago
  CSV wins because its universal and very simple. With an editor like Notepad++ and the CSV plugin, reformating, like change date format, is very easy and even with colored columns.
• prepend 3y ago
  This article seems written by someone who never had to work with diverse data pipelines. I work with large volumes of data from many different sources. I’m lucky to get them to send csv. Of course there are better formats, but all these sources aren’t able to agree on some successful format. Csv that’s zipped is producible and readable by everyone. And that makes is more efficient. I’ve been reading these “everyone is stupid, why don’t they just do the simple, right thing and I don’t understand the real reason for success” articles for so long it just makes me think the author doesn’t have a mentor or an editor with deep experience. It’s like arguing how much mp3 sucks and how we should all just use flac. The author means well, I’m sure. Maybe his next article will be about how airlines should speak Esperanto because English is such a flawed language. That’s a clever and unique observation.
  • gerdesj 3y ago
    ... And what's more, you'll be an Engineer my son.
  • hn_throwaway_99 3y ago
    Totally agree. His arguments are basically "performance!" (which is honestly not important to 99% of CSV export users) and "It's underderspecified!" And while I can agree with the second, at least partly, in the real world the spec is essentially "Can you import it to Excel?". I'm amazed at how much programmers can discount "It already works pretty much everywhere" for the sake of more esoteric improvements. All that said (and perhaps counter to what I said), I do hope "Unicode Separated Values" takes off. It's essentially just a slight tweak to CSV where the delimiters are special unicode characters, so you don't have to have complicated quoting/escaping logic, and it also supports multiple sheets (i.e. a workbook) in a single file.
    • ryncewynd 3y ago
      Are there unicode characters specifically for delimiters? If Excel had a standardised "Save as USV" option it would solve so many issues for me. I get so many broken CSVs from third-parties
• WatchDog 3y ago
  The only real problems I ever have with CSV, is when excel is involved.
• anothernewdude 3y ago
  I like how their alternative is an instant non-starter.
  • thesnide 3y ago
    Yes... if i need a better format, i'll just use sqlite.
• onethumb 3y ago
  CSV has some limits and difficulties, but has massive benefits in terms of readability, portability, etc. I feel like USV (Unicode Separated Values) neatly improves CSV while maintaining most of its benefits. https://github.com/sixarm/usv https://github.com/sixarm/usv
• GuB-42 3y ago
  As a French, there is another problem with CSV. In the French locale, the decimal point is the comma, so "121.5" is written "121,5". It means, of course, that the comma can't be used as a separator, so the semicolon is used instead. It means that depending whether or not the tool that exports the CSV is localized or not, you get commas or you get semicolons. If you are lucky, the tool that imports it speaks the same language. If you are unlucky, it doesn't, but you can still convert it. If you are really unlucky, then you get commas for both decimal numbers and separators, making the file completely unusable. There is a CSV standard, RFC 4180, but no one seems to care.
  • Gabriel54 3y ago
    If I'm not mistaken this is pretty universal outside of the US (and maybe the UK).
    • zztop44 3y ago
      You are mistaken. Probably more countries overall use a decimal comma, but the decimal point is used as convention in many countries, including China, India, Nigeria and the Philippines.
    • sfRattan 3y ago
      Going by the Wikipedia article and included map, use of comma versus period as decimal separators is roughly an even split: https://en.wikipedia.org/wiki/Decimal_separator https://en.wikipedia.org/wiki/Decimal_separator https://commons.wikimedia.org/wiki/File:DecimalSeparator.svg https://commons.wikimedia.org/wiki/File:DecimalSeparator.svg
      • fshr 3y ago
        There's definitely a big distribution disparity. 11 of the 15 most populous countries use the period for decimals.
        • micheljansen 3y ago
• breadwinner 3y ago
  The reason CSV is popular is because it is (1) super simple, and (2) the simplicity leads to ubiquity. It is extremely easy to add CSV export and import capability to a data tool, and that has come to mean that there are no data tools that don't support CSV format. Parquet is the opposite of simple. Even when good libraries are available (which it usually isn't), it is painful to read a Parquet file. Try reading a Parquet file using Java and Apache Parquet lib, for example. Avro is similar. Last I checked there are two Avro libs for C# and each has its own issues. Until there is a simple format that has ubiquitous libs in every language, CSV will continue to be the best format despite the issues caused by under-specification. Google Protobuf is a lot closer than Parquet or Avro. But Protobuf is not a splitable format, which means it is not Big Data friendly, unlike Parquet, Avro and CSV.
  • wetpaws 3y ago
    [dead]
  • msla 3y ago
    > there are no data tools that don't support CSV format. They support CSV but not your CSV. For example, how does quoting work? Does quoting work?
    • rovr138 3y ago
      I was working with a vendor’s csv recently… They had never had a customer do X on Y field, so they never quoted it nor added code to quote it if needed.. Of course, we did X in one entry. Took me too long to find that which obviously messed up everything after.
  • kerkeslager 3y ago
    > Google Protobuf is a lot closer than Parquet or Avro. But Protobuf is not a splitable format, which means it is not Big Data friendly, unlike Parquet, Avro and CSV. Eh, I don't think that's the problem. If that was the problem, there are a zillion ways to chunk files; .tar is probably the most ubiquitous but there are others. The bigger problem is that Protobuf is way harder to use. Part of the reason CSV is underspecified is it's simple enough it feels, at first glance, like it doesn't need specification. Protobuf has enough dark corners that I definitely don't know all of it, despite having used it pretty extensively. I think Unicode Separated Values (USV) is a much better alternative and as another poster mentioned, I hope it takes off.
• baazaa 3y ago
  Schemas are overrated. Often the source-system can't be trusted so you need to check everything anyway or you'll have random strings in your data. Immature languages/libraries often do dumb stuff like throwing away the timezone before adjusting it to UTC. They might not support certain parquet types (e.g. an interval). Like I've recently found it much easier to deal with schema evolution in pyspark with a lot of historical CSVs than historical parquets. This is essentially a pyspark problem, but if everything works worse with your data format then maybe it's the format that's the problem. CSV parsing is always and everywhere easy, easier than the problems parquets often throw up. The only time I'd recommend parquet is if you're setting up a pipeline with file transfer and you control both ends... but that's the easiest possible situation to be in; if your solution only works when it's a very easy problem then it's not a good solution.
• black_13 3y ago
  [dead]
• 392 3y ago
  I must have missed when Excel added Parquet support.
  • bongodongobob 3y ago
    Yeah, I think the use case for CSVs 99% of the time is so you can load it into Excel and do things with it. Any other use case is going to involve a DB and if you're exporting databases to import to Excel or another DB, you are in fact doing it wrong.
• j7ake 3y ago
  I wouldn’t say the article proposes a better way, but he proposes rather a more complex way. Nothing beats CSV in terms of simplicity, minimal friction, and ease of exploring across diverse teams.
• quesera 3y ago
  Every single use I've ever seen of CSV would be improved by the very simple change to TSV. Even Excel can handle it. It is far safer to munge data containing tabs (convert to spaces, etc), than commas (remove? convert to dots? escape?). The better answer is to use ASCII separators as Lyndon Johnson intended, but that turns out to be asking a lot of data producers. Generating TSV is usually easier than generating CSV.
  • chirau 3y ago
    you are assuming the regular end user knows the difference between 4 spaces and a tab and the nuances that come with them trying to replace one with the other or why space between two values is different at one point from the next. Commas are, by far, better delimeters than tabs in the grand scheme of things and with both expert and regular users considered.
    • quesera 3y ago
      I disagree. The end user doesn't need to know the difference between 4 spaces and a tab. Tabs are just whitespace. Tabs are uncommon but convenient whitespace. Commas are extremely common content. Tabs are a vastly better delimiter. If you are wrapping source code in a CSV, a) you're doing it wrong, and b) you'll get bitten by newlines just as quickly! If you're including content that requires specific whitespace preservation, just escape the (usually rare) tabs. TSV certainly is not perfect. But it solves the major problems for 95% of CSVs, and it's just as convenient for humans. I do agree that one should not arbitrarily munge content. But note that HTML does munge whitespace, and we've never suffered meaningfully for it.
      • chirau 2y ago
        I don't think you fully understood what i was saying. If a regular user had rows like this Adam Smith 27 WA JonathanBoyd 23 NC They are likely going to have tougher time adding a new row as compared to if it was comma delimited. You underestimate the simplicity of end users and how tabs and spaces can confuse them. This is why they prefer Excel, with boxes, because they cannot keep up with formatting and such. Tabs are spaces to many people. Commas are clearer.
• deleted 3y ago
  [deleted]
• SPBS 3y ago
  1. CSV is for ensuring compatibility with the widest range of consumers, not for ensuring best read or storage performance for consumers. (It is already more efficient than JSON because it can be streamed, and takes up less space than a JSON array of objects) 2. The only data type in CSV is a string. There is no null, there are no numbers. Anything else must be agreed upon between producer and consumer (or more commonly, a consumer looks at the CSV and decides how the producer formatted it). JSON also doesn’t include dates, you’re not going to see people start sending API responses as Apache Parquet. CSV is fiiine.
• randomsolutions 3y ago
  I like the ping pong of one day an article being posted where everyone asks, "when/why did everything become so complicated", and then the next day something like this is posted.
• bongodongobob 3y ago
  The author seems to be missing the point of CSVs entirely. I looked him up expecting a fresh college grad, but am surprised to see he's probably in his early 30s. Seems to be in a dev bubble that doesn't actually work with users. Try telling 45 year old salesman he needs to export his data in parquet. "Why would I need to translate it to French??" I feel like I'm pretty up to date on stuff, and I've never heard of parquet or seen in as an option, in any software, ever.
• gmoot 3y ago
  "I'm a big fan of Apache Parquet as a good default. You give up human readable files, but..." Lost me right there. It has to be human readable.
• MisterBastahrd 3y ago
  Trying to do business without using CSV is like trying to weld without using a torch. Might be possible but you aren't likely to have success at it.
• noddingham 3y ago
  Tell me you've never worked a real job without telling me. This is a technologists solution in search of a problem. Do you also argue that "email is dead"?
• jgord 3y ago
  CSV is a superb, incredibly useful data format.. but not perfect or complete. Instead of breaking CSV by adding to it .. I recommend augmenting it : It would be useful to have a good standardized / canonical json format for things like encoding, delimiter, schema and metadata, to accompany a zipped csv file, perhaps packaged in the same archive. Gradually datasets would become more self-documenting and machine-usable without wrangling.
  • cxr 3y ago
    > It would be useful to have a good standardized / canonical json format for things like encoding, delimiter, schema and metadata We already have that. Dan Brickley and others put a lot of thoughtful effort into it <https://www.w3.org/TR/tabular-data-primer/#dialects https://www.w3.org/TR/tabular-data-primer/#dialects>: > A lot of what's called "CSV" that's published on the web isn't actually CSV. It might use something other than commas (such as tabs or semi-colons) as separators between values, or might have multiple header lines. [...] You can provide guidance to processors that are trying to parse those files through the `dialect` property As is usually the case with standards, it's not that the standard doesn't exist but that people just don't even bother checking (much less caring about what it says or actually trying to follow it).
• neonsunset 3y ago
  If you ever need to parse CSV really fast and happen to know C#, there is an incredible vectorized parser for that: https://github.com/nietras/Sep/ https://github.com/nietras/Sep/
• cozzyd 3y ago
  there needs to be some pandoc (panbin?) for binary formats to convert between parquet, hdf5, fits, netcdf, grib, ROOT, sqlite, etc. (Ok these are not all equivalent in capability...).
• rossdavidh 3y ago
  "You give up human readable files, but what you gain in return is..." Stop right there. You lose more than you gain. Plus, taking the data out of [proprietary software app my client's data is in] in csv is usually easy. Taking the data out in Apache Parquet is...usually impossible, but if it is possible at all you'll need to write the code for it. Loading the data into [proprietary software app my client wants data put into] using a csv is usually already a feature it has. If it doesn't, I can manipulate csv to put it into their import format with any language's basic tools. And if it doesn't work, I can look at the csv myself, because it's human readable, to see what the problem is. 90% of real world coding is taking data from a source you don't control, and somehow getting it to a destination you don't control, possibly doing things with it along the way. Your choices are usually csv, xlsx, json, or [shudder] xml. Looking at the pros and cons of those is a reasonable discussion to have.
  • TimTheTinker 3y ago
    I think his arguments apply more closely to SQLite databases. They're not directly human readable, but boy are there a lot of tools for working with them.
    • askvictor 3y ago
      We have a use case where we effectively need to have a relational database, but in git. The database doesn't change much, but when it does, references between tables may need to be updated. But we need to easily be able to see diffs between different versions. We're trying an SQLite DB, with exports to CSV as part of CI - the CSV files are human-readable and diff'able. It's also worth noting that SQLite can ingest CSV files into memory and perform queries on them directly - if the files are not too large, it's possible to bypass the sqlite format entirely.
      • zachmu 3y ago
        Somebody already said this, but we built exactly this and it's called Dolt. https://github.com/dolthub/dolt https://github.com/dolthub/dolt Would love to hear how it addresses your use case or falls short.
• thayne 3y ago
  Parquet is a columnar format. Which might be what you want, but it also might not, like if you want to process one row at a time in a stream. Maybe avro would be a better format in that case?
• liquidify 3y ago
  what about just exporting to sqlite files?
  • outop 3y ago
    This is the right answer for the cases when your complexity grows beyond csv.
  • rkaveland 3y ago
    sqlite is an excellent choice.
• throwitaway222 3y ago
  I'd rather work with someone that prefers a format, but doesn't write articles like this. It's fine to "prefer" parquet, but CSV is totally fine - whatever works mate. When you hit the inevitable "friends don't let friends" or "considered harmful" type of people, it's time to move quickly past them and let the actual situation dictate the best solution.
• QuiDortDine 3y ago
  > You give up human readable files, but what you gain in return is incredibly valuable Not as valuable as human-readable files. And what kind of monstrous CSV files has this dude been working with? Data types? Compression? I just need to export 10,000 names/emails/whatevers so I can re-import them elsewhere. Like, I guess once you start hitting GBs, an argument can be made, but this article sounds more like "CSV considered harmful", which is just silly to me.
• croes 3y ago
  >Numerical columns may also be ambigious, there's no way to know if you can read a numerical column into an integral data type, or if you need to reach for a float without first reading all the records. Most of the time you know the source pretty well and can simply ask about the value range.
  • duped 3y ago
    If you just assume f64 then you have 53 bits of integer precision which is more than enough for the fast majority of applications. If JS hasn't proven this thoroughly, I don't know what has. Obviously there are edges, but they're edges by nature. And like you say, you usually know the source pretty well.
• F_J_H 3y ago
  meh
• rascul 3y ago
  CSV can be fine with some well defined datasets. It can get weird in other cases, though.
• mlhpdx 3y ago
  I’ve always liked CSV. It’s a streaming friendly format so: - the sender can produce it incrementally - the receiver can begin processing it as soon as the first byte arrives (or, more roughly, unescaped newline) - gzip compression works without breaking the streaming nature Yeah, it’s a flawed interchange format. But in a closed system over HTTP it’s brilliant.
• codeonline 3y ago
  The utillity of a file being human readable cant be overstated. File formats like CSV will outlast religion.
• nxpnsv 3y ago
  Csv sure is a step up from excel though…
• Twirrim 3y ago
  One particularly memorable on-call shift had a phenomenal amount of pain caused by the use of CSV somewhere along the line, and a developer who decided to put an entry "I wonder, what happens if I put in a comma", or something similar. That single comma caused hours of pain. Quite why they thought production was the place to test that, when they knew the data would end up in CSV, is anybody's guess. I think Hanlon's razor applies in that situation.
• lolive 3y ago
  As a data architect in a big company, I cannot tell how harmful such a stupid data format CSV can be. All the possible semantics of the data has to be offloaded to either the brain of people [don’t do that! Just don’t!] or out-of-sync specs [better hidden in the CMS of the company that the Ark of Alliance, and outdated anyway] or obscure code or SQL queries [an opportunity for hilarious reverse engineering sessions, where you hate a retired developper forever for all the tricks he added inside code to circumvent poorly defined data. Then got away to Florida beach after hiring you.]
  • lolive 3y ago
    The best thing I see really often is people sending the data model of a CSV file as, #guessWhat, ANOTHER CSV file!!! [please kill me!]
• lolive 3y ago
  I still don’t understand how you deal with cardinalities in a CSV. You always recreate an object model on top of it to deal with them properly ? Cf a tweet I wrote in one of my past lives: https://x.com/datao/status/1572226408113389569?s=20 https://x.com/datao/status/1572226408113389569?s=20
• lolive 3y ago
  The reason why USV did not use the proper ASCII codes for field separator and record separator is a bit too pragmatic for me… https://github.com/SixArm/usv/tree/main/doc/faq#why-use-control-picture-characters-rather-than-the-control-characters-themselves https://github.com/SixArm/usv/tree/main/doc/faq#why-use-cont...
• _shantaram 3y ago
  Surprised no one has mentioned sqlite even once in these comments.
  • rkaveland 3y ago
    I regret forgetting about it in the article. sqlite is a great solution.
  • reportgunner 3y ago
    "Friends don't let friends write SQL" /s
• visitor4712 3y ago
  excel 2021: the "a spreadsheet is all it needs"-file is not usable because excel is not able to translate the "LC references that are inside brackets" into other languages.
• rkaveland 3y ago
  Author here. I see now that the title is too controversial, I should have toned that down. As I mention in the conclusion, if you're giving parquet files to your user and all they want to know is how to turn it into Excel/CSV, you should just give them Excel/CSV. It is, after all, what end users often want. I'm going to edit the intro to make the same point there. If you're exporting files for machine consumption, please consider using something more robust than CSV.
  • cryptonector 3y ago
    > I see now that the title is too controversial, I should have toned that down. Sometimes a click-baity title is what you need to get a decent conversation/debate going. Considering how many comments this thread got, I'd say you achieved that even if sparking a lengthy HN thread had never been your intent.
  • fifilura 3y ago
    Congratulations for getting the article upvoted and don't be too hard on yorself.
  • smcin 3y ago
    Well what would be a more accurate title? "CSV format should only be for external interchange or archival; columnar formats like Parquet or Arrow better for performance"? People are busy; instead of hinting "something more robust than CSV", mention the alternatives and show a comparison (load time/search time/compression ratio) summary graph. (Where is the knee of the curve?) There's also an implicit assumption to each use-case about whether the data can/should fit in memory or not, and how much RAM a typical machine would have. As you mention, it's pretty standard to store and access compressed CSV files as .csv.zip or .csv.gz, which mitigates at least trading off the space issue for a performance overhead when extracting or searching. The historical reason a standard like CSV became so entrenched with business, financial and legal sectors is the same as other enterprise computing; it's not that users are ignorant; it's vendor and OS lock-in. Is there any tool/package that dynamically switches between formats internally? estimates comparative file sizes before writing? ("I see you're trying to write a 50Gb XLSX file...") estimates read time when opening a file? etc. Those sort of things seem worth mentioning.
• atoav 3y ago
  CSV is totally fine if you use it for the right kind of data and the right application. That means: - data that has predictable value types (mostly numbers and short labels would be fine), e.g. health data about a school class wouldn't involve random binary fields or unbounded user input - data that has a predictable, managable length — e.g. the health data of the school class wouldn't be dramatically longer than the number of students in that class - data with a long sampling period. If you read that dataset once a week performance and latency become utterly irrelevant - if the shape of your data is already tabular and not e.g. a graph with many references to other rows - if the gain in human readability and compatibility for the layperson outweighs potential downsides about the format - if you use a sane default for encoding (utf8, what else), quoting, escaping, delimiter etc. Every file format is a choice, often CSV isn't the wrong one (but: very often it is).
• deleted 3y ago
  [deleted]
• mukundesh 3y ago
  Using parquet in python requires installing pyarrow and numpy, whereas CSV comes with stdlib. Also, the csv has a very pythonic interface vis-a-vis parquet, in most cases if I can fit the file in memory I would go with CSV.
• Evidlo 3y ago
  There is CSVY, which lets you set a delimiter, schema, column types, etc. and has libraries in many languages and is natively supported in R. Also is backwards-compatible with most CSV parsers. https://github.com/leeper/csvy https://github.com/leeper/csvy
• sam_goody 3y ago
  I have all SQL exported to CSV and committed to git once a day (no, I don't think this is the same as WAL/replication). Dumping to CSV is built into MySQL and Postgres (though MySQL has better support), is faster on export and much faster on import, doesn't fill up the file with all sorts of unneeded text, can be diffed (and triangulated by git) line by line, is human readable (eg. grepping the CSV file) and overall makes for a better solution than mysqldumping INSERTs. In Docker, I can import millions of rows in ~3 minutes using CSV; far better than anything else I tried when I need to mock the whole DB. I realize that the OP is more talking about using CSV as a interchange format or compressed storage, but still would love to hear from others if my love of CSV is misplaced :)
• JonChesterfield 3y ago
  XML. Not CSV, not Parquet (whatever that is), not protobufs. Export the data as XML, with a schema. Not json or yaml either. You can render the XML into whatever format you want downstream. The alternative path involves parsing csv in order to turn it into a different csv, turning json into yaml and so forth. Parsing "human readable" formats is terrible relative to parsing XML. Go with the unambiguous source format and turn it into whatever is needed in various locations as required.
• throwaway38375 3y ago
  CSVs won the war. No vendor lock in and very portable. I wish TSVs were more popular though. Tabs appear less frequently than commas in data. My biggest recommendation is to avoid Excel! It will mangle your data if you let it.
• cess11 3y ago
  SQL and XML have schemas, and they're to a large extent human readable, even to people who aren't developers. If storage is cheap, compression isn't very important. I've never come across this Parquet-format, is it grep:able? Gzip:ed CSV is. Can a regular bean counter person import Parquet into their spreadsheet software? A cursory web search indicates they can't without having a chat with IT, and SQL might be easier while XML seems pretty straightforward. Yes, CSV is kind of brittle, because the peculiarities with a specific source is like an informal schema but someone versed in whatever programming language makes this Parquet convenient won't have much trouble figuring out a CSV.
• culebron21 3y ago
  The poor performance argument is not true even for Python ecosystem that the author discusses. Try saving geospatial data in GeoPackage, GeoJson, FlatGeobuf. They are saved slower than in plain CSV (the only inconvenience is that you must convert geometries into WKT strings). GeoPackage was "the Format of the Future" 8 years ago, but it's utterly slow when saving, because it's an SQLite database and indexes all the data. Files in .csv.gz are more compact than anything else, unless you have some very-very specific field of work and a very compressible data. As far as I remember, Parquet files are larger than CSV with the same data. Working with the same kind of data in Rust, I see everything saved and loaded in CSV is lightning fast. The only thing you may miss is indexing. Whereas saving to binary is noteably slower. A data in generic binary format becomes LARGER than in CSV. (Maybe if you define your own format and write a driver for it, you'll be faster, but that means no interoperability at all.)
  • smcin 3y ago
    > for geospatial data... GeoPackage was "the Format of the Future" 8 years ago What's the current consensus? Can you link to a summary article? (Some still say GeoPackage is: https://mapscaping.com/shapefiles-vs-geopackage/ https://mapscaping.com/shapefiles-vs-geopackage/ )
    • culebron21 3y ago
      I'd say compared to Shapefile, it is indeed better in every aspect (to begin with, shp has 8-character column names limit). For some kinds of data and operations GPKG is superior to other geo-formats. Like 1) store a lot of data, but retreive within an area (you can set an arbitrary polygon as a filter with GDAL driver, IIRC), 2) append/delete/modify and have the data indexed -- with CSV here you'll have to just reprocess and rewrite the entire file. The problem is that in data science you want whole datasets to be atomic, to have reproducible results. So you don't care much of these sub-dataset operations. Another sudden issue with GPKG and atomicity is that sqlite changes DB modification time every time you just read. So if you use Makefile, which checks for updates by modification time, you either have to let it re-run some updates, or manually touch other files downstream, or rely on separate files that you `touch` (unix tool that updates file's modification time). I read a Russian OSM blogger Ilya Zverev evangelize for GPKG back in 2016 in his blog: https://shtosm.ru https://shtosm.ru. I guess he was referring to GPKG vs ShapeFile too, not CSV. I think he's totally correct in this. But look above at my other comment with a benchmark: CSV turns out far easier on resources if you have lots of points. Back in 2017 I've made a tool that could read and write CSV, Fiona-supported formats (GeoJson, GPKG, CSV, Postgres DB), and our proprietary MongoDB. (Here's the tool, without the Mongo feature https://github.com/culebron/erde/ https://github.com/culebron/erde/ ) And I tried all easily available formats, and every single one has some favorable cases, and sucks at some other (well, Shapefile is outdated, so it's out of competition). Among them, FGB is kinda like better GPKG if you don't need mutations.
• ykonstant 3y ago
  I finally set aside my laziness and started a thread on r/vim for the .usv project: https://www.reddit.com/r/vim/comments/1bo41wk/entering_and_displaying_ascii_separators_in_vim/ https://www.reddit.com/r/vim/comments/1bo41wk/entering_and_d...?
• eska 3y ago
  It's strange to me that people complain about some variety in CSV files while acting as if parquet was one specific file format that's set in stone. They can't even decide which features are core, and the file format has many massive backwards-incompatible changes already. If you give me a parquet file I cannot guarantee that I can read it, and if I produce one I cannot guarantee that you can. I treat formats such as parquet as I generally do: I try to allow various different inputs, and produce standard outputs. Parquet is something I allow purely as an optimization. CSV is the common default all of my tools have (UTF-8 without BOM, international locale, comma separator, quoting at the start of the value optional, standards-compliant date format or unix timestamps). Users generally don't have any issue with adapting their files to that format if there's any difference.
  • orthoxerox 3y ago
    That's true. Parquet went through the weirdest changes between its various revisions and because it was used for Hadoop data lakes, there's a whole bunch of data that is being stored in legacy formats. Off the top of my head: - different physical types to store timestamps: INT96 vs INT64 - different ways to interpret timestamps before tzdb (current vs earliest tzdb record) - different ways to handle proleptic Gregorian dates and timestamps - different ways to handle time zones (since Parquet only has the equivalents of LocalDateTime and Instant, but no OffsetDateTime or ZonedDateTime and earlier versions of Hive 3 were terribly confused which is which) - decimal data type was written differently, as a byte array in older versions and as int/byte array/binary in the newer ones - Hadoop ecosystem doesn't support decimals longer than 38 digits, but the file format supports them
• coxley 3y ago
  xsv makes dealing with csv miles easier: https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
• shadowgovt 3y ago
  CSV is still a nice, compact intermediate between ease of reading and ease of processing, which is an advantage most alternatives lack.
• atroxone 3y ago
  "csv is terrible!" Screams the rustocean from his ivory tower
• braiamp 3y ago
  Openrefine has saved my bacon more times that I care to admit. It ingest everything and have powerful exporting tools. Friends give friends CSV files, and also tell them about tools that help them deal with wide array of crap formats.
• unsupp0rted 3y ago
  I wish I could get Excel to stop converting Product UPCs to scientific notation when opening CSVs. Also some UPCs start with 0 Worst is when Excel saves the scientific notation back to the CSV, overwriting the correct number.
  • reportgunner 3y ago
    1. Open a blank workbook 2. Enable the legacy Text Import Wizard as per [0] 3. Go to Data -> Get Data -> Legacy Wizards -> From Text (Legacy) 4. Set config based on your CSV file, typically select "Delimited" and "My data has headers" enabled 5. Click Next and pick the delimiter, typically "Comma" 6. Click Next and click the columns with UPCs, select "Text" in the "Column data format" area 7. Click Finish (I'm not saying this is great, just sharing how to do it in case you don't know) [0] https://professor-excel.com/import-csv-text-files-excel/ https://professor-excel.com/import-csv-text-files-excel/ edit: you can also set up a PowerQuery query that will always open some CSV at some path and apply this config, but I don't want to have anything to do with PowerQuery, sorry.
    • unsupp0rted 3y ago
      Wow! I had no idea you could set data format on legacy text columns during import. I had thought the column preview was just that- not a selectable radio button that you can then apply column data formatting to. Thanks for the instructions! It's cumbersome as heck, but it's better than nothing.
      • reportgunner 3y ago
        You can also access (pretty much) the same dialog via Data -> Text to Columns but you need to have some data already pasted in Excel.
• bazoom42 3y ago
  Here is a crazy idea: So csv itself is abiguous, but as a convention we could encode the options in the file name. E.g data.uchq.csv means utf8, comma-separated, with header, quoted.
  • deleted 3y ago
    [deleted]
• ratherbefuddled 3y ago
  Ubiquity has a quality all of its own. Yes CSV is a pain in many regards, but many of the difficulties with it arise from the fact that anybody can produce it with very little tool support - which is also the reason it is so widely used. Recommending a decidedly niche format as an alternative is not going anywhere.
• jll29 3y ago
  Whether or not you use Parquet is one thing, but CSV will stay because any achival/data exchange format should be human readable.
• divan 3y ago
  Remembering all the cases I needed to export to CSV – 99% are the relatively small datasets, so marginal gains in a few millisecond to import aren't worth sacrificing convenience. And sometimes you just get data from gazzilion of diverse sources, and CSV is the only option available. I suspect that not everybody here work exclusively with huge datasets and well-defined data pipelines. On a practical side, if I want to follow suggestions, how do I export to Avro from Numbers/Excel/Google Sheets?
• kvakerok 3y ago
  Friends also don't allow friends only CSV import of data, but here we are.
• darrmit 3y ago
  I gave up at "You give up human readable files". While I recognize in some cases these recommendations may make sense/CSV may not be ideal, the idea of a CSV _export_ is generally that it could need to be reviewed by a human.
• yipeeeerrrrr 3y ago
  Ingesting data via CSV with Azure Polybase is one of the fastest things I have encountered. +1 for CSV.
• lakomen 3y ago
  Ok, funny guy. Tell that to all the wholesale providers, which use software from 2005 or at least it feels that way. No query params in their single endpoint and only csv exports possible. Then add to that, that shopify, apparently the leader or whatever in shopping software, can't do better than require exactly the format they say, don't you dare coming with configurable fields or mapping. The industry is stuck in the 00s, if not 90s.
• Zababa 3y ago
  It is weird to say both that "CSV files have terrible compression" and then that the proposed format, Apache Parquet, has "Really good compression properties, competitive with .csv.gz". I think what's meant here is that csv compresses really well but you loose the ability to "seek" inside the file.
• julik 3y ago
  "There's a better way" - "just" write your application in Java or Python, import Thrift, zstandard and boost, do some compiling - and presto, you can now export a very complicated file format you didn't really need which you hope your users (who all undoubtedly have Java and Python and Thrift and whatnot) will be able to read. CSV does not deserve the hate.
• snissn 3y ago
  new line seperated json "JSONL"
• osloensis 3y ago
  This is the level of discourse among Norwegian graduates. Half of them are taught to worship low level, the other half has framework diabetes. Don't come here to work if you don't want to drown in nitpicking and meaningless debates like this.
• deleted 3y ago
  [deleted]
• mtr 3y ago
  What's the best way to expose random CSV/.xlsx files for future joins etc? We're house hunting and it would be nice have a local db to keep track of price changes, asking prices, photos, etc. And look up (local) municipal OpenData for an address and grab the lot size, zoning, etc. I'm using Airtable and sometimes Excel, but it would be nice to have a home (hobby) setup for storing queryable data.