Hacker News Reader

Hacker News

Comment by Macha | Hacker News Reader

Parent Full thread

Macha

> 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.ht...) that uses backslash escaping instead.

View on HN

The RFC 4180 says:

5. Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.

The point is your clients and partner firms don't actually care what RFC 4180 says, they just expect you to deal with whatever their CSV library spits out.

4180 is just one of many CSV variants. You can't simply examine a CSV and determine for sure which flavor of file you were given, you can only guess.

For added fun, last column should be 1-2-2021.

Oh that's easy, it's Janreburary Firscond 2021.

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.

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.

Reply on news.ycombinator.com