Insert from excel to sql using Microsoft.ACE.OLEDB.12.0
-
I am trying to create a procedur that inserts data from an excel-file into a table in sql server 2008 R2 using the Microsoft ACE OLEDB 12.0 engine...
CREATE procedure mySP
@excelfile varchar(200)
AS
DECLARE @sql varchar(1024)
SET @sql='SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0 Xml;HDR=YES;Database='+@excelfile+''','''+'SELECT * FROM (SELECT ROW_NUMBER() OVER() AS rowNum FROM [Reservdelar$]) AS Foo WHERE rowNum =>3'''+');'
INSERT INTO myTable(
NAME,
PERIOD,
TYPE,
NMBR,
VALUE
)EXEC (@sql)
Excelfile looks like this:
I want to insert into a db_table that has this type of structure:
NAME (varchar)
NMBR (varchar)
TYPE (int)
PERIOD(int)
VALUE (money)
I want to start reading from row 3.
In the db_column NAME, I want the data from column A in the excel file.
In the db_column NMBR, I want the data from column B in the excel file.
In the db_column TYPE, I want the data from column C in the excel file.
In the db_column PERIOD, I want the data from cell D2 in the excel file.
In the db_column VALUE, I want the data from column D in the exel file.
Next record created in Sql Server I want the above except PERIOD shall be read from cell E2 and VALUE shall be read from column E...
I also don't want to read from column P!!!
I have my hands tied here a little bit since I can't have the excel-file in any other way AND the SQL table also has to be the way it's designed.
Am I making any sense here..? :unsure:
-
Use a staging table:
CREATE procedure mySP@excelfile varchar(200)
AS
DECLARE @sql varchar(1024)
CREATE TABLE #MyLocalTempTable ([columns which nicely match the Excel file])
SET @sql='SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0 Xml;HDR=YES;Database='+@excelfile+''','''+'SELECT * FROM (SELECT ROW_NUMBER() OVER() AS rowNum FROM [Reservdelar$]) AS Foo WHERE rowNum =>3'''+');'
INSERT INTO #MyLocalTempTable(
NAME,
PERIOD,
TYPE,
NMBR,
VALUE
)EXEC (@sql)
INSERT INTO myTable(
NAME,
PERIOD,
TYPE,
NMBR,
VALUE
SELECT [the correct rows and columns]
FROM #MyLocalTempTable
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply