5 ms·

CSV's biggest competitor is XML. But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic

CSV's biggest competitor is XML. But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic micro-database engine and a file format, it isn't a very good universal B2B format because there are too many features you'd have to support to interoperate. I'd argue CSV's biggest strength is that it is easy to parse correctly, because the format is so simple and the standard is largely the superset. The biggest problem for CSV is that the most popular desktop application for view CSV (Microsoft Excel) sucks at it, and causes data corruption on save (and has for at least twenty years). If people wanted a relational database as a file, I can think of nothing better than SQLite, but in B2B you want automated tooling that can parse the format without human involvement. If you need some structure there are some simpler XML-based formats which accommodate that, but still offer far fewer features you'd have to support than SQLite does. Ajax might become popular in this space, but I'm yet to see it.

debaserab2 9y ago
I'd actually argue that CSV's biggest competitor specifically is xlsx. Which is kind of XML...
- Someone1234 9y ago
  I'm talking about business to business transactions, as in automated transactions on VANs and similar data interchanges. The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries which require Windows licenses, and the two formats have a huge amount of overhead compared to XML formats and CSV (which costs money on VANs). This is a space where plain text files and binary is still king. Formats like EDI are still massive. In many ways CSV and XML are the new kids on the block. Only end users are sending Excel formatted files (and PDFs) to one another.
  - merb 9y ago
    > The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries XLS yes, however XLSX is just easy to parse if you document only contains simple types. of course with embedded mathml/wordml it gets a little bit wierd.
- wenc 9y ago
  XLSX is actually a decent format for business users. The only trouble is it has tons of limits [1], most restrictive being the limit of 1,048,576 rows by 16,384 columns. I'm working with a client who had to split up their data into several XLSX files because they hit a row limit. They can of course ETL the data into a real SQL database, but ETL tools for end users are still not a common thing plus not all end users have access to a SQL database. [1] https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 https://support.office.com/en-us/article/excel-specification...
wenc 9y ago
> I'd argue CSV's biggest strength is that it is easy to parse correctly Not sure I resonate with that. I work with large CSVs of varying provenance every day (I work in big data) and there's always some CSV edge case that stymies my analysis pipeline. Timestamp parsing is extremely hard if it's not ISO-8601, as well as handling of unicode encoding, missing data, type inference, European usage of , as a decimal point, hidden characters, etc. One of the costs of almost complete freedom in input is the "interesting" possibilities people come up with to stymie your code. The Pandas read_csv method has tons of switches to deal with all kinds of CSV parsing definitions precisely because there is no standard. Fortunately this covers 80% of the use-cases. https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html https://pandas.pydata.org/pandas-docs/stable/generated/panda... Excel's CSV parsing isn't the greatest, but does a surprisingly decent job considering how ill-defined CSV is. XML isn't really on anyone's radar in the big data world. It's an interchange format yes but is hugely inefficient for dataframe-type data.
- Someone1234 9y ago
  > Excel's CSV parsing isn't the greatest, but does a surprisingly decent job considering how ill-defined CSV is. No, it really doesn't. Save this as a CSV, open it in Excel, hit save, and then review the raw CSV: "1000000000012345", "Hello", "2000000000067890", "World", Here's what Excel (O365) does to it for me: 1E+15, Hello, 2E+15, World, That data is now permanently lost. And this corruption occurs for almost all EAN/UPCs. There are many ways to get around this in Excel, but the default experience is data corruption and has been for most of my lifetime. It is a terrible application for CSV. If they followed the standard every column would be what it is: Text. > XML isn't really on anyone's radar in the big data world. But is a central theme in the business to business back-end systems world. I'm talking about ordering, invoicing, remittances, hospital records, hospital billing, and so on. No clue what "big data" is, we deal with billions of transactions a year, but that's likely not what you're referring to.
  - wenc 9y ago
    1. I think in the first instance, Excel performs type-inference and coerces it into a numeric type and writes a truncated precision version of numeric fields in CSV, which is an abominable sin. There are workarounds for this [1], but agree this is terrible behavior due to CSV's lack of types. Arguably, there should have been an explicit Excel switch which forces all CSV data to be parsed as raw strings. I've seen problems with Excel CSVs that are worse than that: I have serial numbers that have leading 0's that have semantic meaning, like 000002324122323. Most CSV parsers cannot tell that this isn't a numeric type and so handle it wrongly. So I resort to [1]. 2. Big data is of course somewhat ambiguous nomenclature as well, but is nowadays typically understood to mean the Hadoop ecosystem or similar. Data is typically ingested into a distributed file system (HDFS, S3, etc.) in formats such as Parquet, Avro, JSON and often CSV. A schema-on-read database like Hive sits on top of this layer and presents a SQL interface to the user. Tools like Apache Spark provide programmatic transformations that operate on the data on the large. CSV is often promoted as a format for storing structured data due to ease of ingestion and inspection (all you need is a text editor for troubleshooting). However, you pay a performance penalty every time an analytic query is run because CSV doesn't support indexes, predicate pushdowns, compression, etc. and records can sometimes be uninterpretable under certain schemas (in which case they are simply excluded or dropped). Sometimes having one too many commas in a row can completely mess things up (all the columns get shifted in a record) -- I found this out the hard way when Spark exported malformed CSVs with un-escaped commas. [1] http://support.pitneybowes.com/SearchArticles/VFP06_KnowledgeWithSidebarTroubleshoot?id=kA280000000TTCfCAO&popup=false&lang=en_US http://support.pitneybowes.com/SearchArticles/VFP06_Knowledg...
BjoernKW 9y ago
> that it is easy to parse correctly While that's not exactly correct - CSV can be quite hard to parse if you want to cover each and every variation and edge case - for all practical intents and purposes that statement is true in my opinion. CSV is something of a lowest common denominator, a compromise between well-defined, structured data and portability / accessibility.
- libx 9y ago
  CSV could be much easier to parse if people use the ASCII codes meant to be used as field and line separators, thus avoiding having problems with commas. See https://ronaldduncan.wordpress.com/2009/10/31/text-file-formats-ascii-delimited-text-not-csv-or-tab-delimited-text/ https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...
infogulch 9y ago
> CSV's biggest strength is that it is easy to parse correctly (goes on to say a bunch of applications don't parse it correctly; lets add mssql to the pile)
- Someone1234 9y ago
  Please review the comment rules here: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
chaz6 9y ago
Just as a point of interest, sqlite is behind the geopackage [1] specification, which is a common format for exchanging geospatial data. [1] https://www.geopackage.org/ https://www.geopackage.org/

debaserab2 9y ago

I'd actually argue that CSV's biggest competitor specifically is xlsx. Which is kind of XML...

Someone1234 9y ago
I'm talking about business to business transactions, as in automated transactions on VANs and similar data interchanges. The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries which require Windows licenses, and the two formats have a huge amount of overhead compared to XML formats and CSV (which costs money on VANs). This is a space where plain text files and binary is still king. Formats like EDI are still massive. In many ways CSV and XML are the new kids on the block. Only end users are sending Excel formatted files (and PDFs) to one another.
- merb 9y ago
  > The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries XLS yes, however XLSX is just easy to parse if you document only contains simple types. of course with embedded mathml/wordml it gets a little bit wierd.
wenc 9y ago
XLSX is actually a decent format for business users. The only trouble is it has tons of limits [1], most restrictive being the limit of 1,048,576 rows by 16,384 columns. I'm working with a client who had to split up their data into several XLSX files because they hit a row limit. They can of course ETL the data into a real SQL database, but ETL tools for end users are still not a common thing plus not all end users have access to a SQL database. [1] https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 https://support.office.com/en-us/article/excel-specification...

wenc 9y ago

> I'd argue CSV's biggest strength is that it is easy to parse correctly Not sure I resonate with that. I work with large CSVs of varying provenance every day (I work in big data) and there's always some CSV edge case that stymies my analysis pipeline. Timestamp parsing is extremely hard if it's not ISO-8601, as well as handling of unicode encoding, missing data, type inference, European usage of , as a decimal point, hidden characters, etc. One of the costs of almost complete freedom in input is the "interesting" possibilities people come up with to stymie your code. The Pandas read_csv method has tons of switches to deal with all kinds of CSV parsing definitions precisely because there is no standard. Fortunately this covers 80% of the use-cases. https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html https://pandas.pydata.org/pandas-docs/stable/generated/panda... Excel's CSV parsing isn't the greatest, but does a surprisingly decent job considering how ill-defined CSV is. XML isn't really on anyone's radar in the big data world. It's an interchange format yes but is hugely inefficient for dataframe-type data.

Someone1234 9y ago
> Excel's CSV parsing isn't the greatest, but does a surprisingly decent job considering how ill-defined CSV is. No, it really doesn't. Save this as a CSV, open it in Excel, hit save, and then review the raw CSV: "1000000000012345", "Hello", "2000000000067890", "World", Here's what Excel (O365) does to it for me: 1E+15, Hello, 2E+15, World, That data is now permanently lost. And this corruption occurs for almost all EAN/UPCs. There are many ways to get around this in Excel, but the default experience is data corruption and has been for most of my lifetime. It is a terrible application for CSV. If they followed the standard every column would be what it is: Text. > XML isn't really on anyone's radar in the big data world. But is a central theme in the business to business back-end systems world. I'm talking about ordering, invoicing, remittances, hospital records, hospital billing, and so on. No clue what "big data" is, we deal with billions of transactions a year, but that's likely not what you're referring to.
- wenc 9y ago
  1. I think in the first instance, Excel performs type-inference and coerces it into a numeric type and writes a truncated precision version of numeric fields in CSV, which is an abominable sin. There are workarounds for this [1], but agree this is terrible behavior due to CSV's lack of types. Arguably, there should have been an explicit Excel switch which forces all CSV data to be parsed as raw strings. I've seen problems with Excel CSVs that are worse than that: I have serial numbers that have leading 0's that have semantic meaning, like 000002324122323. Most CSV parsers cannot tell that this isn't a numeric type and so handle it wrongly. So I resort to [1]. 2. Big data is of course somewhat ambiguous nomenclature as well, but is nowadays typically understood to mean the Hadoop ecosystem or similar. Data is typically ingested into a distributed file system (HDFS, S3, etc.) in formats such as Parquet, Avro, JSON and often CSV. A schema-on-read database like Hive sits on top of this layer and presents a SQL interface to the user. Tools like Apache Spark provide programmatic transformations that operate on the data on the large. CSV is often promoted as a format for storing structured data due to ease of ingestion and inspection (all you need is a text editor for troubleshooting). However, you pay a performance penalty every time an analytic query is run because CSV doesn't support indexes, predicate pushdowns, compression, etc. and records can sometimes be uninterpretable under certain schemas (in which case they are simply excluded or dropped). Sometimes having one too many commas in a row can completely mess things up (all the columns get shifted in a record) -- I found this out the hard way when Spark exported malformed CSVs with un-escaped commas. [1] http://support.pitneybowes.com/SearchArticles/VFP06_KnowledgeWithSidebarTroubleshoot?id=kA280000000TTCfCAO&popup=false&lang=en_US http://support.pitneybowes.com/SearchArticles/VFP06_Knowledg...

BjoernKW 9y ago

> that it is easy to parse correctly While that's not exactly correct - CSV can be quite hard to parse if you want to cover each and every variation and edge case - for all practical intents and purposes that statement is true in my opinion. CSV is something of a lowest common denominator, a compromise between well-defined, structured data and portability / accessibility.

libx 9y ago
CSV could be much easier to parse if people use the ASCII codes meant to be used as field and line separators, thus avoiding having problems with commas. See https://ronaldduncan.wordpress.com/2009/10/31/text-file-formats-ascii-delimited-text-not-csv-or-tab-delimited-text/ https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...

infogulch 9y ago

> CSV's biggest strength is that it is easy to parse correctly (goes on to say a bunch of applications don't parse it correctly; lets add mssql to the pile)

Someone1234 9y ago
Please review the comment rules here: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html

chaz6 9y ago

Just as a point of interest, sqlite is behind the geopackage [1] specification, which is a common format for exchanging geospatial data. [1] https://www.geopackage.org/ https://www.geopackage.org/