Introduction:
In this article demonstrates,how to import a excel spreadsheet data’s into database tables using sql server.
Main:
We can easily achieve this using the below methods,
1.SSIS
2.Ad hoc queries
3.Excel COM methods
4.Bulk load
The below examble will save the excel data’s into xml format and will insert into database table.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[netImportExcelSheet]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[netImportExcelSheet]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[netGetColumnDataTypeName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[netGetColumnDataTypeName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[netGetColumnDataTypeName] (@TableName nvarchar(261), @ColumnName sysname)
RETURNS nvarchar(387)
AS
--Retrive the data type name for a column in the given table. The table can be a local/global temp table or a physcial table in the current DB
--SELECT dbo.[netGetColumnDataTypeName](N'[Production].[Product]','[Weight]')
--CREATE TABLE #Test1 (C1 nvarchar(200) not null); SELECT dbo.[netGetColumnDataTypeName](N'dbo.#Test1','C1'); DROP TABLE #Test1;
BEGIN
DECLARE @IsTempDataTable bit;
DECLARE @DataTypeName nvarchar(387);
DECLARE @OutputTableName sysname;
SELECT @TableName=ISNULL(@TableName,N''), @ColumnName=ISNULL(@ColumnName,N''),@DataTypeName=N'';
IF @TableName=N'' OR @ColumnName=N'' RETURN N'';
SET @OutputTableName=PARSENAME ( @TableName , 1 );
IF LEFT(@OutputTableName,1)=N'#'
BEGIN
SET @IsTempDataTable=1;
END
ELSE
BEGIN
SET @IsTempDataTable=0;
END
IF @IsTempDataTable=1
BEGIN
SELECT @DataTypeName=
(SELECT b.[name] FROM tempdb.sys.types b WHERE b.user_type_id=a.user_type_id)+
CASE WHEN a.user_type_id IN (231,239) -- nvarchar,nchar
THEN '(' + CASE WHEN a.max_length=-1 THEN N'max' ELSE cast (a.[max_length]/2 as nvarchar(25)) END + ')'
WHEN a.user_type_id IN (175,165,167) -- char,varbinary,varchar
THEN '(' + CASE WHEN a.max_length=-1 THEN N'max' ELSE cast (a.[max_length] as nvarchar(25)) END + ')'
WHEN a.user_type_id IN (106,108) -- decimal, numeric
THEN '('+cast (a.[precision] as nvarchar(25)) +N','+cast (a.[scale] as nvarchar(25)) +')'
WHEN a.user_type_id IN (241) -- xml
THEN CASE WHEN a.xml_collection_id=0 THEN N'' ELSE N'('+(SELECT s.name+N'.'+x.name FROM sys.xml_schema_collections x, sys.schemas s
WHERE x.xml_collection_id=a.xml_collection_id AND x.schema_id=s.schema_id) +N')' END
ELSE N''
END
FROM tempdb.sys.columns a
WHERE a.object_id=object_id('tempdb..'+@OutputTableName)
AND a.[name] collate database_default = @ColumnName
END
ELSE
BEGIN
SELECT @DataTypeName=
(SELECT b.[name] FROM sys.types b WHERE b.user_type_id=a.user_type_id)+
CASE WHEN a.user_type_id IN (231,239) -- nvarchar,nchar
THEN '(' + CASE WHEN a.max_length=-1 THEN N'max' ELSE cast (a.[max_length]/2 as nvarchar(25)) END + ')'
WHEN a.user_type_id IN (175,165,167) -- char,varbinary,varchar
THEN '(' + CASE WHEN a.max_length=-1 THEN N'max' ELSE cast (a.[max_length] as nvarchar(25)) END + ')'
WHEN a.user_type_id IN (106,108) -- decimal, numeric
THEN '('+cast (a.[precision] as nvarchar(25)) +N','+cast (a.[scale] as nvarchar(25)) +')'
WHEN a.user_type_id IN (241) -- xml
THEN CASE WHEN a.xml_collection_id=0 THEN N'' ELSE N'('+(SELECT s.name+N'.'+x.name FROM sys.xml_schema_collections x, sys.schemas s
WHERE x.xml_collection_id=a.xml_collection_id AND x.schema_id=s.schema_id) +N')' END
ELSE N''
END
FROM sys.columns a
WHERE a.object_id=object_id(@TableName)
AND a.[name]=@ColumnName
END
RETURN @DataTypeName;
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[netImportExcelSheet]
@ExcelFileName nvarchar(255),
@WorkSheetName nvarchar(255),
@OutputTableName nvarchar(257)=N'',
@FirstRowIsHeader bit=0,
@IdentityColumn sysname=N'',
@FirstRowToCheckDataType int=1,
@RowsToCheckDataType int=8,
@FirstRowToImportData int=0,
@RowsToImport int=0,
@Debug tinyint=0
AS
/*<RevisionHistory>
[1],2009-10-13,Peter He,Created
</RevisionHistory>*/
/*<Summary>
Import Excel XML spreadsheet into database. The spreadsheet must be saved in xml format (.xml).
1) Import from a spreadsheet and return all columns in a resultset; @FirstRowIsHeader must be 1;
2) Import from a spreadsheet and save all columns in a specified (global temporary/physical) table. @FirstRowIsHeader must be 1;
3) Import from a spreadsheet and save all columns in a precreated (local or global temporary/physical) table. @FirstRowIsHeader can be 1 or 0;
4) Import from a spreadsheet and save columns specified in a (global temporary/physical) table. @FirstRowIsHeader must be 1;
Feel free to change and use it. If you make changes/improvments, please send me a copy of the script to phe1129@hotmail.com.
</Summary>*/
/*<Parameters>
[1],@ExcelFileName: The Excel workbook file name, must be saved in xml format
[2],@WorkSheetName: Worksheet name to import
[3],@OutputTableName: The output data table. If empty string, return a result set.
If specified, the table can be pre-created. The table can be a local or global temporary table or a physical table;
If specified and the table does not exist, the SP will create it and save the imported data. The table must a global temp table or physical table;
[4],@FirstRowIsHeader: Whether the first row in the worksheet is header.1, first row is header; 0, not;
[5],@IdentityColumn: If specified data table does not exist, add an identity column with the name specified by @IdentityColumn when creating the table.
If @IdentityColumn is empty, no identity column is added; If specified data table pre-created, this parameter is ignored;
[6],@FirstRowToImportData: The first row to import data. Default is @FirstRowIsHeader+1
[7],@RowsToImport: Number of rows to import data. Default is 0 (all rows except the header)
[8],@FirstRowToCheckDataType: The first row used to check the data type. Default is @FirstRowToImportData. Not applicable if @OutputTableName is pre-created
[9],@RowsToCheckDataType: Number of rows to read to check the data type length. Default is 8. Not applicable if @OutputTableName is pre-created.
</Parameters>*/
/*<RunSample>
-- Import a worksheet and return all columns, first row must be header
EXEC dbo.[netImportExcelSheet]
@ExcelFileName=N'C:TestBook.xml',
@WorkSheetName=N'Sheet1',
@FirstRowIsHeader=1,
@Debug=1
</RunSample>*/
/*<RunSample>
-- Import a worksheet with all columns and save the data into the table ##Data, which does not exist, first row must be header
IF object_id('tempdb..##Data') IS NOT NULL
DROP TABLE ##Data;
EXEC dbo.[netImportExcelSheet]
@ExcelFileName=N'C:TestBook.xml',
@WorkSheetName=N'Sheet1',
@OutputTableName=N'##Data',
@FirstRowIsHeader=1,
@IdentityColumn=N'RecordID'
SELECT * FROM ##Data
IF object_id('tempdb..##Data') IS NOT NULL
DROP TABLE ##Data;
</RunSample>*/
/*<RunSample>
-- Import a worksheet with selected columns and save the data into the table #Data, which is precreated, first column must be header
IF object_id('tempdb..#Data') IS NOT NULL
DROP TABLE #Data;
CREATE TABLE #Data (Text2Col nvarchar(30), NumCol nvarchar(50) NULL, DateCol nvarchar(50) Null)
EXEC dbo.[netImportExcelSheet]
@ExcelFileName=N'C:TestBook.xml',
@WorkSheetName=N'Sheet1',
@OutputTableName=N'#Data',
@FirstRowIsHeader=1
SELECT * FROM #Data
IF object_id('tempdb..#Data') IS NOT NULL
DROP TABLE #Data;
</RunSample>*/
SET NOCOUNT ON
BEGIN TRY
SELECT @OutputTableName=ISNULL(LTRIM(RTRIM(@OutputTableName)),N''),
@WorkSheetName=ISNULL(LTRIM(RTRIM(@WorkSheetName)),N''),
@ExcelFileName=ISNULL(LTRIM(RTRIM(@ExcelFileName)),N''),
@FirstRowIsHeader=ISNULL(@FirstRowIsHeader,0),
@RowsToCheckDataType=ISNULL(@RowsToCheckDataType,0),
@RowsToImport=ISNULL(@RowsToImport,0);
-- Check valid excel file name is given
IF @ExcelFileName=N''
BEGIN
RAISERROR(N'Excel workbook name cannot be empty',16,1)
END
-- Check valid excel spreadsheet name is given
IF @WorkSheetName=N''
BEGIN
RAISERROR(N'Excel worksheet name cannot be empty',16,1)
END
DECLARE @SQL nvarchar(max)
, @InsertSql nvarchar(max)
, @WS xml
, @MsgText nvarchar(2048)
, @Loop int
, @DataTypeName sysname
, @ColumnName sysname
, @ColNum int
, @RowNum int
, @DetectDataType bit
, @IsTempDataTable bit
, @DataTableExists bit
, @StringLen int
, @LastRowToCheckDataType int
, @LastRowToImportData int
, @iDoc int;
SELECT @ColNum=0,@RowNum=0,@iDoc=0;
-- Define the raw data table for spreadsheet cells
CREATE TABLE #Raw$ExcelData (RID int identity not null, Data nvarchar(max) collate database_default NOT NULL, CellIndex int not NULL,RowID int not null PRIMARY KEY CLUSTERED(RowID,RID))
-- Define the temporary table for column mapping between data table and xml spreadsheet
CREATE TABLE #Columns (ColumnID int identity not null, ColumnName sysname collate database_default NOT NULL, ExcelColNum int NOT NULL, DataTypeName nvarchar(128) collate database_default NOT NULL DEFAULT N'',IsNullable tinyint not null)
-- First row to import default is @FirstRowIsHeader+1
IF ISNULL(@FirstRowToImportData,0)=0
BEGIN
SET @FirstRowToImportData=CAST(@FirstRowIsHeader as int)+1;
END
-- First row to check data type is @FirstRowToImportData
IF ISNULL(@FirstRowToCheckDataType,0)=0
BEGIN
SET @FirstRowToCheckDataType=@FirstRowToImportData;
END
SET @WS=NULL;
SET @SQL=N' SET @WorkSheet=NULL; SELECT @WorkSheet=CAST(BulkColumn as xml) FROM OPENROWSET (BULK '''+@ExcelFileName+N''', SINGLE_BLOB ) A;'
IF @Debug=1 PRINT @SQL;
exec sp_executesql @SQL,N'@WorkSheet xml OUTPUT',@WorkSheet=@WS OUTPUT;
-- Check valid XML spreadsheets are loaded
IF @WS IS NULL
BEGIN
RAISERROR(N'Failed to load the spcified workbook. Please verify the workbook is saved in xml format, the file is on the specified location, and SQL Server has permission to read it',16,1)
END
SELECT @DataTableExists=0, @IsTempDataTable=0, @InsertSql=N'';
IF @OutputTableName<>N''
BEGIN
SET @IsTempDataTable=CASE WHEN LEFT(@OutputTableName,1)=N'#' THEN 1 ELSE 0 END;
IF @IsTempDataTable=1
BEGIN
IF object_id('tempdb..'+@OutputTableName) IS NOT NULL
BEGIN
SET @DataTableExists=1;
END
END
ELSE
BEGIN
IF object_id(@OutputTableName) IS NOT NULL
BEGIN
SET @DataTableExists=1;
END
END
END
-- Get the column and row numbers
;WITH XMLNAMESPACES(DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
'urn:schemas-microsoft-com:office:office' AS o,
'http://www.w3.org/TR/REC-html40' AS html,
'urn:schemas-microsoft-com:office:excel' AS x,
'urn:schemas-microsoft-com:office:spreadsheet' AS ss
)
SELECT @ColNum=R.c.value(N'@ss:ExpandedColumnCount','int'),
@RowNum=R.c.value(N'@ss:ExpandedRowCount','int')
FROM @WS.nodes('Workbook/Worksheet[@ss:Name=sql:variable("@WorkSheetName")]/Table') R(c);
-- Empty worksheet
IF @ColNum=0 OR @RowNum=0
BEGIN
RAISERROR(N'Invalid Excel worksheet. No data in the worksheet',16,1)
END
-- Calculate last row to check data type
IF @RowsToCheckDataType>0
BEGIN
SELECT @LastRowToCheckDataType=@FirstRowToCheckDataType+@RowsToCheckDataType-1;
END
ELSE
BEGIN
--By default, check data type down to the last row
SELECT @LastRowToCheckDataType=@RowNum;
END
-- Calculate last row to import data
IF @RowsToImport>0
BEGIN
SELECT @LastRowToImportData=@FirstRowToImportData+@RowsToImport-1;
END
ELSE
BEGIN
--By default, import down to the last row
SELECT @LastRowToImportData=@RowNum;
END
-- First row must be headers if the data table is not pre-created
IF @FirstRowIsHeader=0 AND @DataTableExists=0
BEGIN
RAISERROR(N'Invalid Excel worksheet. The first row must be the column name of the data in the work sheet or provide the table definition to hold the data',16,1)
END
IF @DataTableExists=1
BEGIN
-- Get the column list and data types for the pre-created table
IF @IsTempDataTable=1
BEGIN
-- Temp table
INSERT #Columns(ColumnName,DataTypeName,ExcelColNum,IsNullable)
SELECT e.[name],dbo.netGetColumnDataTypeName(@OutputTableName,e.[name]),0,e.is_nullable
FROM tempdb.sys.columns e
WHERE e.object_id=object_id('tempdb..'+@OutputTableName)
END
ELSE
BEGIN
-- Physical table
INSERT #Columns(ColumnName,DataTypeName,ExcelColNum,IsNullable)
SELECT e.[name],dbo.netGetColumnDataTypeName(@OutputTableName,e.[name]),0,e.is_nullable
FROM sys.columns e
WHERE e.object_id=object_id(@OutputTableName)
END
IF @Debug=1 SELECT '#Columns' AS DataTableColumns,* FROM #Columns
END
IF @FirstRowIsHeader=1
BEGIN
IF @WS.exist('
declare default element namespace "urn:schemas-microsoft-com:office:spreadsheet";
declare namespace o="urn:schemas-microsoft-com:office:office";
declare namespace html="http://www.w3.org/TR/REC-html40";
declare namespace x="urn:schemas-microsoft-com:office:excel";
declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
/Workbook/Worksheet[@ss:Name=sql:variable("@WorkSheetName")]/Table/Row[1]')=1
BEGIN
-- Get header definitions
SET @Loop=1;
WHILE @Loop<=@ColNum
BEGIN
SELECT @ColumnName=N'';
;WITH XMLNAMESPACES(DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
'urn:schemas-microsoft-com:office:office' AS o,
'http://www.w3.org/TR/REC-html40' AS html,
'urn:schemas-microsoft-com:office:excel' AS x,
'urn:schemas-microsoft-com:office:spreadsheet' AS ss
)
SELECT @ColumnName=R.c.value(N'(Cell[sql:variable("@Loop")]/Data)[1]','nvarchar(128)')
FROM @WS.nodes('Workbook/Worksheet[@ss:Name=sql:variable("@WorkSheetName")]/Table/Row[1]') R(c);
IF ISNULL(@ColumnName,N'')<>N''
BEGIN
-- Skip empty columns
IF @DataTableExists=0
BEGIN
IF EXISTS(SELECT 1 FROM #Columns WHERE ColumnName=@ColumnName)
BEGIN
-- duplicated header name
RAISERROR(N'Invalid Excel worksheet. The column names defined in the first row are duplicated',16,1)
END
INSERT #Columns(ColumnName,ExcelColNum,IsNullable)
SELECT @ColumnName,@Loop,1
END
ELSE
BEGIN
-- Match table column with header by name for pre-created output data table
UPDATE #Columns SET ExcelColNum=@Loop WHERE ColumnName=@ColumnName;
END
END
SET @Loop=@Loop+1
END
IF @Debug=1 SELECT * FROM #Columns;
IF @DataTableExists=1
BEGIN
-- Check whether the data table has columns that is NOT NULL and is not defined in the Excel sheet
IF EXISTS(SELECT 1 FROM #Columns c WHERE c.ExcelColNum=0 AND c.IsNullable=0)
BEGIN
RAISERROR(N'Some non-nullable columns defined in the table [%s] does not exist in the Excel worksheet [%s].',16,1,@OutputTableName,@WorkSheetName);
END
END
END
ELSE
BEGIN
-- Header does not exist in the spreadsheet (empty spreadsheet)
RAISERROR(N'Invalid Excel worksheet. The first row must be the data headers',16,1)
END
END
ELSE
BEGIN
--Output table is given and pre-created. Table column and spreadsheet column is mapped by position:
UPDATE #Columns SET ExcelColNum=ColumnID
END
SET @DetectDataType=0;
IF EXISTS(SELECT 1 FROM #Columns WHERE DataTypeName=N'')
BEGIN
SET @DetectDataType=1;
END
-- parepare the xml document, need to specify the namespace
EXEC sp_xml_preparedocument @iDoc OUTPUT, @WS,'<Workbook xmlns:P="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40"/>';
-- Use dynamic sql to load raw cell data from specified worksheet becuase xpath in OPENXML does not support sql:variable() funciton.
SET @SQL=N'
INSERT #Raw$ExcelData(Data,CellIndex,RowID)
SELECT ISNULL(Data,N''''),ISNULL(CellIndex,0),DENSE_RANK ( ) OVER (ORDER BY RowID)'+
CASE WHEN @DetectDataType=1 THEN N'
FROM OPENXML(@iDoc,''P:Workbook/P:Worksheet[@ss:Name="'+@WorkSheetName+N'"]/P:Table/P:Row/P:Cell'',10)'
ELSE N'
FROM OPENXML(@iDoc,''P:Workbook/P:Worksheet[@ss:Name="'+@WorkSheetName+N'"]/P:Table/P:Row'+
CASE WHEN @FirstRowToImportData>1 OR @LastRowToImportData<@RowNum THEN
N'[position()>='+CAST(@FirstRowToImportData as nvarchar(30))+N' and position()<='+CAST(@LastRowToImportData as nvarchar(30))+N']'
ELSE N''
END+N'/P:Cell'',10)'
END + N'
WITH (Data nvarchar(max) ''P:Data[1]'',
CellIndex int ''@ss:Index'',
RowID int ''@mp:parentid'',
CellID int ''@mp:id'')
ORDER BY RowID,CellID'
IF @Debug=1 PRINT @SQL;
exec sp_executesql @SQL,N'@iDoc int',@iDoc=@iDoc;
-- Remove the xml document immediately and set the handle to 0;
EXEC sp_xml_removedocument @iDoc
SET @iDoc=0;
IF @Debug=1 SELECT * FROM #Raw$ExcelData ORDER BY RowID,RID
-- Assign the correct cell (column) index for each cell
DECLARE @Rows int
-- Set the CellIndex to 1 for the first cell for each row
UPDATE D SET CellIndex=1
FROM #Raw$ExcelData D,(SELECT R.RowID,MIN(R.RID) AS RID FROM #Raw$ExcelData R GROUP BY R.RowID ) F
WHERE D.RowID=F.RowID AND D.RID=F.RID AND D.CellIndex=0
SET @Rows=ISNULL(@@ROWCOUNT,0)
-- Set the CellInded to be 1 plus the CellIndex of the previous cell
WHILE @Rows>0
BEGIN
UPDATE D SET CellIndex=P.CellIndex+1
FROM #Raw$ExcelData D,#Raw$ExcelData P
WHERE D.RowID=P.RowID AND D.RID=P.RID+1
AND D.CellIndex=0 AND P.CellIndex>0
SET @Rows=ISNULL(@@ROWCOUNT,0)
END
IF @Debug=1 SELECT * FROM #Raw$ExcelData ORDER BY RowID,RID
IF @DetectDataType=1
BEGIN
-- detect cell data type (the length of the string)
SELECT @Loop=MIN(ColumnID) FROM #Columns WHERE DataTypeName=N'';
WHILE @Loop IS NOT NULL
BEGIN
SET @StringLen=0;
SELECT @StringLen=ISNULL(MAX(LEN([Data])),100)
FROM #Raw$ExcelData
WHERE RowID BETWEEN @FirstRowToCheckDataType AND @LastRowToCheckDataType
AND CellIndex=@Loop
IF @StringLen>255
BEGIN
-- use nvarchar(max) if any cell exceeds 255 chars
SELECT @DataTypeName=N'nvarchar(max)'
END
ELSE
BEGIN
-- set the string to the maximum length of the cell in the column
SELECT @DataTypeName=N'nvarchar('+CAST(@StringLen as nvarchar(25))+N')'
END
UPDATE #Columns SET DataTypeName=@DataTypeName WHERE ColumnID=@Loop
SELECT @Loop=MIN(ColumnID) FROM #Columns WHERE ColumnID>@Loop;
END
IF @FirstRowToImportData>1
BEGIN
-- delete rows not asked to import
DELETE #Raw$ExcelData WHERE RowID<@FirstRowToImportData
END
END
IF @Debug=1 SELECT * FROM #Columns ORDER BY ColumnID
IF @LastRowToImportData<@RowNum
BEGIN
-- delete rows not asked to import
DELETE #Raw$ExcelData WHERE RowID>@LastRowToImportData
END
IF @OutputTableName<>N''
BEGIN
IF @DataTableExists=0
BEGIN
-- Create the output data table if specified and not pre-created
SET @SQL=N'
CREATE TABLE '+@OutputTableName+N' ( '+CASE WHEN @IdentityColumn<>N'' THEN @IdentityColumn+N' int identity not null,' ELSE N'' END;
SELECT @Loop=MIN(ColumnID) FROM #Columns;
WHILE @Loop IS NOT NULL
BEGIN
SELECT @SQL=@SQL+ColumnName+N' '+ DataTypeName+CASE @IsTempDataTable WHEN 1 THEN N' collate database_default' ELSE N'' END + N' NULL,' FROM #Columns WHERE ColumnID=@Loop
SELECT @Loop=MIN(ColumnID) FROM #Columns WHERE ColumnID>@Loop;
END
SET @SQL=LEFT(@SQL,LEN(@SQL)-1)+N')';
IF @Debug=1 PRINT @SQL;
exec sp_executesql @SQL;
END
END
SET @InsertSql=N'';
IF @OutputTableName<>N''
BEGIN
-- Build INSERT OutputTable ... SELECT ... statement
SELECT @InsertSql=@InsertSql+ColumnName+N','
FROM #Columns
ORDER BY ExcelColNum;
SET @InsertSql=LEFT(@InsertSql,LEN(@InsertSql)-1)
SET @InsertSql=N'INSERT '+@OutputTableName+'('+@InsertSql+N')'+nchar(13)+nchar(10)+N'SELECT '+@InsertSql+N' FROM ('+nchar(13)+nchar(10);
END
DECLARE @CList nvarchar(max), @PList nvarchar(max)
SELECT @CList=N'',@PList=N'';
-- Build the select Column list (@Clist) and the pivot column list (PList)
SELECT @CList=@CList+N'CAST(['+CAST(ExcelColNum as nvarchar(25))+N'] AS '+DataTypeName+N') AS ['+ColumnName+N'],',
@PList=@PList+N'['+CAST(ExcelColNum as nvarchar(25))+N'],'
FROM #Columns
ORDER BY ExcelColNum;
SELECT @CList=LEFT(@CList,LEN(@CList)-1),@PList=LEFT(@PList,LEN(@PList)-1)
-- Pivot the cell into rows and save to output table or output a record set
SET @SQL=@InsertSql+N'
SELECT RowID AS RowNumber,'+
@CList+N'
FROM (SELECT RowID,Data,CellIndex FROM #Raw$ExcelData) p
PIVOT
(
MAX (Data)
FOR CellIndex IN
( '+@PList+')
) AS pvt'+CASE WHEN @InsertSql=N'' THEN N'' ELSE N') T' END
/*
-- xuqery is slow and hard to arrange the cell index for al rows
-- The follwoing code works only if all cells have value
BEGIN
SET @InsertSql=@InsertSql+N'
SELECT';
SELECT @Loop=MIN(ColumnID) FROM #Columns;
WHILE @Loop IS NOT NULL
BEGIN
SELECT @DataTypeName=DataTypeName,@ExcelColNum=ExcelColNum,@ColumnName=ColumnName
FROM #Columns
WHERE ColumnID=@Loop;
SELECT @InsertSql=@InsertSql+N'
R.c.value(N''(Cell['+CAST(@ExcelColNum as nvarchar(30))+N']/Data)[1]'','''+@DataTypeName+N''')'+ CASE WHEN @OutputTableName=N'' THEN N' AS ['+@ColumnName+N']' ELSE N',' END
SELECT @Loop=MIN(ColumnID) FROM #Columns WHERE ColumnID>@Loop;
END
SET @InsertSql=LEFT(@InsertSql,LEN(@InsertSql)-1);
SET @SQL=@SQL+N'
;WITH XMLNAMESPACES(DEFAULT ''urn:schemas-microsoft-com:office:spreadsheet'',
''urn:schemas-microsoft-com:office:office'' AS o,
''http://www.w3.org/TR/REC-html40'' AS html,
''urn:schemas-microsoft-com:office:excel'' AS x,
''urn:schemas-microsoft-com:office:spreadsheet'' AS ss
)'+nchar(13)+nchar(10)+@InsertSql+N'
FROM @WorkSheet.nodes(''Workbook/Worksheet[@ss:Name=sql:variable("@WorkSheetName")]/Table/Row'+CASE WHEN @FirstRowIsHeader=1 THEN N'[position()>1]' ELSE N'' END+''') R(c)'
END
*/
IF @Debug=1 PRINT @SQL;
EXEC(@SQL);
END TRY
BEGIN CATCH
IF XACT_STATE()<>0 ROLLBACK TRAN
IF @iDoc>0
BEGIN
EXEC sp_xml_removedocument @iDoc;
SET @iDoc=0;
END
SET @MsgText=ERROR_MESSAGE()
PRINT @MsgText+' at line '+CAST(ERROR_LINE() as nvarchar(30))
RAISERROR(@MsgText,16,1)
RETURN -1
END CATCH
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[netImportExcelSheet]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[netImportExcelSheet] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[netGetColumnDataTypeName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[netGetColumnDataTypeName] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[netGetColumnDataTypeName] (@TableName nvarchar(261), @ColumnName sysname) RETURNS nvarchar(387) AS --Retrive the data type name for a column in the given table. The table can be a local/global temp table or a physcial table in the current DB --SELECT dbo.[netGetColumnDataTypeName](N'[Production].[Product]','[Weight]') --CREATE TABLE #Test1 (C1 nvarchar(200) not null); SELECT dbo.[netGetColumnDataTypeName](N'dbo.#Test1','C1'); DROP TABLE #Test1; BEGIN DECLARE @IsTempDataTable bit; DECLARE @DataTypeName nvarchar(387); DECLARE @OutputTableName sysname; SELECT @TableName=ISNULL(@TableName,N''), @ColumnName=ISNULL(@ColumnName,N''),@DataTypeName=N''; IF @TableName=N'' OR @ColumnName=N'' RETURN N''; SET @OutputTableName=PARSENAME ( @TableName , 1 ); IF LEFT(@OutputTableName,1)=N'#' BEGIN SET @IsTempDataTable=1; END ELSE BEGIN SET @IsTempDataTable=0; END IF @IsTempDataTable=1 BEGIN SELECT @DataTypeName= (SELECT b.[name] FROM tempdb.sys.types b WHERE b.user_type_id=a.user_type_id)+ CASE WHEN a.user_type_id IN (231,239) -- nvarchar,nchar THEN '(' + CASE WHEN a.max_length=-1 THEN N'max' ELSE cast (a.[max_length]/2 as nvarchar(25)) END + ')' WHEN a.user_type_id IN (175,165,167) -- char,varbinary,varchar THEN '(' + CASE WHEN a.max_length=-1 THEN N'max' ELSE cast (a.[max_length] as nvarchar(25)) END + ')' WHEN a.user_type_id IN (106,108) -- decimal, numeric THEN '('+cast (a.[precision] as nvarchar(25)) +N','+cast (a.[scale] as nvarchar(25)) +')' WHEN a.user_type_id IN (241) -- xml THEN CASE WHEN a.xml_collection_id=0 THEN N'' ELSE N'('+(SELECT s.name+N'.'+x.name FROM sys.xml_schema_collections x, sys.schemas s WHERE x.xml_collection_id=a.xml_collection_id AND x.schema_id=s.schema_id) +N')' END ELSE N'' END FROM tempdb.sys.columns a WHERE a.object_id=object_id('tempdb..'+@OutputTableName) AND a.[name] collate database_default = @ColumnName END ELSE BEGIN SELECT @DataTypeName= (SELECT b.[name] FROM sys.types b WHERE b.user_type_id=a.user_type_id)+ CASE WHEN a.user_type_id IN (231,239) -- nvarchar,nchar THEN '(' + CASE WHEN a.max_length=-1 THEN N'max' ELSE cast (a.[max_length]/2 as nvarchar(25)) END + ')' WHEN a.user_type_id IN (175,165,167) -- char,varbinary,varchar THEN '(' + CASE WHEN a.max_length=-1 THEN N'max' ELSE cast (a.[max_length] as nvarchar(25)) END + ')' WHEN a.user_type_id IN (106,108) -- decimal, numeric THEN '('+cast (a.[precision] as nvarchar(25)) +N','+cast (a.[scale] as nvarchar(25)) +')' WHEN a.user_type_id IN (241) -- xml THEN CASE WHEN a.xml_collection_id=0 THEN N'' ELSE N'('+(SELECT s.name+N'.'+x.name FROM sys.xml_schema_collections x, sys.schemas s WHERE x.xml_collection_id=a.xml_collection_id AND x.schema_id=s.schema_id) +N')' END ELSE N'' END FROM sys.columns a WHERE a.object_id=object_id(@TableName) AND a.[name]=@ColumnName END RETURN @DataTypeName; END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[netImportExcelSheet] @ExcelFileName nvarchar(255), @WorkSheetName nvarchar(255), @OutputTableName nvarchar(257)=N'', @FirstRowIsHeader bit=0, @IdentityColumn sysname=N'', @FirstRowToCheckDataType int=1, @RowsToCheckDataType int=8, @FirstRowToImportData int=0, @RowsToImport int=0, @Debug tinyint=0 AS /*<RevisionHistory> [1],2009-10-13,Peter He,Created </RevisionHistory>*/ /*<Summary> Import Excel XML spreadsheet into database. The spreadsheet must be saved in xml format (.xml). 1) Import from a spreadsheet and return all columns in a resultset; @FirstRowIsHeader must be 1; 2) Import from a spreadsheet and save all columns in a specified (global temporary/physical) table. @FirstRowIsHeader must be 1; 3) Import from a spreadsheet and save all columns in a precreated (local or global temporary/physical) table. @FirstRowIsHeader can be 1 or 0; 4) Import from a spreadsheet and save columns specified in a (global temporary/physical) table. @FirstRowIsHeader must be 1; Feel free to change and use it. If you make changes/improvments, please send me a copy of the script to phe1129@hotmail.com. </Summary>*/ /*<Parameters> [1],@ExcelFileName: The Excel workbook file name, must be saved in xml format [2],@WorkSheetName: Worksheet name to import [3],@OutputTableName: The output data table. If empty string, return a result set. If specified, the table can be pre-created. The table can be a local or global temporary table or a physical table; If specified and the table does not exist, the SP will create it and save the imported data. The table must a global temp table or physical table; [4],@FirstRowIsHeader: Whether the first row in the worksheet is header.1, first row is header; 0, not; [5],@IdentityColumn: If specified data table does not exist, add an identity column with the name specified by @IdentityColumn when creating the table. If @IdentityColumn is empty, no identity column is added; If specified data table pre-created, this parameter is ignored; [6],@FirstRowToImportData: The first row to import data. Default is @FirstRowIsHeader+1 [7],@RowsToImport: Number of rows to import data. Default is 0 (all rows except the header) [8],@FirstRowToCheckDataType: The first row used to check the data type. Default is @FirstRowToImportData. Not applicable if @OutputTableName is pre-created [9],@RowsToCheckDataType: Number of rows to read to check the data type length. Default is 8. Not applicable if @OutputTableName is pre-created. </Parameters>*/ /*<RunSample> -- Import a worksheet and return all columns, first row must be header EXEC dbo.[netImportExcelSheet] @ExcelFileName=N'C:TestBook.xml', @WorkSheetName=N'Sheet1', @FirstRowIsHeader=1, @Debug=1 </RunSample>*/ /*<RunSample> -- Import a worksheet with all columns and save the data into the table ##Data, which does not exist, first row must be header IF object_id('tempdb..##Data') IS NOT NULL DROP TABLE ##Data; EXEC dbo.[netImportExcelSheet] @ExcelFileName=N'C:TestBook.xml', @WorkSheetName=N'Sheet1', @OutputTableName=N'##Data', @FirstRowIsHeader=1, @IdentityColumn=N'RecordID' SELECT * FROM ##Data IF object_id('tempdb..##Data') IS NOT NULL DROP TABLE ##Data; </RunSample>*/ /*<RunSample> -- Import a worksheet with selected columns and save the data into the table #Data, which is precreated, first column must be header IF object_id('tempdb..#Data') IS NOT NULL DROP TABLE #Data; CREATE TABLE #Data (Text2Col nvarchar(30), NumCol nvarchar(50) NULL, DateCol nvarchar(50) Null) EXEC dbo.[netImportExcelSheet] @ExcelFileName=N'C:TestBook.xml', @WorkSheetName=N'Sheet1', @OutputTableName=N'#Data', @FirstRowIsHeader=1 SELECT * FROM #Data IF object_id('tempdb..#Data') IS NOT NULL DROP TABLE #Data; </RunSample>*/ SET NOCOUNT ON BEGIN TRY SELECT @OutputTableName=ISNULL(LTRIM(RTRIM(@OutputTableName)),N''), @WorkSheetName=ISNULL(LTRIM(RTRIM(@WorkSheetName)),N''), @ExcelFileName=ISNULL(LTRIM(RTRIM(@ExcelFileName)),N''), @FirstRowIsHeader=ISNULL(@FirstRowIsHeader,0), @RowsToCheckDataType=ISNULL(@RowsToCheckDataType,0), @RowsToImport=ISNULL(@RowsToImport,0); -- Check valid excel file name is given IF @ExcelFileName=N'' BEGIN RAISERROR(N'Excel workbook name cannot be empty',16,1) END -- Check valid excel spreadsheet name is given IF @WorkSheetName=N'' BEGIN RAISERROR(N'Excel worksheet name cannot be empty',16,1) END DECLARE @SQL nvarchar(max) , @InsertSql nvarchar(max) , @WS xml , @MsgText nvarchar(2048) , @Loop int , @DataTypeName sysname , @ColumnName sysname , @ColNum int , @RowNum int , @DetectDataType bit , @IsTempDataTable bit , @DataTableExists bit , @StringLen int , @LastRowToCheckDataType int , @LastRowToImportData int , @iDoc int; SELECT @ColNum=0,@RowNum=0,@iDoc=0; -- Define the raw data table for spreadsheet cells CREATE TABLE #Raw$ExcelData (RID int identity not null, Data nvarchar(max) collate database_default NOT NULL, CellIndex int not NULL,RowID int not null PRIMARY KEY CLUSTERED(RowID,RID)) -- Define the temporary table for column mapping between data table and xml spreadsheet CREATE TABLE #Columns (ColumnID int identity not null, ColumnName sysname collate database_default NOT NULL, ExcelColNum int NOT NULL, DataTypeName nvarchar(128) collate database_default NOT NULL DEFAULT N'',IsNullable tinyint not null) -- First row to import default is @FirstRowIsHeader+1 IF ISNULL(@FirstRowToImportData,0)=0 BEGIN SET @FirstRowToImportData=CAST(@FirstRowIsHeader as int)+1; END -- First row to check data type is @FirstRowToImportData IF ISNULL(@FirstRowToCheckDataType,0)=0 BEGIN SET @FirstRowToCheckDataType=@FirstRowToImportData; END SET @WS=NULL; SET @SQL=N' SET @WorkSheet=NULL; SELECT @WorkSheet=CAST(BulkColumn as xml) FROM OPENROWSET (BULK '''+@ExcelFileName+N''', SINGLE_BLOB ) A;' IF @Debug=1 PRINT @SQL; exec sp_executesql @SQL,N'@WorkSheet xml OUTPUT',@WorkSheet=@WS OUTPUT; -- Check valid XML spreadsheets are loaded IF @WS IS NULL BEGIN RAISERROR(N'Failed to load the spcified workbook. Please verify the workbook is saved in xml format, the file is on the specified location, and SQL Server has permission to read it',16,1) END SELECT @DataTableExists=0, @IsTempDataTable=0, @InsertSql=N''; IF @OutputTableName<>N'' BEGIN SET @IsTempDataTable=CASE WHEN LEFT(@OutputTableName,1)=N'#' THEN 1 ELSE 0 END; IF @IsTempDataTable=1 BEGIN IF object_id('tempdb..'+@OutputTableName) IS NOT NULL BEGIN SET @DataTableExists=1; END END ELSE BEGIN IF object_id(@OutputTableName) IS NOT NULL BEGIN SET @DataTableExists=1; END END END -- Get the column and row numbers ;WITH XMLNAMESPACES(DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet', 'urn:schemas-microsoft-com:office:office' AS o, 'http://www.w3.org/TR/REC-html40' AS html, 'urn:schemas-microsoft-com:office:excel' AS x, 'urn:schemas-microsoft-com:office:spreadsheet' AS ss ) SELECT @ColNum=R.c.value(N'@ss:ExpandedColumnCount','int'), @RowNum=R.c.value(N'@ss:ExpandedRowCount','int') FROM @WS.nodes('Workbook/Worksheet[@ss:Name=sql:variable("@WorkSheetName")]/Table') R(c); -- Empty worksheet IF @ColNum=0 OR @RowNum=0 BEGIN RAISERROR(N'Invalid Excel worksheet. No data in the worksheet',16,1) END -- Calculate last row to check data type IF @RowsToCheckDataType>0 BEGIN SELECT @LastRowToCheckDataType=@FirstRowToCheckDataType+@RowsToCheckDataType-1; END ELSE BEGIN --By default, check data type down to the last row SELECT @LastRowToCheckDataType=@RowNum; END -- Calculate last row to import data IF @RowsToImport>0 BEGIN SELECT @LastRowToImportData=@FirstRowToImportData+@RowsToImport-1; END ELSE BEGIN --By default, import down to the last row SELECT @LastRowToImportData=@RowNum; END -- First row must be headers if the data table is not pre-created IF @FirstRowIsHeader=0 AND @DataTableExists=0 BEGIN RAISERROR(N'Invalid Excel worksheet. The first row must be the column name of the data in the work sheet or provide the table definition to hold the data',16,1) END IF @DataTableExists=1 BEGIN -- Get the column list and data types for the pre-created table IF @IsTempDataTable=1 BEGIN -- Temp table INSERT #Columns(ColumnName,DataTypeName,ExcelColNum,IsNullable) SELECT e.[name],dbo.netGetColumnDataTypeName(@OutputTableName,e.[name]),0,e.is_nullable FROM tempdb.sys.columns e WHERE e.object_id=object_id('tempdb..'+@OutputTableName) END ELSE BEGIN -- Physical table INSERT #Columns(ColumnName,DataTypeName,ExcelColNum,IsNullable) SELECT e.[name],dbo.netGetColumnDataTypeName(@OutputTableName,e.[name]),0,e.is_nullable FROM sys.columns e WHERE e.object_id=object_id(@OutputTableName) END IF @Debug=1 SELECT '#Columns' AS DataTableColumns,* FROM #Columns END IF @FirstRowIsHeader=1 BEGIN IF @WS.exist(' declare default element namespace "urn:schemas-microsoft-com:office:spreadsheet"; declare namespace o="urn:schemas-microsoft-com:office:office"; declare namespace html="http://www.w3.org/TR/REC-html40"; declare namespace x="urn:schemas-microsoft-com:office:excel"; declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet"; /Workbook/Worksheet[@ss:Name=sql:variable("@WorkSheetName")]/Table/Row[1]')=1 BEGIN -- Get header definitions SET @Loop=1; WHILE @Loop<=@ColNum BEGIN SELECT @ColumnName=N''; ;WITH XMLNAMESPACES(DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet', 'urn:schemas-microsoft-com:office:office' AS o, 'http://www.w3.org/TR/REC-html40' AS html, 'urn:schemas-microsoft-com:office:excel' AS x, 'urn:schemas-microsoft-com:office:spreadsheet' AS ss ) SELECT @ColumnName=R.c.value(N'(Cell[sql:variable("@Loop")]/Data)[1]','nvarchar(128)') FROM @WS.nodes('Workbook/Worksheet[@ss:Name=sql:variable("@WorkSheetName")]/Table/Row[1]') R(c); IF ISNULL(@ColumnName,N'')<>N'' BEGIN -- Skip empty columns IF @DataTableExists=0 BEGIN IF EXISTS(SELECT 1 FROM #Columns WHERE ColumnName=@ColumnName) BEGIN -- duplicated header name RAISERROR(N'Invalid Excel worksheet. The column names defined in the first row are duplicated',16,1) END INSERT #Columns(ColumnName,ExcelColNum,IsNullable) SELECT @ColumnName,@Loop,1 END ELSE BEGIN -- Match table column with header by name for pre-created output data table UPDATE #Columns SET ExcelColNum=@Loop WHERE ColumnName=@ColumnName; END END SET @Loop=@Loop+1 END IF @Debug=1 SELECT * FROM #Columns; IF @DataTableExists=1 BEGIN -- Check whether the data table has columns that is NOT NULL and is not defined in the Excel sheet IF EXISTS(SELECT 1 FROM #Columns c WHERE c.ExcelColNum=0 AND c.IsNullable=0) BEGIN RAISERROR(N'Some non-nullable columns defined in the table [%s] does not exist in the Excel worksheet [%s].',16,1,@OutputTableName,@WorkSheetName); END END END ELSE BEGIN -- Header does not exist in the spreadsheet (empty spreadsheet) RAISERROR(N'Invalid Excel worksheet. The first row must be the data headers',16,1) END END ELSE BEGIN --Output table is given and pre-created. Table column and spreadsheet column is mapped by position: UPDATE #Columns SET ExcelColNum=ColumnID END SET @DetectDataType=0; IF EXISTS(SELECT 1 FROM #Columns WHERE DataTypeName=N'') BEGIN SET @DetectDataType=1; END -- parepare the xml document, need to specify the namespace EXEC sp_xml_preparedocument @iDoc OUTPUT, @WS,'<Workbook xmlns:P="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"/>'; -- Use dynamic sql to load raw cell data from specified worksheet becuase xpath in OPENXML does not support sql:variable() funciton. SET @SQL=N' INSERT #Raw$ExcelData(Data,CellIndex,RowID) SELECT ISNULL(Data,N''''),ISNULL(CellIndex,0),DENSE_RANK ( ) OVER (ORDER BY RowID)'+ CASE WHEN @DetectDataType=1 THEN N' FROM OPENXML(@iDoc,''P:Workbook/P:Worksheet[@ss:Name="'+@WorkSheetName+N'"]/P:Table/P:Row/P:Cell'',10)' ELSE N' FROM OPENXML(@iDoc,''P:Workbook/P:Worksheet[@ss:Name="'+@WorkSheetName+N'"]/P:Table/P:Row'+ CASE WHEN @FirstRowToImportData>1 OR @LastRowToImportData<@RowNum THEN N'[position()>='+CAST(@FirstRowToImportData as nvarchar(30))+N' and position()<='+CAST(@LastRowToImportData as nvarchar(30))+N']' ELSE N'' END+N'/P:Cell'',10)' END + N' WITH (Data nvarchar(max) ''P:Data[1]'', CellIndex int ''@ss:Index'', RowID int ''@mp:parentid'', CellID int ''@mp:id'') ORDER BY RowID,CellID' IF @Debug=1 PRINT @SQL; exec sp_executesql @SQL,N'@iDoc int',@iDoc=@iDoc; -- Remove the xml document immediately and set the handle to 0; EXEC sp_xml_removedocument @iDoc SET @iDoc=0; IF @Debug=1 SELECT * FROM #Raw$ExcelData ORDER BY RowID,RID -- Assign the correct cell (column) index for each cell DECLARE @Rows int -- Set the CellIndex to 1 for the first cell for each row UPDATE D SET CellIndex=1 FROM #Raw$ExcelData D,(SELECT R.RowID,MIN(R.RID) AS RID FROM #Raw$ExcelData R GROUP BY R.RowID ) F WHERE D.RowID=F.RowID AND D.RID=F.RID AND D.CellIndex=0 SET @Rows=ISNULL(@@ROWCOUNT,0) -- Set the CellInded to be 1 plus the CellIndex of the previous cell WHILE @Rows>0 BEGIN UPDATE D SET CellIndex=P.CellIndex+1 FROM #Raw$ExcelData D,#Raw$ExcelData P WHERE D.RowID=P.RowID AND D.RID=P.RID+1 AND D.CellIndex=0 AND P.CellIndex>0 SET @Rows=ISNULL(@@ROWCOUNT,0) END IF @Debug=1 SELECT * FROM #Raw$ExcelData ORDER BY RowID,RID IF @DetectDataType=1 BEGIN -- detect cell data type (the length of the string) SELECT @Loop=MIN(ColumnID) FROM #Columns WHERE DataTypeName=N''; WHILE @Loop IS NOT NULL BEGIN SET @StringLen=0; SELECT @StringLen=ISNULL(MAX(LEN([Data])),100) FROM #Raw$ExcelData WHERE RowID BETWEEN @FirstRowToCheckDataType AND @LastRowToCheckDataType AND CellIndex=@Loop IF @StringLen>255 BEGIN -- use nvarchar(max) if any cell exceeds 255 chars SELECT @DataTypeName=N'nvarchar(max)' END ELSE BEGIN -- set the string to the maximum length of the cell in the column SELECT @DataTypeName=N'nvarchar('+CAST(@StringLen as nvarchar(25))+N')' END UPDATE #Columns SET DataTypeName=@DataTypeName WHERE ColumnID=@Loop SELECT @Loop=MIN(ColumnID) FROM #Columns WHERE ColumnID>@Loop; END IF @FirstRowToImportData>1 BEGIN -- delete rows not asked to import DELETE #Raw$ExcelData WHERE RowID<@FirstRowToImportData END END IF @Debug=1 SELECT * FROM #Columns ORDER BY ColumnID IF @LastRowToImportData<@RowNum BEGIN -- delete rows not asked to import DELETE #Raw$ExcelData WHERE RowID>@LastRowToImportData END IF @OutputTableName<>N'' BEGIN IF @DataTableExists=0 BEGIN -- Create the output data table if specified and not pre-created SET @SQL=N' CREATE TABLE '+@OutputTableName+N' ( '+CASE WHEN @IdentityColumn<>N'' THEN @IdentityColumn+N' int identity not null,' ELSE N'' END; SELECT @Loop=MIN(ColumnID) FROM #Columns; WHILE @Loop IS NOT NULL BEGIN SELECT @SQL=@SQL+ColumnName+N' '+ DataTypeName+CASE @IsTempDataTable WHEN 1 THEN N' collate database_default' ELSE N'' END + N' NULL,' FROM #Columns WHERE ColumnID=@Loop SELECT @Loop=MIN(ColumnID) FROM #Columns WHERE ColumnID>@Loop; END SET @SQL=LEFT(@SQL,LEN(@SQL)-1)+N')'; IF @Debug=1 PRINT @SQL; exec sp_executesql @SQL; END END SET @InsertSql=N''; IF @OutputTableName<>N'' BEGIN -- Build INSERT OutputTable ... SELECT ... statement SELECT @InsertSql=@InsertSql+ColumnName+N',' FROM #Columns ORDER BY ExcelColNum; SET @InsertSql=LEFT(@InsertSql,LEN(@InsertSql)-1) SET @InsertSql=N'INSERT '+@OutputTableName+'('+@InsertSql+N')'+nchar(13)+nchar(10)+N'SELECT '+@InsertSql+N' FROM ('+nchar(13)+nchar(10); END DECLARE @CList nvarchar(max), @PList nvarchar(max) SELECT @CList=N'',@PList=N''; -- Build the select Column list (@Clist) and the pivot column list (PList) SELECT @CList=@CList+N'CAST(['+CAST(ExcelColNum as nvarchar(25))+N'] AS '+DataTypeName+N') AS ['+ColumnName+N'],', @PList=@PList+N'['+CAST(ExcelColNum as nvarchar(25))+N'],' FROM #Columns ORDER BY ExcelColNum; SELECT @CList=LEFT(@CList,LEN(@CList)-1),@PList=LEFT(@PList,LEN(@PList)-1) -- Pivot the cell into rows and save to output table or output a record set SET @SQL=@InsertSql+N' SELECT RowID AS RowNumber,'+ @CList+N' FROM (SELECT RowID,Data,CellIndex FROM #Raw$ExcelData) p PIVOT ( MAX (Data) FOR CellIndex IN ( '+@PList+') ) AS pvt'+CASE WHEN @InsertSql=N'' THEN N'' ELSE N') T' END /* -- xuqery is slow and hard to arrange the cell index for al rows -- The follwoing code works only if all cells have value BEGIN SET @InsertSql=@InsertSql+N' SELECT'; SELECT @Loop=MIN(ColumnID) FROM #Columns; WHILE @Loop IS NOT NULL BEGIN SELECT @DataTypeName=DataTypeName,@ExcelColNum=ExcelColNum,@ColumnName=ColumnName FROM #Columns WHERE ColumnID=@Loop; SELECT @InsertSql=@InsertSql+N' R.c.value(N''(Cell['+CAST(@ExcelColNum as nvarchar(30))+N']/Data)[1]'','''+@DataTypeName+N''')'+ CASE WHEN @OutputTableName=N'' THEN N' AS ['+@ColumnName+N']' ELSE N',' END SELECT @Loop=MIN(ColumnID) FROM #Columns WHERE ColumnID>@Loop; END SET @InsertSql=LEFT(@InsertSql,LEN(@InsertSql)-1); SET @SQL=@SQL+N' ;WITH XMLNAMESPACES(DEFAULT ''urn:schemas-microsoft-com:office:spreadsheet'', ''urn:schemas-microsoft-com:office:office'' AS o, ''http://www.w3.org/TR/REC-html40'' AS html, ''urn:schemas-microsoft-com:office:excel'' AS x, ''urn:schemas-microsoft-com:office:spreadsheet'' AS ss )'+nchar(13)+nchar(10)+@InsertSql+N' FROM @WorkSheet.nodes(''Workbook/Worksheet[@ss:Name=sql:variable("@WorkSheetName")]/Table/Row'+CASE WHEN @FirstRowIsHeader=1 THEN N'[position()>1]' ELSE N'' END+''') R(c)' END */ IF @Debug=1 PRINT @SQL; EXEC(@SQL); END TRY BEGIN CATCH IF XACT_STATE()<>0 ROLLBACK TRAN IF @iDoc>0 BEGIN EXEC sp_xml_removedocument @iDoc; SET @iDoc=0; END SET @MsgText=ERROR_MESSAGE() PRINT @MsgText+' at line '+CAST(ERROR_LINE() as nvarchar(30)) RAISERROR(@MsgText,16,1) RETURN -1 END CATCH |
Conclusion:
Hope this helps,
Happy Coding.
This is an awesome article, I will be sure to add this blog to my bookmarks
Yes, that is true, I agree with you, but I am not sure if there are no other options.
found your site on del.icio.us these days and really liked it.. i bookmarked it and will probably be back to check it out some more later
hey,this is Chester Schlesselman,just discovered your web-site on google and i must say this blog is great.may I share some of the information found in your post to my local friends?i’m not sure and what you think?anyhow,Many thanks!
I really dig what you write on here. I try and come back to it every day so keep up the good posts!
I just wanted to comment and say that I really enjoyed reading your blog post here. It was very informative and I also digg the way you write! Keep it up and I’ll be back to read more in the future
This is a great article thanks for sharing this informative information.. I will visit your blog regularly for some latest post.
In a search on further information I stumbled on this website, what I found here was simply just cool, thanks alot to be and stay here MODS.
to help me in my Tasks .