7 ms·

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’v

by 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"
    - prepend 3y ago
      And how do you fix the “some analyst” problem? Is there a better format that reduces this problem?
      - SAI_Peregrinus 3y ago
        JSON? RON? Protobuf? Cap'n'Proto? Anything with more types than "string" which is all CSV has, and with an unambiguous data encoding. Preferably also a way to transmit the schema, since all of these formats (including CSV) have a schema but don't necessarily include it in the output. About half the problems with CSV are due to encoding ambiguities, the other half are due to schema mismatches.
VMG 3y ago
FTA: * What does missing data look like? The empty string, NaN, 0, 1/1-1970, null, nil, NULL, \0? * What date format will you need to parse? What does 5/5/12 mean? * How multiline data has been written? Does it use quotation marks, properly escape those inside multiline strings, or maybe it just expects you to count the delimiter and by the way can delimiters occur inside bare strings? And let me add my own question here: what is the actual delimiter? Do you support `,`, `;` and `\t`?
- fredguth 3y ago
  What is the encoding of the text file? UTF8, windows-1252? What is the decimal delimiter “.”, “,”? Most csv users don’t even know they have to be aware of all of these differences.
  - SAI_Peregrinus 3y ago
    The main issue is that "CSV" isn't one format with a single schema. It's one format with thousands of schemas and no way to communicate them. Every program picks its own schema for CSVs it produces, some even change the schema depending on various factors (e.g. the presence or absence of a header row). RFC 4180 provides a (mostly) unambiguous format for writing CSVs, but because it discards the (implied) schema it's useless for reading CSVs that come from other programs. RFC 4180 fields have only one type: text string in US-ASCII encoding. There are no dates, no decimal separators, no letters outside the US-ASCII alphabet, you get nothing! It leaves the option for the MIME type to specify a different text encoding, but that's not part of the resulting file so it's only useful when downloading from the internet.
    - averms 3y ago
      > RFC 4180 provides a (mostly) unambiguous format for writing CSVs, What are the ambiguities in RFC 4180?
      - SAI_Peregrinus 3y ago
        It allows non-ASCII text but does not provide any way to indicate charset within the file, instead requiring it out-of-band. Once the file is saved, the text encoding becomes ambiguous. Likewise for the presence or absence of a header row. Likewise for whether double quotes (`"`) are allowed in fields (rule 5). This one gets even worse, since the following rule (6) uses double quotes to escape line breaks and commas, but they may not be allowed at all so commas in fields may not be escapable. It only supports text, not numbers, dates, or any other data, and provides no way to indicate any data type other than text.
Macha 3y ago
> Parsing CSV correctly is not hard, you just can’t use split and be done with it. Parsing RFC-compliant CSVs and telling clients to go away with non-compliant CSVs is not hard. Parsing real world CSVs reliably is simply impossible. The best you can do is heuristics. How do you interpret this row of CSV data? 1,5,The quotation mark "" is used...,2021-1-1 What is the third column? The RFC says that it should just be literally > The quotation mark "" is used... But the reality is that some producers of CSVs, which you will be expected to support, will just blindly apply double quote escaping, and expect you to read: The quotation mark " is used... Or maybe you find a CSV producer in the wild (let's say... Spark: https://spark.apache.org/docs/latest/sql-data-sources-csv.html https://spark.apache.org/docs/latest/sql-data-sources-csv.ht...) that uses backslash escaping instead.
- yetihehe 3y ago
  For added fun, last column should be 1-2-2021.
  - martinflack 3y ago
    Oh that's easy, it's Janreburary Firscond 2021.
  - pezezin 3y ago
    At least in that case you know that the last part is the year. It is much funnier when you encounter something like 3-4-17 and you don't know if it is d/m/y, m/d/y, or y/m/d.
- rietta 3y ago
  The deliminator is a setting that can be changed. I never said hard code. This is an interesting exercise to give students, but I am struggling to look back in my mind through the last 25 years of line of business application development where any of this was intractable. My approach has been to leverage objects. I have a base class for a CsvWriter and CsvReader that does RFC compliant work. I get the business stake holders to provide samples of files they need to import or export. I look at those with my eyes and make sub-classes as needed. And data type influencing is a fun side project. I worked for a while on a fully generic CSV to SQL converter. Basically you end up with regex matches for different formats and you keep a running tally of errors encountered and then do a best fit for the column. Using a single CSV file is consistent with itself for weird formatting induced by whatever process the other side used. It actually worked really well, even on multi gigabyte CSV files from medical companies that one of my clients had to analyze with a standard set of SQL reports.
barrkel 3y ago
CSV is not well-defined. Data in the wild doesn't even agree that it's comma separated. String encoding? Dates? Formatted numbers? Booleans (T/F/Y/N/etc)? Nested quotes? Nested CSV!? How about intermediate systems that muck things up. String encoding going through a pipeline with a misconfiguration in the middle. Data with US dates pasted into UK Excel and converted back into CSV, so that the data is a mix of m/d/yy and d/m/yy depending on the magnitude of the numbers. Hand-munging of data in Excel generally, so that sometimes the data is misaligned WRT rows and columns. I've seen things in CSV. I once wrote an expression language to help configure custom CSV import pipelines, because you'd need to iterate a predicate over the data to figure out which columns are which (the misalignment problem above).
- wruza 3y ago
  Intermediate systems like Excel will break anything, they aren’t constrained to CSV. Excel screws up at the level of a cell value, not at the file format.
- joncrocks 3y ago
  Indeed. It's like 'text file' - there are many ways to encode and decode these. Add another munging the to list, ids that 'look like numbers' e.g. `0002345` will get converted to `2,345`. Better be sure to pre-pend ' i.e. `'0002345`
- barrkel 3y ago
  On the topic of nested CSV, three approaches: - treat it as a join, and unroll by duplicating non-nested CSV data in separate rows for every element in the nested CSV - treat it as a projection, have an extraction operator to project the cells you want - treat it as text substitution problem; I've seen CSV files where every line of CSV was quoted like it was a single cell in a larger CSV row You get nested CSV because upstream systems are often master/detail or XML but need to use CSV because everybody understands CSV because it's such a simple file format. Good stuff.
- fragmede 3y ago
  Yeah but we're not in the dark ages of computers anymore. Export to Sqlite database instead.
himinlomax 3y ago
> well defined format No.
cm2187 3y ago
One example that will kill loading a csv in excel beyond the usual dates problem. If you open in excel a csv file that has some large id stored as int64, they will be converted to an excel number (I suspect a double) and rounded. Also if you have a text column but where some of the codes are numeric with leading zeros, the leading zeros will be lost. And NULL is treated as the string "NULL". I am aware you can import a csv file in excel by manually defining the column types but few people use that. I'd be fine with an extension of the csv format with one extra top row to define the type of each column.
- JumpCrisscross 3y ago
  > aware you can import a csv file in excel by manually defining the column types but few people use that And what fraction of those users would be able to anything with another format? > an extension of the csv format with one extra top row to define the type of each column If the goal is foolproof export to Excel, use XLSX.
frizlab 3y ago
Excel will literally use a different value separator depending on the locale of the machine (if the decimal separator for numbers is a comma and not a dot, it’ll use a semicolon as a value separator instead of a comma).
orwin 3y ago
I will hard disagree here. Always have has a clrf issue or another weirdness come up. Especially if you work with teams from different countries, csv is hell. I always generate rfc compliant csv, not once it was accepted from day one. Once, it took us two weeks to make it pass the ingestion process (we didn't have access to the ingest logs and had to request them each day, after the midnight processing) so in the end, it was only 10 different tries, but still. I had once an issue with json (well, not one created by me) , and it was clearly a documentation mistake. And I hate json (I'm an XML proponent usually). Csv is terrible.
alserio 3y ago
Excel and data precision are really at odds. But it might be interesting to see what would happen if excel shipped with parquet import and export capabilities
rietta 3y ago
Y'all caught me being less than rigorous in my language while posting from my phone while in the middle of making breakfast for the kids and the wife to get out the door. To the "not well defined" aspect, I disagree in part. There is an RFC for CSV and that is defined. Now the conflated part is the use of CSV as an interchange format between various systems and locales. The complexity of data exchange, in my mind, is not the fault of the simple CSV format. Nor is it the fault of the format that third party systems have decided to not implement it as it is defined. That is a different challenge. Data exchange via CSV becomes a multiple party negotiation between software (sometimes the other side is "set in stone" such as a third party commercial system or Excel that is entrenched), users, and users' local configuration. However, while not well defined none of these are intractable from a software engineering point of view. And if your system is valuable enough and needs to ingest data from enough places it is NOT intractable to build auto detection capabilities for all the edge cases. I have done it. Is it sometimes a challenge, yes. Do project managers love it when you give 8 point estimates for what seems like it should be a simple thing, no. That is why the coding kata RFC compliance solution as a base first makes the most sense and then you troubleshoot as bug reports come in about data problems with various files. If you are writing a commercial, enterprise solution that needs to get it right on its own every time, then that becomes a much bigger project. But do you know what is impossible, getting the entire universe of other systems that your customers are using to support some new interchange format. Sorry, that system is no longer under active development. No one is around to make code changes. That is not compatible with the internal tools. For better or worse, CSV is the lingua franca of columns of data. As developers, we deal with it. And yes, do support Excel XLSX format if you can. There are multiple third party libraries to do this in various languages of various quality. As a developer, I have made a lot of my living dealing with this stuff. It can be a fun challenge, frustrating at time, but in the end as professionals we solve what we need to to get the job done.
ravenstine 3y ago
> Parsing CSV correctly is not hard, you just can’t use split and be done with it. And yet you can anyway if you are confident that your CSV won't contain anything that would mess it up.
- delfinom 3y ago
  CSV is so simple that if someone forgets to send you escaped CSV, you can and should simply smack them with a shoe.
mr_toad 3y ago
> Parsing CSV correctly is not hard Parsing the CSV you have in front of you is not hard (usually). Writing a parser that will work for all forms of CSV you might encounter is significantly harder.
kalleboo 3y ago
> Honestly CSV is a very simple, well defined format, that is decades old and is “obvious” This is the problem though. Everything thinks it is "obvious" and does their own broken implementation where they just concatenate values together with commas, and then outsources dealing with the garbage to whoever ends up with the file on their plate. If a more complex, non-obvious format was required, instead of "easy I'll just concatenate values" they might actually decide to put engineering into it (or use a library)

by rietta 3y ago

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"
  - prepend 3y ago
    And how do you fix the “some analyst” problem? Is there a better format that reduces this problem?
    - SAI_Peregrinus 3y ago
      JSON? RON? Protobuf? Cap'n'Proto? Anything with more types than "string" which is all CSV has, and with an unambiguous data encoding. Preferably also a way to transmit the schema, since all of these formats (including CSV) have a schema but don't necessarily include it in the output. About half the problems with CSV are due to encoding ambiguities, the other half are due to schema mismatches.

VMG 3y ago

FTA: * What does missing data look like? The empty string, NaN, 0, 1/1-1970, null, nil, NULL, \0? * What date format will you need to parse? What does 5/5/12 mean? * How multiline data has been written? Does it use quotation marks, properly escape those inside multiline strings, or maybe it just expects you to count the delimiter and by the way can delimiters occur inside bare strings? And let me add my own question here: what is the actual delimiter? Do you support `,`, `;` and `\t`?

fredguth 3y ago
What is the encoding of the text file? UTF8, windows-1252? What is the decimal delimiter “.”, “,”? Most csv users don’t even know they have to be aware of all of these differences.
- SAI_Peregrinus 3y ago
  The main issue is that "CSV" isn't one format with a single schema. It's one format with thousands of schemas and no way to communicate them. Every program picks its own schema for CSVs it produces, some even change the schema depending on various factors (e.g. the presence or absence of a header row). RFC 4180 provides a (mostly) unambiguous format for writing CSVs, but because it discards the (implied) schema it's useless for reading CSVs that come from other programs. RFC 4180 fields have only one type: text string in US-ASCII encoding. There are no dates, no decimal separators, no letters outside the US-ASCII alphabet, you get nothing! It leaves the option for the MIME type to specify a different text encoding, but that's not part of the resulting file so it's only useful when downloading from the internet.
  - averms 3y ago
    > RFC 4180 provides a (mostly) unambiguous format for writing CSVs, What are the ambiguities in RFC 4180?
    - SAI_Peregrinus 3y ago
      It allows non-ASCII text but does not provide any way to indicate charset within the file, instead requiring it out-of-band. Once the file is saved, the text encoding becomes ambiguous. Likewise for the presence or absence of a header row. Likewise for whether double quotes (`"`) are allowed in fields (rule 5). This one gets even worse, since the following rule (6) uses double quotes to escape line breaks and commas, but they may not be allowed at all so commas in fields may not be escapable. It only supports text, not numbers, dates, or any other data, and provides no way to indicate any data type other than text.

Macha 3y ago

> Parsing CSV correctly is not hard, you just can’t use split and be done with it. Parsing RFC-compliant CSVs and telling clients to go away with non-compliant CSVs is not hard. Parsing real world CSVs reliably is simply impossible. The best you can do is heuristics. How do you interpret this row of CSV data? 1,5,The quotation mark "" is used...,2021-1-1 What is the third column? The RFC says that it should just be literally > The quotation mark "" is used... But the reality is that some producers of CSVs, which you will be expected to support, will just blindly apply double quote escaping, and expect you to read: The quotation mark " is used... Or maybe you find a CSV producer in the wild (let's say... Spark: https://spark.apache.org/docs/latest/sql-data-sources-csv.html https://spark.apache.org/docs/latest/sql-data-sources-csv.ht...) that uses backslash escaping instead.

yetihehe 3y ago
For added fun, last column should be 1-2-2021.
- martinflack 3y ago
  Oh that's easy, it's Janreburary Firscond 2021.
- pezezin 3y ago
  At least in that case you know that the last part is the year. It is much funnier when you encounter something like 3-4-17 and you don't know if it is d/m/y, m/d/y, or y/m/d.
rietta 3y ago
The deliminator is a setting that can be changed. I never said hard code. This is an interesting exercise to give students, but I am struggling to look back in my mind through the last 25 years of line of business application development where any of this was intractable. My approach has been to leverage objects. I have a base class for a CsvWriter and CsvReader that does RFC compliant work. I get the business stake holders to provide samples of files they need to import or export. I look at those with my eyes and make sub-classes as needed. And data type influencing is a fun side project. I worked for a while on a fully generic CSV to SQL converter. Basically you end up with regex matches for different formats and you keep a running tally of errors encountered and then do a best fit for the column. Using a single CSV file is consistent with itself for weird formatting induced by whatever process the other side used. It actually worked really well, even on multi gigabyte CSV files from medical companies that one of my clients had to analyze with a standard set of SQL reports.

barrkel 3y ago

CSV is not well-defined. Data in the wild doesn't even agree that it's comma separated. String encoding? Dates? Formatted numbers? Booleans (T/F/Y/N/etc)? Nested quotes? Nested CSV!? How about intermediate systems that muck things up. String encoding going through a pipeline with a misconfiguration in the middle. Data with US dates pasted into UK Excel and converted back into CSV, so that the data is a mix of m/d/yy and d/m/yy depending on the magnitude of the numbers. Hand-munging of data in Excel generally, so that sometimes the data is misaligned WRT rows and columns. I've seen things in CSV. I once wrote an expression language to help configure custom CSV import pipelines, because you'd need to iterate a predicate over the data to figure out which columns are which (the misalignment problem above).

wruza 3y ago
Intermediate systems like Excel will break anything, they aren’t constrained to CSV. Excel screws up at the level of a cell value, not at the file format.
joncrocks 3y ago
Indeed. It's like 'text file' - there are many ways to encode and decode these. Add another munging the to list, ids that 'look like numbers' e.g. `0002345` will get converted to `2,345`. Better be sure to pre-pend ' i.e. `'0002345`
barrkel 3y ago
On the topic of nested CSV, three approaches: - treat it as a join, and unroll by duplicating non-nested CSV data in separate rows for every element in the nested CSV - treat it as a projection, have an extraction operator to project the cells you want - treat it as text substitution problem; I've seen CSV files where every line of CSV was quoted like it was a single cell in a larger CSV row You get nested CSV because upstream systems are often master/detail or XML but need to use CSV because everybody understands CSV because it's such a simple file format. Good stuff.
fragmede 3y ago
Yeah but we're not in the dark ages of computers anymore. Export to Sqlite database instead.

himinlomax 3y ago

> well defined format No.

cm2187 3y ago

One example that will kill loading a csv in excel beyond the usual dates problem. If you open in excel a csv file that has some large id stored as int64, they will be converted to an excel number (I suspect a double) and rounded. Also if you have a text column but where some of the codes are numeric with leading zeros, the leading zeros will be lost. And NULL is treated as the string "NULL". I am aware you can import a csv file in excel by manually defining the column types but few people use that. I'd be fine with an extension of the csv format with one extra top row to define the type of each column.

JumpCrisscross 3y ago
> aware you can import a csv file in excel by manually defining the column types but few people use that And what fraction of those users would be able to anything with another format? > an extension of the csv format with one extra top row to define the type of each column If the goal is foolproof export to Excel, use XLSX.

frizlab 3y ago

Excel will literally use a different value separator depending on the locale of the machine (if the decimal separator for numbers is a comma and not a dot, it’ll use a semicolon as a value separator instead of a comma).

orwin 3y ago

I will hard disagree here. Always have has a clrf issue or another weirdness come up. Especially if you work with teams from different countries, csv is hell. I always generate rfc compliant csv, not once it was accepted from day one. Once, it took us two weeks to make it pass the ingestion process (we didn't have access to the ingest logs and had to request them each day, after the midnight processing) so in the end, it was only 10 different tries, but still. I had once an issue with json (well, not one created by me) , and it was clearly a documentation mistake. And I hate json (I'm an XML proponent usually). Csv is terrible.

alserio 3y ago

Excel and data precision are really at odds. But it might be interesting to see what would happen if excel shipped with parquet import and export capabilities

rietta 3y ago

Y'all caught me being less than rigorous in my language while posting from my phone while in the middle of making breakfast for the kids and the wife to get out the door. To the "not well defined" aspect, I disagree in part. There is an RFC for CSV and that is defined. Now the conflated part is the use of CSV as an interchange format between various systems and locales. The complexity of data exchange, in my mind, is not the fault of the simple CSV format. Nor is it the fault of the format that third party systems have decided to not implement it as it is defined. That is a different challenge. Data exchange via CSV becomes a multiple party negotiation between software (sometimes the other side is "set in stone" such as a third party commercial system or Excel that is entrenched), users, and users' local configuration. However, while not well defined none of these are intractable from a software engineering point of view. And if your system is valuable enough and needs to ingest data from enough places it is NOT intractable to build auto detection capabilities for all the edge cases. I have done it. Is it sometimes a challenge, yes. Do project managers love it when you give 8 point estimates for what seems like it should be a simple thing, no. That is why the coding kata RFC compliance solution as a base first makes the most sense and then you troubleshoot as bug reports come in about data problems with various files. If you are writing a commercial, enterprise solution that needs to get it right on its own every time, then that becomes a much bigger project. But do you know what is impossible, getting the entire universe of other systems that your customers are using to support some new interchange format. Sorry, that system is no longer under active development. No one is around to make code changes. That is not compatible with the internal tools. For better or worse, CSV is the lingua franca of columns of data. As developers, we deal with it. And yes, do support Excel XLSX format if you can. There are multiple third party libraries to do this in various languages of various quality. As a developer, I have made a lot of my living dealing with this stuff. It can be a fun challenge, frustrating at time, but in the end as professionals we solve what we need to to get the job done.

ravenstine 3y ago

> Parsing CSV correctly is not hard, you just can’t use split and be done with it. And yet you can anyway if you are confident that your CSV won't contain anything that would mess it up.

delfinom 3y ago
CSV is so simple that if someone forgets to send you escaped CSV, you can and should simply smack them with a shoe.

mr_toad 3y ago

> Parsing CSV correctly is not hard Parsing the CSV you have in front of you is not hard (usually). Writing a parser that will work for all forms of CSV you might encounter is significantly harder.

kalleboo 3y ago

> Honestly CSV is a very simple, well defined format, that is decades old and is “obvious” This is the problem though. Everything thinks it is "obvious" and does their own broken implementation where they just concatenate values together with commas, and then outsources dealing with the garbage to whoever ends up with the file on their plate. If a more complex, non-obvious format was required, instead of "easy I'll just concatenate values" they might actually decide to put engineering into it (or use a library)