2

Given a file with newlines in fields (embedded by double quotes), I tried to use NUL as record separator and then select desired records. For this I have replaced the ends of lines with NUL and then corrected for fields split by a newline (done using sed). However then exactly matching the first field in (GNU) awk with a string fails. Interestingly a string pattern match on the first field fails, which makes me assume that RS="\x00" is correctly applied.

Why would it fail? Why does the pattern match work?

Example file input.txt:

head1,head2,head3
a,b,c
b,no a in first field,c
a,"with quotes",c
a,"with ,",c
b,a,1
a,"with
 newline",c
b,1,a

Record selection via awk with exact string before introducing NUL works:

$awk 'BEGIN {FS=OFS=","} {if ($1=="a") print}' input.txt

Result:

a,b,c
a,"with quotes",c
a,"with ,",c
a,"with

Introducing NUL and correcting "newline-splits" works (note the "with\n newline" entry):

$sed -e 's/$/\x00/' -e 's/\(,"[^,"]*\)\x00/\1/' input.txt | cat -A

head1,head2,head3^@$
a,b,c^@$
b,no a in first field,c^@$
a,"with quotes",c^@$
a,"with ,",c^@$
b,a,1^@$
a,"with$
 newline",c^@$
b,1,a^@$

Using a pattern match for a in field 1 works (note how "a" in other fields fails, but "head1" matches):

$sed -e 's/$/\x00/' -e 's/\(,"[^,"]*\)\x00/\1/' input.txt |
awk 'BEGIN {RS=ORS="\x00" ; FS=OFS=","}
     { if ($1~"a") print}' |
cat -A

head1,head2,head3^@$
a,b,c^@$
a,"with quotes",c^@$
a,"with ,",c^@$
a,"with$
 newline",c^@

HOWEVER: the exact match for "a" in field 1 fails:

sed -e 's/$/\x00/' -e 's/\(,"[^,"]*\)\x00/\1/' input.txt |
awk 'BEGIN {RS=ORS="\x00" ; FS=OFS=","} { if ($1=="a") print}' 

##<no output>##

Where am I wrong? Why does is work before using NUL as RS?

  • You never need sed when you're using awk and your input probably has \n (LF) within fields but \r\n (CR LF) at the end of records (e.g. if it was exported from MS-Excel) and so you could just set RS="\r\n" with GNU awk and be done. Run cat -v file to see if there's ^M (representing \r aka CR) at the end of each record. See also whats-the-most-robust-way-to-efficiently-parse-csv-using-awk – Ed Morton Sep 23 at 15:57
  • @EdMorton I player with the FPAT before asking (even read your exact SO topic), however I could not make it work with newlines - thus the "standards" idea of NUL. But yes, the match must be $1=="\na", I overlooked this. – Fiximan Sep 23 at 16:09
  • @Fixman my comment was about using RS="\r\n" if the records in your file do end with CRLF as they would if it was a CSV exported from Excel, I didn't mention $1=="\na". Using NUL is by no means a standards idea. Per POSIX any file that contains NUL chars is not a valid text file. Some awks will be able to handle it as you like but others won't. – Ed Morton Sep 23 at 16:30
1

Your sed command is not changing newlines (\n) to NULs (\0) but to NULs + newlines (\0\n) (as cat -A shows).

When using GNU awk with RS set to \0, the first character of a subsequent record (and of its first field) will be \n, which will break your exact match.

And the 's/\(,"[^,"]*\)\x00/\1/' newline-splits correction doesn't change that at all -- it just appends the newline",c record to the previous one.


A quick and dirty "solution" is to set RS to \0\n instead of just \0. But that way of massaging csv files so that they can be parsed by awk is not reliable, so you should REALLY find something better.

With your last example:

sed -e 's/$/\x00/' -e 's/\(,"[^,"]*\)\x00/\1/' input.txt |
gawk 'BEGIN {RS=ORS="\x00\n" ; FS=OFS=","} { if ($1=="a") print}' | cat -A
a,b,c^@$
a,"with quotes",c^@$
a,"with ,",c^@$
a,"with$
 newline",c^@$
sed -e 's/$/\x00/' -e 's/\(,"[^,"]*\)\x00/\1/' input.txt |
gawk 'BEGIN {RS="\x00\n" ; FS=OFS=","} { if ($1=="a") print}'
a,b,c
a,"with quotes",c
a,"with ,",c
a,"with
 newline",c
  • This splits the record with an embedded newline too. You've lost the "undo" second expression from the OP's original sed. Try perl -pe 's#\n#\0#g; s#(,"[^,"]*)\0#$1\n#g', although I've not checked that with multiple embedded newlines – roaima Sep 23 at 14:42
  • @roaima Do you mean the s/\(,"[^,"]*\)\x00/\1/? I fail to see what relevance that has. That only gets rid of an extra \n newline",.. record which wouldn't have matched $1=="a" anyways (a really lousy way to fix csv files in order to be parsed by awk). But maybe it's all because the OP is using "input.tyt" instead of "input.txt" in his/her next-to-last example ;-) – user414777 Sep 23 at 15:00
  • @roaima My point is that $1=="a" doesn't match because $1 is actually \na instead of a, and that "line-splitting" correction doesn't change that. I'll make the answer communition wiki -- feel free to improve it. – user414777 Sep 23 at 15:16
  • you fixed the sed 's/$/\x0/', which changes all newlines to null, but the OP's code then had an unfixer that turned embedded newlines that were now nulls back into newlines. – roaima Sep 23 at 15:36
  • @roaima I'm still not getting your point. This question afaicg is not "how I can parse csv with sed & regexes", to which the answer would be "don't do that". See last change; I've got rid of that perl red-herring. – user414777 Sep 23 at 15:49
2

Your file might contain LFs mid-field with CRLF line endings, e.g. if it was exported from MS-Excel. In that case all you need with gawk is:

awk 'BEGIN{RS=ORS="\r\n"; FPAT="[^,]*|(\"[^\"]*\")+"} $1=="a"' file

For example (using cat -v just to make the CRs visible as ^Ms):

$ cat -v file
head1,head2,head3^M
a,b,c^M
b,no a in first field,c^M
a,"with quotes",c^M
a,"with ,",c^M
b,a,1^M
a,"with
 newline",c^M
b,1,a^M

$ awk 'BEGIN{RS=ORS="\r\n"; FPAT="[^,]*|(\"[^\"]*\")+"} $1=="a"' file | cat -v
a,b,c^M
a,"with quotes",c^M
a,"with ,",c^M
a,"with
 newline",c^M

If there's any reason why the above won't work for you then see https://stackoverflow.com/questions/45420535/whats-the-most-robust-way-to-efficiently-parse-csv-using-awk or download/use gawks CSV parser extension in gawkextlib.

| improve this answer | |
0

mixed sed awk approach:

$ < file \
sed -e '
  s/$/\x00/
  s/\(,"[^,"]*\)\x00/\1/
  H;1h;$!d;g
  s/\x00\n/\x00/g
' |
awk '/^a,/' RS="\x00" -

Comments: the mixed sed+awk I have taken your code and tweaked it slightly to get the desired results. Main idea being to strip the newlines which sed invariably puts. So we hold back sed from printing after processing every record. Then at the eof we strip the newlines and pass on this NUL delimited data to awk with NUL as the record separator. Then we simply look for records beginning with a,

Output:

a,b,c
a,"with quotes",c
a,"with ,",c
a,"with
 newline",c

Below are given awk only and sed only methods. They rely on quote inside a quoted field to be doubled up.

pure sed approach:

$ sed -Ee ':a
    /^(([^"]*"){2})*[^"]*$/!{
      $d;N;ba
    }
    /^a,/!d
' file

pure awk approach

$ awk -F\" '
   !(NF%2){
      t = $0; n = NF
      while (getline a > 0) {
        t = t ORS a
        n = n + split(a, _x, FS)
        if (!(nf%2)) break 
      }
      $0 = t
   }/^a,/
' file
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.