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

最新推荐文章于 2023-02-24 08:45:42 发布

tangtao_xp

最新推荐文章于 2023-02-24 08:45:42 发布

阅读量3.5k

点赞数

本文链接：https://blog.csdn.net/tangtao_xp/article/details/42599531

版权

先使用Convert Excel To CSV 29.12.28将不同sheet页excel批量转换为csv（该软件有命令行exe，可以使用bat批量转化），再利用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_db

CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255),WHICHDATE date)
go
*/


use icbc_report_db

declare @filename varchar(255),@path varchar(2000),@sql varchar(8000),@cmd varchar(1000),@ins_date date

declare @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: ' + @tmpTableName
print 'Target Table: ' + @targetTableName
print 'Format File: ' + @formatFileName

-- 清空临时表
print 'delete ' + @tmpTableName
EXEC ('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 @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
delete ALLFILENAMES where WHICHFILE is NULL

-- 保存文件日期到WHICHDATE
update 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 <> -1
		begin
		-- 采用文件格式跳过了临时表日期列
		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_date
		end
close c1
deallocate 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 @tmpTableName
print 'insert into ' + @targetTableName + '('+ @sql_targetCols +') select ' + @sql_tmpCols + ' from ' + @tmpTableName
EXEC ('insert into ' + @targetTableName + '('+ @sql_targetCols +') select ' + @sql_tmpCols + ' from ' + @tmpTableName)

go

bcp格式文件

10.0
20
1       SQLCHAR             0       0       ","     1     座席号                   Chinese_PRC_CI_AS
2       SQLCHAR             0       0       ","     2     座席姓名                 Chinese_PRC_CI_AS
3       SQLCHAR             0       0       ","     3     组别                     Chinese_PRC_CI_AS
4       SQLCHAR             0       0       ","     4     数据来源                 Chinese_PRC_CI_AS
5       SQLCHAR             0       0       ","     5     接听量                   Chinese_PRC_CI_AS
6       SQLCHAR             0       0       ","     6     答帖数                   Chinese_PRC_CI_AS
7       SQLCHAR             0       0       ","     7     调研笔数                 Chinese_PRC_CI_AS
8       SQLCHAR             0       0       ","     8     调研笔数占比             Chinese_PRC_CI_AS
9       SQLCHAR             0       0       ","     9     有效笔数                 Chinese_PRC_CI_AS
10      SQLCHAR             0       0       ","     10    有效笔数占比             Chinese_PRC_CI_AS
11      SQLCHAR             0       0       ","     11    非常满意                 Chinese_PRC_CI_AS
12      SQLCHAR             0       0       ","     12    非常满意占比             Chinese_PRC_CI_AS
13      SQLCHAR             0       0       ","     13    满意                     Chinese_PRC_CI_AS
14      SQLCHAR             0       0       ","     14    满意占比                 Chinese_PRC_CI_AS
15      SQLCHAR             0       0       ","     15    对接通速度不满意         Chinese_PRC_CI_AS
16      SQLCHAR             0       0       ","     16    对接通速度不满意占比     Chinese_PRC_CI_AS
17      SQLCHAR             0       0       ","     17    对服务质量不满意         Chinese_PRC_CI_AS
18      SQLCHAR             0       0       ","     18    对服务质量不满意占比     Chinese_PRC_CI_AS
19      SQLCHAR             0       0       ","     19    对服务态度不满意         Chinese_PRC_CI_AS
20      SQLCHAR             0       0       "\r\n"   20    对服务态度不满意占比     Chinese_PRC_CI_AS