mysql - 将文本文件导入到 mysql 表中

Question

我正在尝试使用 MySQL 将数据导入到 sql 表中。但文本文件中的数据以不同的方式排列。这是一个这样的例子:

#n O. Willum
#a Res. Center for Microperipherik, Technische Univ. Berlin, Germany
#pc 1
#cn 0
#hi 0
#pi 0.0000
#upi 0.0000
#t new product;product group;active product;long product lifetime;old product;product generation;new technology;environmental benefit;environmental choice;environmental consequence

#index 2
#n D. Wei
#a Dept. of Electr. & Comput. Eng., Drexel Univ., Philadelphia, PA, USA
#pc 1
#cn 0
#hi 0
#pi 0.0000
#upi 0.0000
#t lowpass filter;multidimensional product filter;orthonormal filterbanks;product filter;new approach;novel approach;challenging problem;iterative quadratic programming;negligible reconstruction error;spectral factorization

如何将此文本文件转换为 sql 文件？

Answer 1

您可以使用一个小 AWK 脚本对其进行测试。您只需将表名更改 2 倍，并将字段名替换为真实的字段名。您可以重定向文件中的输出并直接在 mysql 客户端中执行它。

awk 脚本

bash-3.2$ cat gen.awk
BEGIN {
        FS=" "
        print "-- createt by gen.awk"
        print "-- written by Bernd Buffen"
        print "-- buffen@t-online.de"
        print
        out="INSERT INTO myTable SET "
        ok=0;
}
{
        if($1 != "") { rem="-- "
        for(i=1;i<=NF;i++)
        {
            rem=rem" "$i
        }
        }
        print rem;



        if($1 == "#n"  ) {out=out""sep"fieldname_n='"; ok=1;sep=", "; }
        if($1 == "#a"  ) {out=out""sep"fieldname_a='"; ok=1;sep=", "; }
        if($1 == "#pc" ) {out=out""sep"fieldname_pc='"; ok=1;sep=", "; }
        if($1 == "#cn" ) {out=out""sep"fieldname_cn='"; ok=1;sep=", "; }
        if($1 == "#hi" ) {out=out""sep"fieldname_hi='"; ok=1;sep=", "; }
        if($1 == "#pi" ) {out=out""sep"fieldname_pi='"; ok=1;sep=", "; }
        if($1 == "#upi") {out=out""sep"fieldname_upi='"; ok=1;sep=", "; }
        if($1 == "#t"  ) {out=out""sep"fieldname_t='"; ok=1;sep=", "; }
        if(ok == 1)
        {
            tmp=$2
            for(i=3;i<=NF;i++)
            {
                tmp=tmp" "$i
            };
            out=out""tmp"' ";
            ok=0;
        }

        if($1 == "")
        {
            print out";"
            print;
            out="";
            sep="";
            out="INSERT INTO myTable SET ";
            ok=0;
        }
}

输入文件

一定要!!文件末尾有换行符

bash-3.2$ cat text.txt
#n O. Willum
#a Res. Center for Microperipherik, Technische Univ. Berlin, Germany
#pc 1
#cn 0
#hi 0
#pi 0.0000
#upi 0.0000
#t new product;product group;active product;long product lifetime;old product;product generation;new technology;environmental benefit;environmental choice;environmental consequence

#index 2
#n D. Wei
#a Dept. of Electr. & Comput. Eng., Drexel Univ., Philadelphia, PA, USA
#pc 1
#cn 0
#hi 0
#pi 0.0000
#upi 0.0000
#t lowpass filter;multidimensional product filter;orthonormal filterbanks;product filter;new approach;novel approach;challenging problem;iterative quadratic programming;negligible reconstruction error;spectral factorization

输出

bash-3.2$ awk -f gen.awk text.txt
-- createt by gen.awk
-- written by Bernd Buffen
-- buffen@t-online.de

--  #n O. Willum
--  #a Res. Center for Microperipherik, Technische Univ. Berlin, Germany
--  #pc 1
--  #cn 0
--  #hi 0
--  #pi 0.0000
--  #upi 0.0000
--  #t new product;product group;active product;long product lifetime;old product;product generation;new technology;environmental benefit;environmental choice;environmental consequence
--  #t new product;product group;active product;long product lifetime;old product;product generation;new technology;environmental benefit;environmental choice;environmental consequence
INSERT INTO myTable SET fieldname_n='O. Willum' , fieldname_a='Res. Center for Microperipherik, Technische Univ. Berlin, Germany' , fieldname_pc='1' , fieldname_cn='0' , fieldname_hi='0' , fieldname_pi='0.0000' , fieldname_upi='0.0000' , fieldname_t='new product;product group;active product;long product lifetime;old product;product generation;new technology;environmental benefit;environmental choice;environmental consequence' ;

--  #index 2
--  #n D. Wei
--  #a Dept. of Electr. & Comput. Eng., Drexel Univ., Philadelphia, PA, USA
--  #pc 1
--  #cn 0
--  #hi 0
--  #pi 0.0000
--  #upi 0.0000
--  #t lowpass filter;multidimensional product filter;orthonormal filterbanks;product filter;new approach;novel approach;challenging problem;iterative quadratic programming;negligible reconstruction error;spectral factorization
--  #t lowpass filter;multidimensional product filter;orthonormal filterbanks;product filter;new approach;novel approach;challenging problem;iterative quadratic programming;negligible reconstruction error;spectral factorization
INSERT INTO myTable SET fieldname_n='D. Wei' , fieldname_a='Dept. of Electr. & Comput. Eng., Drexel Univ., Philadelphia, PA, USA' , fieldname_pc='1' , fieldname_cn='0' , fieldname_hi='0' , fieldname_pi='0.0000' , fieldname_upi='0.0000' , fieldname_t='lowpass filter;multidimensional product filter;orthonormal filterbanks;product filter;new approach;novel approach;challenging problem;iterative quadratic programming;negligible reconstruction error;spectral factorization' ;

bash-3.2$