利用bcp批量导入指定文件夹下CSV数据到sql server

来源：互联网发布：js array add 编辑：程序博客网时间：2024/05/21 17:35

先使用Convert Excel To CSV 29.12.28将不同sheet页excel批量转换为csv，再利用bcp导入sql

1) Convert Excel To CSV 29.12.28软件注册码地址 http://www.keygenguru.com/serial/convert_excel_to_csv_29_12_28.html

2) excel文件没有包含日期列，日期列在excel文件名中有包含

3) dbo.udf_GetNumeric() 是自定义函数，可以从文件名字符串中获得数字

4) 指定好文件夹后，读取文件夹和子文件夹下所有csv文件

以下是导入sql script

-- sqlcmd -S 192.168.0.111 -U sa -P Ab123456 -i d:\batchImport.sql -o d:\output.txt/*use icbc_report_dbCREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255),WHICHDATE date)go*/use icbc_report_dbdeclare @filename varchar(255),@path varchar(2000),@sql varchar(8000),@cmd varchar(1000),@ins_date datedeclare @tmpTableName varchar(500)declare @targetTableName varchar(500)declare @formatFileName varchar(500)-- 临时表set @tmpTableName = '[icbc_report_db].[dbo].[CliSur_tmp]'-- 目标表set @targetTableName = '[icbc_report_db].[dbo].[CliSur]'-- 导入bcp格式文件(格式化文件需要放在和导入csv相同目录下)set @formatFileName = 'CliSur_tmp.fmt'print 'Temporary Table: ' + @tmpTableNameprint 'Target Table: ' + @targetTableNameprint 'Format File: ' + @formatFileName-- 清空临时表print 'delete ' + @tmpTableNameEXEC ('delete ' + @tmpTableName)print 'delete ALLFIENAMES'delete ALLFILENAMES-- 设置csv存放路径-- 放在c盘会提示没有权限SET @path = 'D:\客户调研表\'SET @cmd = 'dir ' + @path + '*.csv /b /a-d /s'INSERT INTO  ALLFILENAMES(WHICHFILE) EXEC Master..xp_cmdShell @cmdUPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is nulldelete ALLFILENAMES where WHICHFILE is NULL-- 保存文件日期到WHICHDATEupdate ALLFILENAMES set WHICHDATE = cast(dbo.udf_GetNumeric(replace(WHICHFILE,WHICHPATH,'')) as date)-- 循环加入文件表declare c1 cursor for SELECT WHICHPATH,WHICHFILE,WHICHDATE FROM ALLFILENAMES where WHICHFILE like '%.csv%'    open c1    fetch next from c1 into @path,@filename,@ins_date    While @@fetch_status <> -1begin-- 采用文件格式跳过了临时表日期列set @sql = 'bcp '+ @tmpTableName + ' in ' + @filename + ' -f ' + @path +@formatFileName + ' -T -F2'print 'bcp '+ @tmpTableName + ' in ' + @filename + ' -f ' + @formatFileName + ' -T -F2'EXEC icbc_report_db..xp_cmdshell @sql-- 把文件名读取到的日期，更新到临时表日期列--update @tmpTableName set [日期] = '@ins_date' where [日期] is NULL-- 注意插入日期要加单引号EXEC ('update ' + @tmpTableName + ' set [日期] = ''' + @ins_date + ''' where [日期] is NULL')--select * from [icbc_report_db].[dbo].[CliSur_tmp]fetch next from c1 into @path,@filename,@ins_dateendclose c1deallocate c1-- 将最终结果加入到客户调研统计表declare @sql_tmpCols varchar(8000)declare @sql_targetCols varchar(8000)-- 映射关系set @sql_targetCols = '[Date],[TellerId],[FuncGrpName],[DataSource],[AnsCallNum],[QReplyNum],[SurNum],[ValidSurNum],[VeryGoodNum],[GoodNum],[AttBadNum],[ConnSpdBadNum],[QltyBadNum]'set @sql_tmpCols = '[日期],cast(right([座席号],6) as varchar(6)),[组别],[数据来源],cast([接听量] as smallint),cast([答帖数] as smallint),cast([调研笔数] as smallint),cast([有效笔数] as smallint),cast([非常满意] as smallint),cast([满意] as smallint),cast([对接通速度不满意] as smallint),cast([对服务质量不满意] as smallint),cast([对服务态度不满意] as smallint)'-- 临时表转化到目标表-- insert into dbo.CallIn(...) select ... from dbo.a-- insert into @targetTableName @sql_toCallIn from @tmpTableNameprint 'insert into ' + @targetTableName + '('+ @sql_targetCols +') select ' + @sql_tmpCols + ' from ' + @tmpTableNameEXEC ('insert into ' + @targetTableName + '('+ @sql_targetCols +') select ' + @sql_tmpCols + ' from ' + @tmpTableName)go

bcp格式文件

10.0201       SQLCHAR             0       0       ","     1     座席号                   Chinese_PRC_CI_AS2       SQLCHAR             0       0       ","     2     座席姓名                 Chinese_PRC_CI_AS3       SQLCHAR             0       0       ","     3     组别                     Chinese_PRC_CI_AS4       SQLCHAR             0       0       ","     4     数据来源                 Chinese_PRC_CI_AS5       SQLCHAR             0       0       ","     5     接听量                   Chinese_PRC_CI_AS6       SQLCHAR             0       0       ","     6     答帖数                   Chinese_PRC_CI_AS7       SQLCHAR             0       0       ","     7     调研笔数                 Chinese_PRC_CI_AS8       SQLCHAR             0       0       ","     8     调研笔数占比             Chinese_PRC_CI_AS9       SQLCHAR             0       0       ","     9     有效笔数                 Chinese_PRC_CI_AS10      SQLCHAR             0       0       ","     10    有效笔数占比             Chinese_PRC_CI_AS11      SQLCHAR             0       0       ","     11    非常满意                 Chinese_PRC_CI_AS12      SQLCHAR             0       0       ","     12    非常满意占比             Chinese_PRC_CI_AS13      SQLCHAR             0       0       ","     13    满意                     Chinese_PRC_CI_AS14      SQLCHAR             0       0       ","     14    满意占比                 Chinese_PRC_CI_AS15      SQLCHAR             0       0       ","     15    对接通速度不满意         Chinese_PRC_CI_AS16      SQLCHAR             0       0       ","     16    对接通速度不满意占比     Chinese_PRC_CI_AS17      SQLCHAR             0       0       ","     17    对服务质量不满意         Chinese_PRC_CI_AS18      SQLCHAR             0       0       ","     18    对服务质量不满意占比     Chinese_PRC_CI_AS19      SQLCHAR             0       0       ","     19    对服务态度不满意         Chinese_PRC_CI_AS20      SQLCHAR             0       0       "\r\n"   20    对服务态度不满意占比     Chinese_PRC_CI_AS

0 0