Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

/*导入多个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 )