Is there a problem with this file or a problem with miller?

[seancarmody]

I don't know whether I have stumbled onto a bug or whether there is a problem with the file I am trying to parse. The file is question is stored at ftp://ftp2.bom.gov.au/anon/gen/clim_data/IDCKMSTM0S.csv.gz and is a database of tropical cyclones hosted by the Australian Bureau of Meterology. If I gunzip the file and delete the first four lines and run mlr --icsv check on the resulting file I receive the following error:

mlr: mlr: CSV header/data length mismatch 79 != 1 at filename original_points.csv row 24975.

Looking at that row number there does not seem to be anything wrong with the data (and there is more than one value). Also, R and Excel are all able to process the file without complaint. Any thoughts?

[aborruso]

Hi @seancarmody there are some errors in that CSV.

Two of these:

• a few blank lines;
• Incorrect use of double quotes. If it has them in a cell, you need to apply escape. If you have in a cell lorem "ipsum" sit, a proper CSV must be in this way: "lorem ""ipsum"" sit"

You can correct it in this way:

<IDCKMSTM0S.csv  tail -n +5 | sed  '/^$/d' | mlr --icsvlite --ocsv cat >output.csv

• skip unnecessary rows;
• remove blank rows;
• impose csvlite as input format, which disregards the double-quote escaping rule, and set csv as output to create a fully correct csv, with the correct use of quotation marks

The error message does not help, and perhaps it should be changed. But for that we need to ask @johnkerl

[seancarmody]

I won't hold my breath but I might also suggest to the Bureau that they change their format!

[seancarmody]

Excellent, much appreciated. I thought there must be something malformed somewhere, but was looking for number of entries. This helps a lot!