July 31, 2009 at 10:53 am
I have over 40 excel 2007 spreadsheets to import into a SQL 2008 table. I have found several suggestions that look something like this:
SELECT *
INTO xxxx
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0'
,'Excel 8.0;Database=D:\SharePointTempDocuments\ASA\CO_02_New_Chart_of_Accounts.xlsx'
,'SELECT * FROM [Posting$]'
)
I get the following 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. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
I'm assuming that the reference to the null server results for the access denied error but I'm not sure. I am logged into the SQL server as a domain administrator and the SQL service is started by another domain administrator.
This type of import will occur frequently in the future and I would really like to automate it. Any suggestions would be greatly appreciated.
Thanks
Chuck
July 31, 2009 at 11:30 am
I've gotten that error a few times. It usually means either the Excel file is open (me or another user), or that the account SQL is running under doesn't have permission for the file, or that the account I'm using doesn't have permission.
I've not had luck with "read" permissions, I've usually had to have the file somewhere that I have full access.
I've also had problems with network paths giving that error, but local paths not. It doesn't recognize mapped drives, so far as I know, but I think that gives a different error.
Of course, Excel files can also be password protected, so that could give the same error, but I'm assuming you've already checked that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 31, 2009 at 3:08 pm
Hi GSquared. Thanks for the response. I logged in as a domain administrator and the SQL service is started by a domain administrator. I checked the rights on the excel spreadsheet and increased domain users to full rights and I'm still getting the error. If it's a permissions issue I'm at a loss as to what it could be. I wondering if there is something wrong with the script. Maybe "Excel 8.0" I have not be able to determine what Excel 8.0 means. Is that the same as Excel 2007 or could it be looking for the wrong format?
Chuck
August 1, 2009 at 3:02 am
I converted the spreadsheet to Excel 2003 and tried the following script:
SELECT *
INTO #t
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;Database=D:\CO_07_New_Chart_of_Accounts.xls'
,'SELECT * FROM [Posting$]'
)
This worked fine. I then did some further searching and found the following recommendation:
USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
After running this the original script against the Excel 2007 spreadsheet worked fine.
Chuck
August 1, 2009 at 4:35 pm
My 2 cents. Can you not use SSIS to import the 47 spreadsheets? SSIS may be a cleaner and more efficient way to import this data in SQL Server.
August 2, 2009 at 3:14 am
Probably. I work for a varity of clients and personally work with SQL 2008 express so I don't have as much experience with SSIS as I would like. In that I work with various clients and accordingly various data sources, the scripting solution seemed the best solution and I don't think I can justify the cost with anything SSIS has to offer. The above solution allowed me to design a canned script that will allow me to import all 2003 or 2007 Excel spreadsheets from a given folder. With this I was able to import 39,000 line from 44 spreadsheets in about 30 seconds.
Chuck
August 2, 2009 at 9:05 am
Chuck.Evans
With this I was able to import 39,000 line from 44 spreadsheets in about 30 seconds.
For the benefit of others would you consider posting your code or submitting it as a script?
August 3, 2009 at 4:21 am
Most of the important part of the script was included above but here is the current version of the canned script I am retaining for future use.
--Excel 2007 – Make sure the 2007 Office System Driver: Data Connectivity Components are installed.
--Excel 2007 – Run the scripts below before trying to import from Excel 2007
USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
/* Excel 2007
The first part of this script imports all of the file names from a specified folder. The folder should contain only the
Excel spreadsheet you want to import. The second part imports all of the data from the specified named area of each
spreadsheet.
*/
declare @STR varchar(8000),@path varchar(1000),@file varchar(200),@area varchar(200)
select @path='<>'
select @area='NAMED AREA OR SHEET NAME'
if OBJECT_ID('tempdb..#t') is not null drop table #t
create table #t(line varchar(1000),depth int,isFile int)
insert into #t exec master..xp_dirTree @path,0,1
--select * from #t
if OBJECT_ID('tempdb..##t') is not null drop table ##t
declare a cursor for select line from #t order by line open a fetch next from a into @file
select @STR='SELECT * INTO ##t FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0''
,''Excel 12.0;Database='+@path+'\'+@file+'''
,''SELECT * FROM ['+@area+'$]'')' exec(@str)
fetch next from a into @file while @@FETCH_STATUS-1 begin
select @STR='insert ##t SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0''
,''Excel 12.0;Database='+@path+'\'+@file+'''
,''SELECT * FROM ['+@area+'$]'')' exec(@str)
fetch next from a into @file end close a deallocate a
select * from ##t
--Excel 2003 – Substitute the following @STR definitions in the above script
select @STR='SELECT * INTO ##t FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0''
,''Excel 8.0;Database='+@path+'\'+@file+'''
,''SELECT * FROM ['+@area+']'')' exec(@str)
select @STR='insert ##t SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0''
,''Excel 8.0;Database='+@path+'\'+@file+'''
,''SELECT * FROM ['+@area+']'')' exec(@str)
August 3, 2009 at 2:33 pm
Well done on solving it. And thanks for posting the solution.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply