Exporting Excel to sql server Temporary table.

Question

I need to take all the values in an excel file to a temporary/physical table in SQL Server 2005. I don't need the Import export method. I tried the following linked server method:

SELECT * 
INTO db1.dbo.table1
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\renju.xls', 
    'SELECT * FROM [sheet1$]')

But it is returing an error as:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".

I'm using Excel 2003, and I've already added the linked server for "Microsoft.Jet.OLEDB.4.0."

Answer 1

Do you have column names in the first row?

Answer 2

Yes i'm having coloumn names in the first row.

Answer 3

I would try installing/re-installing the Microsoft Data Access Components (MDAC):

http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&displaylang=en

Answer 4

I'm unable to install MDAC_TYP.Its throwing an error "MDAC 2.8 RTM is incompatible with this version of Windows. All of its features are currently part of Windows. "

Answer 5

Integration Services / SSIS import and export wizard.

Answer 6

If you have a linked server set up you can use the following syntax:

select * from openquery(LinkedServerName, 'select * from [Sheet1$]')

Answer 7

Hi, did you fix this? I think table1 should not already be created for this to work, and table1 will have the same columns as the excel sheet. Is that what you want?

you can try using OpenDataSource if this hasn't been fixed. It will be a tad slower than OpenRowSet in performance:

 INSERT INTO db1.dbo.table1
      SELECT *
      FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                           'Data Source="c:\renju.xls";
                            Extended properties=Excel 8.0')...Sheet1$

You can also try Extended properties = Excel 5.0 if it doesn't work.