Using OPENROWSET to import Excel file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Using OPENROWSET to import Excel file

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-11-05 : 12:21:44

I'm trying to use the OPENROWSET function to import an Excel file into SQL but I'm getting the following error:


select * into dbo.P09_Import
from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Database=\\-...\P09.xlsx;', 'SELECT * FROM [P09$A2:BY]'


OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 100
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

What's the best way to solve this?

I tried to make the file a text file (so I could use bulk insert) but the data has some issues as it's being created by Access. The data is in every other row instead.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-05 : 12:27:01

you could make a SSIS package (try the import/export wizard) to do the work. That gives you a bit more control

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-11-05 : 12:27:46

Unfortunately, I don't have the SSIS package :/
Trying to automate the import process as much as possible.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-05 : 12:43:15

Actually, you probably do have SSIS. From SSMS, right-click on the database you want to use for your imported Excel files, select Tasks and Import data. Follow the wizard and save the resulting file after running it. That file is a SSIS package which you can further edit in SSDT. If you don't have SSDT installed, it's a free download here:

http://msdn.microsoft.com/en-ca/data/hh297027

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-11-05 : 12:45:33

Thanks! I actually found a way to just import from Access directly. I was attempting it earlier but it wasn't working. Played around with it and got the following to properly work:

select * from OPENdatasource('Microsoft.ACE.OLEDB.12.0', 'Data Source="\\....\Step1-Parse.accdb"')...[P09]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources