Re: Need Help Importing Data from Excel to My SQL Database

alvinstraight38@xxxxxxxxxxx (alvinstraight38@xxxxxxxxxxx) writes:
I am trying to import data from an Excel spread*** into my SQL
database. I am running SQL 2005.

I following Microsoft's instructions for creating a linked server, and
it appeared to work. However when I run this query:

SELECT * INTO test FROM OPENQUERY(EFORMS,
'SELECT * FROM [Form Tracker Baseline$]')


I get the following error:

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"EFORMS" reported an error. The provider did not give any information
about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "EFORMS".


Test is the table that will receive the imported data. Eforms is the
name of my linked server, and Form Tracker Baseline is the work***
name.

The error sounds like it can not locate my spread***. Any ideas why
I am getting this message?

To me it sounds like SQL Server cannot find Excel at all. Do you have
SQL Server on the same machine as you have Excel?

Is there an easier way to do this import?

If I had an Excel book that I wanted to import to SQL Server, I would
save it as a comma-separated file and then use BCP. Don't really know
whether that is easier, but it's the only way I know...


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.