最近对接友商几个接口,有个接口需要友商提供源头数据,友商懒得要死,直接说通过线下发数据,直接甩给我们excel让我们到文件里面找数据。这对于系统来讲简直没法用,都啥年代了,打仗还用石头啊。。我们只能先将它们发的数据同步到数据库中。如何将Excel数据同步到数据库中呢?网上搜了下,有下面几种方式。
- 借助Navicat导入向导功能,将.csv文件导入(太简单了 不介绍)。
- 使用sql导入
- 使用shell脚本导入
1. 如何使用sql导入?
假如导入一个用户表,对应字段为:username、phone、address。
-
新建一个数据表customer
CREATE TABLE `customer` ( `username` varchar(255) DEFAULT NULL, `phone` varchar(255) DEFAULT NULL, `address` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
新建一个excel文件,列数,字段对应customer表
-
新建一个
1.txt
文件,放到C
盘,将excel文件中数据复制过去,不要复制标题
-
在
customer
对应的库执行一下脚本LOAD DATA LOCAL INFILE 'C:/1.txt'INTO TABLE customer FIELDS TERMINATED BY '\t'
刷新表格,已经有数据了
但是中文乱码了!!
查看字符集:show variables like "%char%";
把不是utf8
的都设置为utf8
set character_set_database= utf8;
set character_set_filesystem= utf8;
set character_set_server =utf8;
查看下字符集
再次导入后查看
总体来说这种方式还是比较麻烦的 ,下面要放大招了
2. shell脚本导入
-
依然使用上面新建的
customer
表 -
按照数据库的列,新建好.xls或者.xlsx excel文件
-
将excel文件另存为.cvs文件,用Notepad++或者其它编辑器将.csv文件保存为utf8编码文件,否可可能导致字段中文乱码
-
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文件路径,启动时需要以此指定
-
将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导入
中提到的乱码解决办法 -
最后数据库中已经有数据了