一个批量处理CSV文件插入到MySQL的dos脚本

1，编写dos命令批处理脚本，用于解压目录里的zip包里面的csv文件

        可执行文件名：movezip.bat

@echo off
setlocal

:: 设置源目录和目标目录，请替换下面的路径
set sourceDir=E:\tmp\
set targetDir=E:\tmp\csv\

echo 1 %sourceDir%
echo 1 %targetDir%

del %targetDir%*.csv

REM 查找源目录及其所有子目录下的zip文件
for /r %sourceDir% %%z in (*.zip) do (
    echo "%%z"
 ::   copy "%%z" D:\tmp\
	"C:\Program Files\7-Zip\7z.exe" x -o"%targetDir%" "%%z"
)

endlocal

2，循环处理csv文件，导入mysql

可执行文件名：insert.bat

@echo off

setlocal enabledelayedexpansion

:: MySQL数据库参数
set "dbHost=10.0.0.112"
set "dbPort=3306"
set "dbName=db_test"
set "dbUser=root"
set "dbPassword=123456"

:: CSV文件所在目录
set "directory=E:\\tmp\\csv\"

:: 目标MySQL表名
set "tableName=tk_gov_vatinvoice1"

set "sql_file=load_data.sql"

:: 遍历目录下的所有CSV文件
for %%f in ("%directory%\*.csv") do (
	
	echo LOAD DATA LOCAL INFILE '%%f' INTO TABLE !tableName! FIELDS TERMINATED BY ',' ENCLOSED BY '^"' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS (comcode,comname,@var2,@var3,@var8,@var4,sellername,@var5,buyername,billingtime,fee,tax,@var6,billtypeinvoice,billstate,billrisk,@var7,@var1^) SET billingno=REPLACE^(@var2,'\'',''^),dayidx=STR_TO_DATE(@var1, ^'%%^Y-%%m-%%d'^),billingcode=REPLACE^(@var3,'\'',''^),sellernumber=REPLACE^(@var4,'\'',''^),buyernumber=REPLACE^(@var5,'\'',''^),datasource=REPLACE^(@var6,'\'',''^),billdirection=REPLACE^(@var7,'\'',''^),digiteleinvoice=REPLACE^(@var8,'\'',''^); > !sql_file!		
	
	mysql -h %dbHost% -P %dbPort% -u %dbUser% -p%dbPassword% %dbName% < !sql_file!
)

endlocal

3，仔细按照目录路径放好，执行bat文件即可