分割CSV单行文件与awk

Question

需要在mysql中插入单行CSV文件(VoIP费率)。文件模式:

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

where FS="，" and RS=" .

我如何将单行拆分为单独的行或使用AWK构建直sql插入文件的每个目的地?

CSV文件

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

需要修改为每行一个目标记录

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

我得到了awk -F ',' '{print $1$2}' rates.csv

的两个前字段

1201 New Jersey

我可以通过awk -F '[, ]' '{print $4}' rates.csv切割的第三个字段

0.012500

但是我不知道如何很好地将它循环到整行。

谢谢

Answer 1

给定新的输入:

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

与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

与任意awk:

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

或者如果你喜欢循环:

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

Answer 2

$ 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