shell 导入csv到mysql

来源：互联网发布：coc女皇升级数据2017年编辑：程序博客网时间：2024/06/01 09:16

方法一：整个文件导入

#/bin/bashexport_sql="LOAD DATA INFILE '$1'            INTO TABLE $3            CHARACTER SET utf8            FIELDS TERMINATED BY ','            ENCLOSED BY '\"' "mysql -h127.0.0.1 -P3306 -uroot -proot $2 -e "${export_sql}"

执行：

[root@xiaofei ~]# /home/xiaofei/test/test.sh  /usr/soft/t_config.csv psi t_config

方法二：一行一行读取然后导入

shell脚本内容如下

#!/bin/sh#The username of mysql databaseUSER="root"#The password of mysql databasePASS="dragonwake"#The datebase name will be createdDATABASE="SmartCare"TABLE="sct_Province"TABLE_DATA="prov.csv"if [ -f ${TABLE_DATA} ]; thenecho "File ${TABLE_DATA} Existed."else echo "File ${TABLE_DATA} Not Existed."echo exit 2fiwhile read line;doquery=`echo $line | awk -F, '{printf("\"%s\", \"%s\", \"%s\", \"%s\"", $2, $3, $4, $5)}'`statement=`echo "insert into ${TABLE}(ProvinceCode, ParentID, ProvinceName, Level) values($query)"`echo $statementmysql -u $USER -p$PASS $DATABASE << EOF > /dev/nullinsert into ${TABLE}(ProvinceCode, ParentID, ProvinceName, Level) values($query);EOFdone < $TABLE_DATAif [ $? -eq 0 ]; thenecho "Insert Data Into $TABLE Success."fi

注：如果csv是从windows系统上传的要去改下编码方式 window一般是doc 先用vi打开csv文件然后：set ff？查看是否是unix 不是的话 :set ff=unix 然后wq保存退出

0 0