2

I have a csv for which I have to extract a specific field. The csv is pipe-delimited (|), with double quotes (") to protect text fields and (this is the catch) newlines in some of the text.

eg.

"aaa"|"111"|"!!!"|""
"bbb"|"222"|"@@
@"|""
"ccc"|"333"|"###"|""

I would like to extract the second field of each record:

111
222
333

I am using a bash script to read the file, but it seems the read command stops when it reaches a newline, even when -d is specified. That means, in the above example, that my script processes record 1 correctly (I use read -d \| varname), but not record 2, because it doesn't recognise the newline as part of the third field. Now it sees @"|"" as a new record and everything gets messy.

Is it at all possible to use read for this, or should I look at alternatives?

I've spent DAYS playing around with read's settings and searching on the web. Funny thing, I came across someone with a problem on the exact same kind of input file that I have, but that problem was with Excel.

  • Is your 2nd column the only one contain digits? – cuonglm May 10 '16 at 15:10
  • No, any field can contain any type of data, and any field can contain newlines. – oxydog May 11 '16 at 5:31
4

For a shell with a read builtin that can handle CSV, you can use ksh93 instead of bash:

$ while IFS='|' read -rS a b c; do printf '%s\n' "$b"; done < file
111
222
333

To convert that format to something that bash's read can handle, you could do:

< file ksh93 -c 'while IFS="|" read -rSA a; do
                   printf "%s|" "${a[@]//[\|]/\\\0}"
                   printf "\0"
                 done' |
       bash -c 'while IFS="|" read -d "" a b c; do
                  printf "%s\n" "$b"
                done'
1

You should really be using a proper CSV parser. For example, using the one that ships with ruby:

ruby -rcsv -e 'CSV.foreach("file", :col_sep => "|") {|row| p row; puts row[1]}'

we get

["aaa", "111", "!!!", ""]
111
["bbb", "222", "@@\n@", ""]
222
["ccc", "333", "###", ""]
333

You can see for the 2nd row, there is the embedded newline. Remove p row to get rid of those "debugging" lines.

0

OK, so the best solution for me (but I guess it's a matter of taste), was using PHP's fgetcsv, since I already have PHP on that server. It's a pity the bash read command doesn't handle the newlines as well as the PHP function. It automatically recognises additional delimiters (like "'s).

Sample:

<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 10000, "|")) !== FALSE) {
        $num = count($data);
        echo "$num fields in line $row:\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $c + 1 . ": " . $data[$c] . "\n";
        }
    }
    fclose($handle);
}
?>

Output (for example in my original question):

4 fields in line 1:
1: aaa
2: 111
3: !!!
4: 
4 fields in line 2:
1: bbb
2: 222
3: @@
@
4: 
4 fields in line 3:
1: ccc
2: 333
3: ###
4: 

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.