SQL/shell将Excel数据导入到MySQL数据库中

我不是女神ヾ 2022-10-11 01:11 529阅读 0赞

最近对接友商几个接口，有个接口需要友商提供源头数据，友商懒得要死，直接说通过线下发数据，直接甩给我们excel让我们到文件里面找数据。这对于系统来讲简直没法用，都啥年代了，打仗还用石头啊。。我们只能先将它们发的数据同步到数据库中。如何将Excel数据同步到数据库中呢？网上搜了下，有下面几种方式。

1.  借助Navicat导入向导功能，将.csv文件导入(太简单了 不介绍)。
2.  使用sql导入
3.  使用shell脚本导入

## 1. 如何使用sql导入？ ##

假如导入一个用户表，对应字段为：username、phone、address。

1.  新建一个数据表customer
    
        CREATE TABLE `customer` (
          `username` varchar(255) DEFAULT NULL,
          `phone` varchar(255) DEFAULT NULL,
          `address` varchar(255) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.  新建一个excel文件，列数，字段对应customer表  
    ![在这里插入图片描述][20210629180354146.png]
3.  新建一个`1.txt`文件，放到`C`盘，将excel文件中数据复制过去，不要复制标题  
    ![在这里插入图片描述][2021062918151244.png]
4.  在`customer`对应的库执行一下脚本
    
        LOAD DATA LOCAL INFILE 'C:/1.txt'INTO TABLE customer FIELDS TERMINATED BY '\t'

刷新表格，已经有数据了  
![在这里插入图片描述][20210629184100111.png]  
但是中文乱码了!!  
查看字符集：`show variables like "%char%";`  
![在这里插入图片描述][20210629184229840.png]  
把不是`utf8`的都设置为`utf8`

set character_set_database= utf8;
    set character_set_filesystem= utf8;
    set character_set_server =utf8;

查看下字符集  
![在这里插入图片描述][20210629184427891.png]  
再次导入后查看  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3F3cXczMzMzMzMz_size_16_color_FFFFFF_t_70]  
总体来说这种方式还是比较麻烦的 ，下面要放大招了

## 2. shell脚本导入 ##

1.  依然使用上面新建的`customer`表
2.  按照数据库的列，新建好.xls或者.xlsx excel文件  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3F3cXczMzMzMzMz_size_16_color_FFFFFF_t_70 1]
3.  将excel文件另存为.cvs文件，用Notepad++或者其它编辑器将.csv文件保存为utf8编码文件，否可可能导致字段中文乱码  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3F3cXczMzMzMzMz_size_16_color_FFFFFF_t_70 2]
4.  shell脚本内容
    
        #/bin/bash
        desc="The following Variables are required to run the bash:\n \$1-database username, \n \$2- database password ,\n \$3- the path of .csv file,which will be imported ! \n \n"
        
        echo -e $desc
        
        #The datebase to be connected
        DB="test"
        #The target table whitch will be putted data
        TABLE="customer"
        
        
        if [ -f $3 ]; then
        
                echo "File $3 Existed."
        
        else
        
                echo "File $3 Not Existed."
                echo 
                exit 2
        
        fi
        
        while read line;
        do        
        query=`echo $line | awk -F, '{printf("\"%s\", \"%s\", \"%s\"", $1,$2, $3)}'`  
        statement=`echo "insert into ${TABLE}(username, phone, address) values($query)"`
                echo $statement
        mysql -h127.0.0.1 -P3306 -u$1 -p$2 $DB  << EOF > /dev/null        
        insert into ${TABLE}(username, phone, address) values($query);
                
        EOF
        
        done < $3
        
        if [ $? -eq 0 ]; then
        
                echo "Insert Data Into $TABLE Success."
        
        fi
    
    *脚本很简单，都有注释*
    
    > $1 $2 $3分别表示数据库用户名，数据库密码，.csv文件路径，启动时需要以此指定
5.  将shell脚本 ，csv文件随便放到一个地方，执行：
    
        ./doimport.sh root 123456 /home/chenyulin/importtest/customer.csv
    
    输出：
    
        [root@localhost importtest]# ./doimport.sh root 123456 /home/chenyulin/importtest/customer.csv
        The following Variables are required to run the bash:
         $1-database username, 
         $2- database password ,
         $3- the path of .csv file,which will be imported ! 
         
        
        File /home/chenyulin/importtest/customer.csv Existed.
        ")sert into customer(username, phone, address) values("张三", "15522255225", "陕西省
        "====="张三", "15522255225", "陕西省
        ")sert into customer(username, phone, address) values("李四", "15822556652", "山东省
        "====="李四", "15822556652", "山东省
        ")sert into customer(username, phone, address) values("王五", "18854525412", "西藏
        "====="王五", "18854525412", "西藏
        Insert Data Into customer Success.
    
    *如果依然中文乱码就执行`sql导入`中提到的乱码解决办法*
6.  最后数据库中已经有数据了  
    ![在这里插入图片描述][20210706180329343.png]

[20210629180354146.png]: /images/20221005/8807e2507ef44899b8cfc722463c97a3.png
[2021062918151244.png]: /images/20221005/a0333ee6532b46b1a01510da14114852.png
[20210629184100111.png]: /images/20221005/395dc1e4aac7407eb5069ae6c72764ff.png
[20210629184229840.png]: /images/20221005/eb18e60335f44645bb98a0a016c9ac66.png
[20210629184427891.png]: /images/20221005/5a96893bf54540308cf197a12a8ab9e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3F3cXczMzMzMzMz_size_16_color_FFFFFF_t_70]: /images/20221005/45288b02aa2b4cc4b0f3b1479e76f35a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3F3cXczMzMzMzMz_size_16_color_FFFFFF_t_70 1]: /images/20221005/a8d54ac07cbf47a583ae0ccb891cf2fb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3F3cXczMzMzMzMz_size_16_color_FFFFFF_t_70 2]: /images/20221005/aa7c0dc23c0f419fa9a070c3f9c3714b.png
[20210706180329343.png]: /images/20221005/dbe5cac7249b41e18fb48fc96aab3431.png