IF OBJECT_ID('TB')IS NOT NULL
DROP TABLE TB
GO
CREATE TABLE TB
(
ARRIVALAIRPORT NVARCHAR(4000),
[MCTTIME] NVARCHAR(4000),
MCTSTATUS NVARCHAR(4000),
DEPARTUREAIRPORT NVARCHAR(4000),
ARRIVALAIRLINE NVARCHAR(4000),
INCOMINGFLIGHT NVARCHAR(4000),
DEPARTUREAIRLINE NVARCHAR(4000),
OUTGOINGFLIGHT NVARCHAR(4000),
ARRIVALTERMINAL NVARCHAR(4000),
DEPARTURETERMINAL NVARCHAR(4000),
PREVIOUSCOUNTRY NVARCHAR(4000),
PREVIOUSCITY NVARCHAR(4000),
PREVIOUSAIRPORT NVARCHAR(4000),
NEXTCOUNTRY NVARCHAR(4000),
NEXTCITY NVARCHAR(4000),
NEXTAIRPORT NVARCHAR(4000),
EFFECTIVEFROM NVARCHAR(4000),
EFFECTIVETO NVARCHAR(4000)
)
IF OBJECT_ID('P_EXCELFILETODATA') IS NOT NULL
DROP PROC P_EXCELFILETODATA
GO
CREATE PROC P_EXCELFILETODATA
(
@PATH SYSNAME, --完全路径名
@FILTER SYSNAME='*.XLSX'--默认导EXCEL2007的
)
AS
--@PATH 完全路径名(D:\XXXX\)
--@FILTER 筛选的文件名选项以及文件类型(合并一起输入)
SET NOCOUNT ON
DECLARE @S NVARCHAR(4000),@SQL NVARCHAR(4000),@I INT--,@PATH NVARCHAR(4000),@FILTER SYSNAME
SET @I=1
SET @S='DIR '+@PATH+@FILTER+'/B'
CREATE TABLE #FILE(FLNAME SYSNAME NULL)
INSERT INTO #FILE EXEC MASTER..XP_CMDSHELL @S
DECLARE @FILE SYSNAME,@SHEETNAME SYSNAME,@SHEETLIKENAME SYSNAME,@COLUMNNAME SYSNAME
DECLARE CUR_FILENAME CURSOR
FOR
SELECT * FROM #FILE WHERE [FLNAME] IS NOT NULL AND [FLNAME]<>'找不到文件'
OPEN CUR_FILENAME
FETCH NEXT FROM CUR_FILENAME INTO @FILE
WHILE @@FETCH_STATUS=0
BEGIN
SET @SHEETNAME=LEFT(@FILE,CHARINDEX('.',@FILE)-1)
SET @SHEETLIKENAME=SUBSTRING(@SHEETNAME,5,LEN(@SHEETNAME))
--PRINT @SHEETNAME
SELECT @SQL = ISNULL(@SQL,'')+ 'SELECT * INTO TB'+ LTRIM(@I) +' FROM OPENROWSET(
''MICROSOFT.ACE.OLEDB.12.0'', ''EXCEL 12.0;HDR=NO;IMEX=1;DATABASE='+@PATH+@FILE+''',''SELECT * FROM ['+@SHEETNAME+'$]'') '
FETCH NEXT FROM CUR_FILENAME INTO @FILE
SET @I=@I+1
END
EXEC(@SQL)
CLOSE CUR_FILENAME
DEALLOCATE CUR_FILENAME
GO
--测试
EXEC P_EXCELFILETODATA 'D:\ExcelFiles\','*.XLSX'
GO
IF OBJECT_ID('TEMPDB..#VALUE_COLNAME') IS NOT NULL
DROP TABLE #VALUE_COLNAME
GO
CREATE TABLE #VALUE_COLNAME(ID INT ,VALUE NVARCHAR(4000),COLNAME NVARCHAR(8))
DECLARE @I INT,@J NVARCHAR(10)
SET @I=1
SET @J='TB1'
WHILE @I<=(SELECT COUNT(NAME) FROM SYSOBJECTS WHERE TYPE='U' AND NAME LIKE '%TB%' AND NAME<>'TB')
BEGIN
--拼接值列,列转行.
DECLARE @S1 NVARCHAR(MAX)
SELECT @S1=ISNULL(@S1+CHAR(10)+' UNION ALL '+CHAR(10)+'SELECT ','')
+'ID='+LTRIM(@I)+',LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),A.'+NAME+')))+LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),B.'+NAME+'))) AS VALUE,
'''+NAME+''' AS COLNAME
FROM '+@J+' A,'+@J+' B WHERE LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),A.F1)))=''Arrival'' AND LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),B.F1)))=''Airport'' '
FROM
TEMPDB.DBO.SYSCOLUMNS WHERE ID=OBJECT_ID('TEMPDB..'+@J+'')
AND NAME <>'ID'
ORDER BY COLID
--EXEC('SELECT '+@S1)
INSERT INTO #VALUE_COLNAME EXEC('SELECT '+@S1)
--SELECT * FROM #VALUE_COLNAME WHERE id=2
--PRINT @I
--拼接列对应
DECLARE @TB_COLNAME NVARCHAR(MAX),@EXCEL_COLNAME NVARCHAR(MAX)
SET @TB_COLNAME=''
SET @EXCEL_COLNAME=''
SELECT
@TB_COLNAME=ISNULL(@TB_COLNAME+',','')+S.NAME,
@EXCEL_COLNAME=ISNULL(@EXCEL_COLNAME+',','')+ISNULL(V.COLNAME,'''''')
--s.name,v.[COLNAME]
FROM
SYSCOLUMNS S
LEFT JOIN
#VALUE_COLNAME V ON S.NAME=V.VALUE
WHERE
S.ID=OBJECT_ID('TB')
AND
V.ID=@I
ORDER BY
S.COLID
SET @TB_COLNAME=RIGHT(@TB_COLNAME,LEN(@TB_COLNAME)-1)
SET @EXCEL_COLNAME=RIGHT(@EXCEL_COLNAME,LEN(@EXCEL_COLNAME)-1)
--插入数据
--PRINT 1
EXEC('INSERT INTO TB('+@TB_COLNAME+') SELECT '+@EXCEL_COLNAME+' FROM ' +@J+ ' WHERE LEN(LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),F1))))=3')
SET @I=@I+1
SET @J='TB'+LTRIM(@I)
END
下面的图片是EXCEL的格式: 要求批量导入这样的EXCEL,全部存放在一个文件夹中。导入每个EXCEL里面的Arrival栏位以下的数据: