How can I add if-else condition to define the field separator in gawk, ksh?

Question

I have to handle some data that comes in pipe delimited files, where each field is enclosed in double quotes.

"Boolean"|"dada -sdf|xcvnb"|"123"

If I take FS="|", then the script takes the above as four fields, whereas this is actually three fields. If I take FS="\"|\"" then I have two issues:

1. I have to deal with 1st and the last field that becomes, "Boolean and 123" separately
2. And more importantly, Now since we don’t have double quotes anymore, when I take each field and process, some functions or commands may not take the whole string that is there in the field(since they may be separated by spaces and different other characters).e.g. the 2nd field becomes dada -sdf|xcvnb i.e. without quotes, which for some commands may give erroneous results as - may be interpreted as options, or only the 1st word may be taken as argument and rest of the string after space is not taken into consideration at all.

My thought - I want to tell gawk that take FS as | only if it is followed by a " and preceded by a ". That way I don't strip off the double quotes from the fields.

How can I write the code? Is there a way?

Answer 1

With gawk you can use the FPAT variable do define a pattern how a field looks like. In your case a field consists of a " followed by zero or more arbitrary characters except of the " and a final ".

Having this you can simply print the first field $1 and the last field $NF:

gawk '{print $1,$NF}' FPAT='"[^"]*"' OFS="|" \
    <<< '"Boolean"|"dada -sdf|xcvnb"|"123"'

Output:

"Boolean"|"123"

Answer 2

I want to tell gawk that take FS as | only if it is followed by a " and preceded by a "

You can do that exactly in perl with positive lookahead and lookbehind...

$ perl -F'/"\K\|(?=")/' -le 'print "$F[0] : $F[1] : $F[2]"' ip.txt 
"Boolean" : "dada -sdf|xcvnb" : "123"

• -F to specify a field separator and save in @F array
  • also sets the -n flag which means iterate over input files in a loop, lines are NOT printed by default
  • To split on spaces, one can simply use -a option
• '/"\K\|(?=")/' using regex to specify field separator. "\K is positive lookbehind and (?=") is positive lookahead. \| is the separator (| needs to be escaped)
• -l strip newline from input lines and add newline to print statements
• -e allows to pass perl code directly instead of from file
• $F[0] array index starts with 0, this one refers to first element of @F array

Further reading:

• Perl flags -pe, -pi, -p, -w, -d, -i, -t?
• perl command switches

Answer 3

I'd use a real CSV parser. I like ruby's:

ruby -rcsv -e '
  opts = { :col_sep => "|", :force_quotes => true }
  CSV.parse(STDIN.read, opts) do |row|
    row.delete_at(1)
    puts row.to_csv(opts)
  end
' filename

outputs

"Boolean"|"123"