CODEWITHSUNDEEP
awkgawk
Polucho
Polucho

Reputation: 23

Edit .csv file with AWK

I have a csv file in which I have to make some changes which you will see in the examples I will put. And I think I can do it with arrays, but I do not know how to structure it. Any ideas?

Original File;

"1033reto";"V09B";"";"";"";"";"";"QVN";"V09B"
"1033reto";"V010";"";"";"";"";"";"QVN";"V010"
"1033reto";"V015";"";"";"";"";"";"QVN";"V015"
"1033reto";"V08C";"";"";"";"";"";"QVN";"V08C"
"1040reto";"V03D";"";"";"";"";"";"QVN";"V03D"
"1040reto";"V01C";"";"";"";"";"";"QVN";"V01C"
"1050reto";"V03D";"";"";"";"";"";"QVN";"V03D"
"1050reto";"V01F";"V07L";"";"";"";"";"QVN";"V01C"

Desired Output:

"1033reto";"V09B";"V010";"V015";"V08C";"";"QVN";"V09B"
"1033reto";"V09B";"V010";"V015";"V08C";"";"QVN";"V010"
"1033reto";"V09B";"V010";"V015";"V08C";"";"QVN";"V015"
"1033reto";"V09B";"V010";"V015";"V08C";"";"QVN";"V08C"
"1040reto";"V03D";"V01C";"";"";"";"";"QVN";"V03D"
"1040reto";"V03D";"V01C";"";"";"";"";"QVN";"V01C"
"1050reto";"V03D";"V01F";"V07L";"";"";"";"QVN";"V03D"
"1050reto";"V03D";"V01F";"V07L";"";"";"";"QVN";"V01C"

Upvotes: 0

Views: 181

Answers (1)

karakfa
karakfa

Reputation: 67497

awk to the rescue!

two pass algorithm. no provision whether it will overwrite existing last two columns if there are enough rows...

$ awk 'BEGIN{FS=OFS=";"} 
     NR==FNR{for(i=2;i<=7;i++) if($i!="\"\"") a[$1]=a[$1] FS $i;next}
            {n=split(a[$1],f,FS); for(i=2;i<=n;i++) $i=f[i]}1' csv{,}

"1033reto";"V09B";"V010";"V015";"V08C";"";"";"QVN";"V09B"
"1033reto";"V09B";"V010";"V015";"V08C";"";"";"QVN";"V010"
"1033reto";"V09B";"V010";"V015";"V08C";"";"";"QVN";"V015"
"1033reto";"V09B";"V010";"V015";"V08C";"";"";"QVN";"V08C"
"1040reto";"V03D";"V01C";"";"";"";"";"QVN";"V03D"
"1040reto";"V03D";"V01C";"";"";"";"";"QVN";"V01C"
"1050reto";"V03D";"V01F";"V07L";"";"";"";"QVN";"V03D"
"1050reto";"V03D";"V01F";"V07L";"";"";"";"QVN";"V01C"

UPDATE: in order to guard the last two fields add if(n>7)n=7; after the split.

Upvotes: 2

Related Questions