CODEWITHSUNDEEP
awk
user1956684
user1956684

Reputation: 23

split CSV single line file with awk

Single line CSV file (VoIP rates) need to be insert in mysql. File pattern:

citycode01,city name01,rate01 citycode02,city name02,rate02 citycode0n,city name0n,rate0n

where FS="," and RS=" ".

how can I split single line into every destination in separate line or build straight sql insert file using AWK?

CSV file

1201,New Jersey,0.012500 1202,USA OUT,0.012500 1203,USA OUT,0.012500 1204,Manitoba,0.008000

Need to be modified to one destination record per line

1201,New Jersey,0.012500  
1202,USA OUT,0.012500  
1203,USA OUT,0.012500  
1204,Manitoba,0.008000

I got two first fields by awk -F ',' '{print $1$2}' rates.csv

1201 New Jersey

Third field I can cut by awk -F '[, ]' '{print $4}' rates.csv

0.012500

But I don't know how to nicely loop it in around a whole line.

Thank you

Upvotes: 1

Views: 348

Answers (2)

Ed Morton
Ed Morton

Reputation: 203209

Given your new input:

1604999,Vancouver - BC,0.008000 1605,USA OUT,0.012500 1605397,USA,0.039000 1605475,,0.061000 1605477,,0.061000 1605692,,0.061000 1605715,,0.061000 1606,USA OUT,0.012500 1607,USA OUT,0.012500 1608,USA OUT,0.012500

With GNU awk for FPAT:

$ awk -v FPAT='[0-9]+,[^,]*,[0-9.]+' -v OFS='\n' '{$1=$1}1' file
1604999,Vancouver - BC,0.008000
1605,USA OUT,0.012500
1605397,USA,0.039000
1605475,,0.061000
1605477,,0.061000
1605692,,0.061000
1605715,,0.061000
1606,USA OUT,0.012500
1607,USA OUT,0.012500
1608,USA OUT,0.012500

With any awk:

$ awk -v ORS= '{gsub(/[0-9]+,[^,]*,[0-9.]+/,"&\n"); gsub(/\n /,"\n")}1' file

or if you prefer a loop:

$ awk '{
    while ( match($0,/[0-9]+,[^,]*,[0-9.]+/) ) {
        print substr($0,RSTART,RLENGTH)
        $0 = substr($0,RSTART+RLENGTH)
    }
}' file

Upvotes: 2

John1024
John1024

Reputation: 113814

$ awk -F' ' -v RS=, 'NR>1 && 1==NR%2{print $1; printf "%s,",$2; next} {printf "%s,",$0}' rates.csv
1201,New Jersey,0.012500
1202,USA OUT,0.012500
1203,USA OUT,0.012500
1204,Manitoba,0.008000

Upvotes: 1

Related Questions