Script for nested delimiter

rajakt · 07-04-2014, 06:38 AM

Hi,

I have file having data as :

"aaa","aaa,vvvv,llll,kkk","bbbbb"
"g","pppp,kk,gg","caa,a,d,e,f","jjj,mmm,lll"

want the outout :
aaa|aaa,vvvv,llll,kkk|bbbbb
g|pppp,kk,gg|caa,a,d,e,f|jjj,mmm,lll

Can anyone provide me directions? I used cut -d, simple awk ,simple sed, but could not get thru in Linux.

Guttorm · 07-08-2014, 11:46 AM

First one is a CSV file.

Code:

sudo apt-get install csvtool

csvtool -u '|' cat filename

danielbmartin · 07-08-2014, 12:39 PM

This transformation may be done in two steps.
1) Replace every instance of "," with |.
2) Replace every instance of " with null.

With this InFile ...

Code:

"aaa","aaa,vvvv,llll,kkk","bbbbb"
"g","pppp,kk,gg","caa,a,d,e,f","jjj,mmm,lll"

...this sed ...

Code:

sed 's/","/|/g; s/"//g' $InFile >$OutFile

... produced this OutFile ...

Code:

aaa|aaa,vvvv,llll,kkk|bbbbb
g|pppp,kk,gg|caa,a,d,e,f|jjj,mmm,lll

Daniel B. Martin

grail · 07-09-2014, 11:33 AM

The sed is probably clearer / cleaner:

Code:

awk '{$1=$1}gsub(/"/,"")' FPAT='"[^"]*"' OFS="|" file

Or in ruby:

Code:

ruby -ne 'puts $_.scan(/"([^"]*)",?/).join("|")' file

danielbmartin · 07-09-2014, 02:29 PM

For those who prefer an awk solution ...

This transformation may be done in two steps.
1) Replace every instance of "," with |.
2) Replace every instance of " with null.

With this InFile ...

Code:

"aaa","aaa,vvvv,llll,kkk","bbbbb"
"g","pppp,kk,gg","caa,a,d,e,f","jjj,mmm,lll"

...this awk ...

Code:

awk 'gsub(/","/,"|") gsub(/"/,"")' $InFile >$OutFile

... produced this OutFile ...

Code:

aaa|aaa,vvvv,llll,kkk|bbbbb
g|pppp,kk,gg|caa,a,d,e,f|jjj,mmm,lll

Daniel B. Martin

Guttorm · 07-10-2014, 04:04 AM

The problem with most sed/awk solutions is that CSV files are a bit complicated. Quotes are allowed in values, and also newlines. This example doesn't have any, but maybe it will sometimes later?

For example we have a value which is:

Code:

Hello
"world"

It's encoded as

Code:

"Hello
""world"""

This is tricky to parse. I think it's better to use a proper parser for CSV.