mysql - 使用awk将csv文件解析为数组

Question

我必须解析一个 csv 文件并将其内容转储到 mysql 表中。

# myfile.csv# Contents# Sample Headers"header1 with quotes", header2withoutquotes, "header3", header4, hdeader5"Sample Text",2,3,4,"MoreText, with commas""Text2 with escaped \"",8,6,7,9"Text3",876,0.6,7,10

第一个输出

rowid|header1 with quotes|Sample Text|myfile1|header2withoutquotes|2|myfile1|header3|3|myfile1|header4|4|myfile1|header5|MoreText, with commas|myfile2|header1 with quotes|Text2 with escaped \"|myfile2|header2withoutquotes|8|myfile2|header3|6|myfile2|header4|7|myfile2|header5|9|myfile3|header1 with quotes|text3|myfile3|header2withoutquotes|876|myfile3|header3|0.6|myfile3|header4|7|myfile3|header5|10|myfile

在第二个输出中，我需要水平对齐自定义标题。例如

rowid|"header1 with quotes"|"header3"|header4|filename 1|Sample Text|3,4,myfile2|Text2 with escaped \"|6|7|myfile3|Text3|0.6|7|myfile

对于第二个输出，它可以是我选择的任何一组标题。然后我可以使用加载数据 infile 将这两个输出数据加载到 mysql 表中。寻找 awk 脚本来实现这一点。需要帮助请叫我。发送。

Answer 1

这应该有效:

{
    if(NR==1)
        split($0,header,",")
    else
    {
        split($0,line,",")
        for (i in line)  
        {
            gsub(/^[ \t]+|"|[ \t]+$)/, "", header[i]); 
            gsub(/^[ \t]+|"|[ \t]+$)/, "", line[i]); 
            print header[i]"|"line[i]"|"FILENAME
        }
        print ""
    }
}

基本上它将第一行存储在 header 数组中，然后它拆分 elem 数组中的每一行并修剪掉前导和尾随空格或制表符。最后，它组成输出字符串。

输出:

header1|text1|file2
header2|2|file2
header3|3|file2
header4|4|file2
hdeader5|moretext|file2

header1|text2|file2
header2|8|file2
header3|6|file2
header4|7|file2
hdeader5|9|file2

header1|text3|file2
header2|876|file2
header3|0.6|file2
header4|7|file2
hdeader5|10|file2

您可以通过删除最后一个 print "" 语句来摆脱每个 block 之间的换行符。