/*导入多个excel入数据库
 1，用xp_cmdshell获取所有excel的名称
 2, 因sheet需要指定，要获取其每个文件要导入的第一个sheet名称
 3，利用OpenDataSource上传excel数据
*/

--excel所在路径
DECLARE @filepath NVARCHAR(1000)
SET @filepath = 'D:\nocode\liaoxiaoman\DDS\'

--获取所有excel名称
DROP TABLE duifang_file_dds
CREATE TABLE duifang_file_dds([filename] NVARCHAR(1000))

DECLARE @cmd VARCHAR(1000)
SET @cmd = N'dir "' + @filepath + '\*' + '*.xls" /B'
INSERT INTO duifang_file_dds
EXEC master..xp_cmdshell @cmd

--清除无效文件名称
DELETE a
FROM duifang_file_dds a
WHERE filename IS NULL

--加字段sheetname,ID,以便做调试
ALTER TABLE duifang_file_dds ADD sheetname NVARCHAR(50),ID INT IDENTITY(1,1)

--取出sheet名称 .此处最好用工具将所有excel的sheetname设置为统一
UPDATE a
--SET a.sheetname = replace(replace(replace(replace(dbo.SUBSTRING2(FILENAME,'绿瘦商贸','第',3),'第',''),'明细',''),'(',''),'（','')
SET a.sheetname = replace(replace(dbo.SUBSTRING2(FILENAME,'','第',3),'(',''),'（','')
FROM duifang_file_dds a

--格式化
--导入时不认点,故要将点替代为#
UPDATE a
SET a.sheetname = replace(replace(sheetname,'.','#'),' ','') + '$' --sheet以$结尾
FROM duifang_file_dds a

--首字母为数字，要前后加引号
UPDATE a
SET a.sheetname = CASE(ISNUMERIC(SUBSTRING(sheetname,1,1))) WHEN 1 THEN QUOTENAME(a.sheetname,'''') ELSE a.sheetname END
FROM duifang_file_dds a

SELECT *
FROM duifang_file_dds a

--建表
DROP TABLE duifang_bill_dds
SELECT   代收款,F2,F3,F4,F5, REPLICATE('',100) FileName INTO   duifang_bill_dds
FROM   OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source= "D:\nocode\liaoxiaoman\DDS\5.21第三批.xls";
Extended Properties= "Excel 5.0;HDR=no;IMEX=1;Excel 8.0";Persist Security Info=False ')...['5#21$']
WHERE 1=2

TRUNCATE TABLE duifang_bill_dds

DECLARE pcur CURSOR FOR
SELECT  filename,sheetname,ID
FROM duifang_file_dds
WHERE ID >=1
ORDER BY ID

DECLARE @filename NVARCHAR(1000), @sheetname NVARCHAR(50),@s NVARCHAR(1000),@ID INT

OPEN pcur
FETCH NEXT FROM pcur INTO @filename,@sheetname,@ID
WHILE @@FETCH_STATUS = 0
BEGIN
	--代收款转成VARCHAR(50),防止包裹号出现e+
	SET @S = 'INSERT duifang_bill_dds
	SELECT CONVERT(VARCHAR(50),convert(bigint,代收款)),F2,F3,F4,F5, ''' + @filename + ''' FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',
	''Data Source= "' + @filepath + @filename + '";
	Extended Properties= "Excel 5.0;HDR=no;IMEX=1;Excel 8.0";Persist Security Info=False '')...[' + @sheetname + ']
	WHERE ISNUMERIC(代收款) = 1'
	PRINT 'ID=' + CONVERT(VARCHAR(50),@ID) + CHAR(13) +@S
	EXEC (@s)
	FETCH NEXT FROM pcur INTO @filename,@sheetname,@ID
END
CLOSE pcur
DEALLOCATE pcur

--哪个文件没有导入
SELECT *
FROM duifang_file_dds a
WHERE  NOT EXISTS( SELECT 1 FROM duifang_bill_dds b WHERE a.FILENAME = b.filename )

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。