Click here to Skip to main content
14,640,601 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi Friends...

I want to read excel dynamically using sql.

Declare @Provider nvarchar(100)
Declare @ConnectionString nvarchar(1000)
Declare @FileName  nvarchar(1000)
Declare @query nvarchar(4000)
Declare @collMonth nvarchar(6)

set @Provider= N'Microsoft.ACE.OLEDB.12.0'
set @ConnectionString=N'Excel 12.0 Xml;HDR=YES;Database='
--set @FileName=N'E:\SharedE\chidTest\2011_NS.xlsx'  
--set @FileName=N'E:\SharedE\chidTest\2011_NS_66b86021-93a8-4a4b-8fc6-0bb3c3500cd0_5-21-2012.xlsx'
set @FileName =N'E:\SharedE\chidTest\MisReportsTest\SysAdmin\MISCREPORTS\EXCELUPLOADS\2011_NS_66b86021-93a8-4a4b-8fc6-0bb3c3500cd0_5-21-2012.xlsx'
set @query=N'Select * From [Sheet1$]' 
set @collMonth=N'201104'

--SELECT * into #APColln FROM OPENROWSET(@FileName);
declare @sql  nvarchar(max)
set @ConnectionString = @ConnectionString + @FileName 

SET @sql  = 'SELECT * into #APColln FROM OPENROWSET(' + QUOTENAME(@Provider, '''') + ', ' + QUOTENAME(@ConnectionString, '''') + ', ' + QUOTENAME(@Query, '''') + ')'

print @sql

This is my Query.

I can read first 2 filenames.

Not able read 3 rd one.
Updated 22-May-12 10:17am
Arul R Ece 22-May-12 3:19am
When excel inside any folder not able to read.
Sunny_Kumar_ 22-May-12 6:08am
I've a little confusion, above in the question you said you're able to read first two files, and you also commented than no files from inside a folder you can read. Opposing stat ...please clarify !!
And also please share the error what you receive...
Corporal Agarn 22-May-12 13:44pm
Have you checked the permissions on the folder?
Rate this:
Please Sign up or sign in to vote.

Solution 1

Since the first two files come from a different directory than the last one, check the permissions on subdirectories:
- MisReportsTest
- SysAdmin
Also check that the file name is correct and the file isn't open when you run the query.

When going through the permissions, check that the credentials under which SQL Server service is run, have access.
Espen Harlinn 22-May-12 18:10pm
Good points :-D
Wendelius 23-May-12 0:40am
Thanks :)
Prasad_Kulkarni 23-May-12 0:25am
Good one +5!
Wendelius 23-May-12 0:41am
Thank you :)
Rate this:
Please Sign up or sign in to vote.

Solution 2

Try this:
SET @sql  = 'INSERT INTO #APColln (Field1, Field2, Fieldn...) ' +
        'SELECT * ' +
         'FROM OPENROWSET(' + @Provider + ', ' + @ConnectionString + ', ' + @Query +  ') AS DT'
Prasad_Kulkarni 23-May-12 0:26am
Good one +5!
Maciej Los 23-May-12 2:05am
Thank you, Prasad ;)

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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100