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.
Posted
Updated 22-May-12 10:17am
v2
Comments
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
- MISCREPORTS
- EXCELUPLOADS
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.
   
Comments
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'
EXEC(@sql)
   
Comments
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