Field separators inside double quotes create a new field

[stn-ptr]

Using this file:

a;b
"text; text";"more text"

I'm getting an unexpected result:

mlr --inidx --fs semicolon cat doublequote.csv
1=a;2=b
1="text;2= text";3="more text"

I'd expect the semicolon inside the double quotes in the first field not to be used as a field separator. This seems to be an issue with using the --inidx and --fs options, as using --icsv or using commas in the file and omitting the --fs option works fine.

[aborruso]

The nidx format I think it has only field separator constrain.

If you want to deal with double quotes and field separators inside a cell, I think you must use something like CSV.

mlr --icsv --ifs ";" -N cat input

to have

1=a,2=b
1=text; text,2=more text

[stn-ptr]

Hello @aborruso,

thank you for the fast reply. I was not using --icsv because in my actual file I'm cleaning up I have some empty headers. Since I can't publish the actual data I tried to create a simple sample. So it seems I need to fix that other issue first.

I my opinion it would still be nice to be able to use delimiters in the nidx format, though. :)

[aborruso]

hello @stn-ptr you could have empty headers, using implicit header.

Starting in example from

a,b,,d,
1,3,6,7,8
4,3,5,7,8

You can use Miller in this way mlr --csv --implicit-csv-header cat input and have

1,2,3,4,5
a,b,,d,
1,3,6,7,8
4,3,5,7,8

If you want to apply a filter (records in which the first field == 4), and remove the implicit header you can run mlr --csv -N filter '$1==4 || $1=="a"' input to obtain

a,b,,d,
4,3,5,7,8

-N is for --implicit-csv-header --headerless-csv-output, then you can use the implicit field and then remove it (here the doc).

If you insert an example input and an example output, I could write a better solution

[johnkerl]

This could be an FAQ entry ... @aborruso is correct, but this is confusing. :^/

[stn-ptr]

mlr --csv -N does the trick for me, thank you a lot.

Just for completeness, here's a more realistic example using the actual locale of the data I'm working with. It is an Excel export and I am preparing the data to be imported into a database.

Datum;Seriennummer;Seriennummer A;Seriennummer B;LosNr;Offset;Bemerkungen;;;;;
09.04.2020;ab345;N/A;487;735;1,5;;;;;;
10.04.2020;ab346;S5673;N/A;732;1,25;"Kommentar; Weiterer Kommentar";;;;;
10.04.2020;ab347;N/A;479;735;1,5;Bemerkung;;;;;Text
11.04.2020;ab344;S5674;N/A;735;0,75;Eine Anmerkung;;;;;
;;;;;;;;;;;
;;;;;;;;;;;
;;;;;;;;;;;
;;;;;;;;;;;

This is what the result should look like:

Datum;Seriennummer;Seriennummer A;Seriennummer B;LosNr;Offset;Bemerkungen
09.04.2020;ab345;N/A;487;735;1,5;
10.04.2020;ab346;S5673;N/A;732;1,25;"Kommentar; Weiterer Kommentar"
10.04.2020;ab347;N/A;479;735;1,5;Bemerkung
11.04.2020;ab344;S5674;N/A;735;0,75;Eine Anmerkung
;;;;;;
;;;;;;
;;;;;;
;;;;;;

Also, there is a couple of different files which use different columns, that's why I'm hesitant to use a hard coded column number. To be able to use the field names I was thinking I'd remove the headerless columns first, that's when I noticed. So I came up with a script to detect empty headers and to unset the column in every record. The script works with mlr -inidx as well as with mlr --csv -N.

[aborruso]

@stn-ptr I'm happy :)

If you want to remove the rows without any data (you have them in your example), you can use skip-trivial-records verb and run something like

mlr -I --csv skip-trivial-records file.csv

Pay attention, using -I you will overwrite the source file.

If this issue is ok for you, please close it.

[stn-ptr]

This one is ok for me, I wasn't sure if I should close it because of the needs-documentation label.

And thanks again for the tip, that's the next issue I was going to resolve.

[johnkerl]

Hi folks -- I'll leave this open pending my writing up an FAQ entry for it.

Also, some of these things will get less confusing in Miller 6 (the Go port) as there will be a double-quote-respecting parser for DKVP certainly, and NIDX probably.

[aborruso]

Also, some of these things will get less confusing in Miller 6 (the Go port) as there will be a double-quote-respecting parser for DKVP certainly, and NIDX probably.

@johnkerl what I read about Miller 6 is more and more bombastic. When do you plan to release it?

[johnkerl]

Hi @aborruso ! Well, I don't know.

• I've been working on Miller about 5 years now, on and off, far more "off" than on. Nonetheless re-writing it is not a quick endeavor.
• However it's a lot quicker the second time around, as I know what I'm doing.
• Another thing making it quicker is that Go is more high-level than C so it takes more lines of code.
• I have a few weeks off work at present which helps -- I'm putting a couple solid weeks into Miller right now, which won't last.

So ... I don't know. It won't be a day or a week, nor probably a month. I don't want to promise anything timewise. But this is very much fun, and will make Miller easier for users to use, and much easier for developers, so I'll keep up the momentum.

[aborruso]

Hi @johnkerl , but if I would like to test this development gorgeous version of Miller, is there a way to do it?

[johnkerl]

It doesn't do much yet :) -- very much partway built.