0

I have a CSV file with many lines like these:

1003,CC,L1008,,(LB1) Urbà de Barberà del Vallès,3,,,
1006,CK,L0520,,Móra d'Ebre - Móra la Nova (estació),3,,,
1007,AV,L0358,,Granollers-Terrassa-Manresa,3,,,
1011,DD,L0480,,(781) St. Vicenç Castellet-Castellgalí-Manresa,3,,,
1012,DD,L0481,,(784) Manresa-Castellgalí-St.Vicenç-Monistrol,3,,,
1013,DD,L0487,,(783) Manresa-Pt.Vilomara-St.Vicenç-Monistrol,3,,,
...

What I need to do is, if the 5th column starts with ( copy the value between parenthesis in place of the 3rd column. If not, leave the line as is.

So the result would be:

1003,CC,LB1,,(LB1) Urbà de Barberà del Vallès,3,,,
1006,CK,L0520,,Móra d'Ebre - Móra la Nova (estació),3,,,
1007,AV,L0358,,Granollers-Terrassa-Manresa,3,,,
1011,DD,781,,(781) St. Vicenç Castellet-Castellgalí-Manresa,3,,,
1012,DD,784,,(784) Manresa-Castellgalí-St.Vicenç-Monistrol,3,,,
1013,DD,783,,(783) Manresa-Pt.Vilomara-St.Vicenç-Monistrol,3,,,
...

I'm thinking on doing it with sed, and I know I can capture the part between parenthesis, but I don't know how to replace a text that is not known.

Is it possible to do it with sed?

2

Provided that your CSV does not have embedded separators (comma separated fields that contain quoted commas) then this is simple with Awk:

$ awk 'BEGIN{OFS=FS=","} match($5,/^\([0-9]+\)/) {$3 = substr($5,RSTART+1,RLENGTH-2)} 1' file.csv
1003,CC,L1008,,(LB1) Urbà de Barberà del Vallès,3,,,
1006,CK,L0520,,Móra d'Ebre - Móra la Nova (estació),3,,,
1007,AV,L0358,,Granollers-Terrassa-Manresa,3,,,
1011,DD,781,,(781) St. Vicenç Castellet-Castellgalí-Manresa,3,,,
1012,DD,784,,(784) Manresa-Castellgalí-St.Vicenç-Monistrol,3,,,
1013,DD,783,,(783) Manresa-Pt.Vilomara-St.Vicenç-Monistrol,3,,,

Using Sed (with the same restriction):

$ sed -E 's/^([^,]*),([^,]*),([^,]*),([^,]*),\(([0-9]+)\)/\1,\2,\5,\4,(\5)/' file.csv
1003,CC,L1008,,(LB1) Urbà de Barberà del Vallès,3,,,
1006,CK,L0520,,Móra d'Ebre - Móra la Nova (estació),3,,,
1007,AV,L0358,,Granollers-Terrassa-Manresa,3,,,
1011,DD,781,,(781) St. Vicenç Castellet-Castellgalí-Manresa,3,,,
1012,DD,784,,(784) Manresa-Castellgalí-St.Vicenç-Monistrol,3,,,
1013,DD,783,,(783) Manresa-Pt.Vilomara-St.Vicenç-Monistrol,3,,,

More robustly, using perl's Text::CSV module:

$ perl -C -MText::CSV -lne '
    BEGIN{$p = Text::CSV->new()} 
    @f = $p->fields() if $p->parse($_); 
    $f[2] = $1 if $f[4] =~ /^\((\d+)\)/; 
    print join ",", @f
  ' file.csv
1003,CC,L1008,,(LB1) Urbà de Barberà del Vallès,3,,,
1006,CK,L0520,,Móra d'Ebre - Móra la Nova (estació),3,,,
1007,AV,L0358,,Granollers-Terrassa-Manresa,3,,,
1011,DD,781,,(781) St. Vicenç Castellet-Castellgalí-Manresa,3,,,
1012,DD,784,,(784) Manresa-Castellgalí-St.Vicenç-Monistrol,3,,,
1013,DD,783,,(783) Manresa-Pt.Vilomara-St.Vicenç-Monistrol,3,,,
1

Why not in Python? (replace filename with your filename)

import re, csv
for line in list(csv.reader(open('filename', 'r'))):
    matches = re.findall('\([0-9]*\)',line[4])
    if matches:
        line[2] = matches[0]
    print(','.join(line))
0

Done by below script

count=`awk '{print NR}' filename| sort -nr| sed -n '1p'`

for ((i=1;i<=$count;i++)); do h=`awk -v i="$count" -F ","  'NR==i && $5 ~ /^\(/{print $5}' filename | awk '{print $1}'| sed -e  "s/(//g" -e "s/)//g"| wc -l`; if [[ $h != 0 ]]; then k=`awk -v i="$count" -F ","  'NR==i && $5 ~ /^\(/{print $5}' filename | awk '{print $1}'| sed -e  "s/(//g" -e "s/)//g"`; awk -v i="$count" -v k="$k" -F "," 'NR==i && $5 ~ /^\(/{$3=k;print $0}' filename| sed "s/ /,/g"; else awk -v i="$count" 'NR==i {print $0}' filename; fi; done

output

1003,CC,LB1,,(LB1),Urbà,de,Barberà,del,Vallès,3,,,
1006,CK,L0520,,Móra d'Ebre - Móra la Nova (estació),3,,,
1007,AV,L0358,,Granollers-Terrassa-Manresa,3,,,
1011,DD,781,,(781),St.,Vicenç,Castellet-Castellgalí-Manresa,3,,,
1012,DD,784,,(784),Manresa-Castellgalí-St.Vicenç-Monistrol,3,,,
1013,DD,783,,(783),Manresa-Pt.Vilomara-St.Vicenç-Monistrol,3,,,
0

The awk and sed versions that steeldriver provided work like a charm, and are very easy to implement in my current environment.

I've improved them a bit to allow also letters, spaces and dots in the code between parenthesis:

awk 'BEGIN{OFS=FS=","} match($5,/^\([a-zA-Z0-9 .]+\)/) {$3 = substr($5,RSTART+1,RLENGTH-2)} 1' file.csv

sed -E 's/^([^,]*),([^,]*),([^,]*),([^,]*),\(([a-zA-Z0-9 .]+)\)/\1,\2,\5,\4,(\5)/' file.csv

Other versions do also work, but doesn't fit as good in the workflow I'm doing.

Thanks!

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.