Export Excel into SQL table using Store procedure (Excel Name as Parameter)

Question

0.00/5 (No votes)

See more:

Use ExcelName as Parameter

SQL

create proc Excel
(
@ExcelName varchar(100)
)
As
Begin

if OBJECT_ID('temp') is not null
drop table temp
select * INTO Excel from openrowset ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=d:\Sharedd\'''+@ExcelName+''.XLSX,[Sheet1$])
End

or
use dynamic query.I got error as below.
How to Solve the Error:
(
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
) in Below Srcipt

SQL

--Exec SP_Excel 'File7.XLSB'

Alter PROC SP_Excel
(@ExcelName VARCHAR(100)
)
As
Begin
Declare @SQL VARCHAR(1000)
if OBJECT_ID('Table1') is not null
drop table Table1
SET @SQL= 'select * INTO Table1 from openrowset (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database=d:\Shared\EXCEL\'+@ExcelName+''',[Details$])'
EXEC(@SQL)
End

Posted 16-Apr-13 0:21am

Saranya Boopathi

Updated 16-Apr-13 1:39am

v3

Add a Solution

Comments

Maciej Los 16-Apr-13 6:23am

Not clear. What a question?

Saranya Boopathi 16-Apr-13 6:32am

I need Excelname as parameter in that SP or else use dynamic query

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-04-16T02:10:00

In both examples you delete table in which later you're trying to insert data.
INSERT[^] statement is wrong.

SQL

Alter PROC SP_Excel
     @ExcelName VARCHAR(255),
     @SheetName VARCHAR(50)
AS
BEGIN
    DECLARE @fExt VARCHAR(3)

    SET @fExt = RIGHT(@ExcelName, 4)

    IF (@fExt = '.xls')
    BEGIN
        INSERT INTO Table1 SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\Shared\EXCEL\' + @ExcelName + '', 'SELECT * FROM [' + @SheetName +'$]')
    ELSE
        INSERT INTO Table1 SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\Shared\EXCEL\' + @ExcelName + '','SELECT * FROM [' + @SheetName + '$]')
    END 
END

Note:
1) Not tested!
2) Column mapping must be the same, so, better way is to define columns:

SQL

INSERT INTO Table1 (Col1, Col2, Col3... ColN)
SELECT Col1, Col2, Col3... ColN
FROM OPENROWSET (...) AS T

[EDIT #1]
I tested this:

SQL

DECLARE @ExcelName VARCHAR(255)
DECLARE @SheetName VARCHAR(50) 
DECLARE @sql VARCHAR(MAX)

SET @ExcelName = 'VeryVeryVeryLongFileName.xls'
SET @SheetName = 'Sheet1'

SET @sql =  N'INSERT INTO Table1 SELECT * FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database=D:\Shared\EXCEL\' + @ExcelName + ',''SELECT * FROM [' + @SheetName + '$]'')'
PRINT @sql

and it should works fine, because it shows result as:

INSERT INTO Table1 SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\Shared\EXCEL\VeryVeryVeryLongFileName.xls,'SELECT * FROM [Sheet1$]')

Please, change the code as is shown above.
[/EDIT]