import os
import pandas as pd
deftoCSV(inFilePath,outFilePath):
read_file = pd.read_excel (inFilePath,sheet_name =0)
read_file.to_csv (outFilePath,
index =None,
header=True)defwalkFile():#fileList = []#dirList = []for root, _ , files in os.walk(import_path):# root 表示当前正在访问的文件夹路径# dirs 表示该文件夹下的子目录名list# files 表示该文件夹下的文件list# 遍历文件for f in files:
inFilePath = os.path.join(root, f)
fname,_ = os.path.splitext(f)
outFilePath = os.path.join(export_path, fname +".csv")print(outFilePath)
toCSV(inFilePath,outFilePath)#fileList.append(file)# # 遍历所有的文件夹# for d in dirs:# folder = os.path.join(root, d)# dirList.append(folder)# return fileList, dirList
import_path ="./import_path"
export_path ="./export_path"
walkFile()
3.创建两个shell脚本一个负责向DB导入数据,一个负责批量导入
负责向DB导入数据
#!/bin/bash
mysql --local-infile -h 连接地址 -uroot -proot<<EOF
use import_data;
LOAD DATA LOCAL INFILE '$1'
INTO TABLE data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(@col1,@col2,@col3,@col4,@col5,@col6,@col7,@col8,@col9,@col10,@col11,@col12,@col13,@col14,@col15,@col16,@col17,@col18,@col19,@col20,@col21,@col22,@col23,@col24,@col25,@col26,@col27,@col28,@col29,@col30,@col31,@col32,@col33,@col34,@col35,@col36,@col37,@col38,@col39,@col40,@col41,@col42,@col43,@col44,@col45,@col46,@col47,@col48)
SET record_key = @col1,field1=@col23,field2=@col16,field3=@col25,field4=@col28,field5=@col5;
EOFexit;