Need Help Importing Data from Excel to My SQL Database

[alvinstraight38]

Hey guys,

I am trying to import data from an Excel spreadsheet 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 worksheet
name.

The error sounds like it can not locate my spreadsheet. Any ideas why
I am getting this message? Is there an easier way to do this import?

Thanks,

[Erland Sommarskog]

al*************@hotmail.com (al*************@hotmail.com) writes:

I am trying to import data from an Excel spreadsheet 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 worksheet
name.

The error sounds like it can not locate my spreadsheet. 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[aj]

Erland Sommarskog wrote:

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...

SSIS (ala OLE DB). Works like a charm....

aj

[alvinstraight38]

On Jan 17, 10:13*am, aj <[e-mail]:

Erland Sommarskog wrote:

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...

SSIS (ala OLE DB). *Works like a charm....

aj

Ahhh just my luck. I am on the free version of 2005 so it looks like
SSIS is not available.

[Jim Johannsen]

On Wed, 16 Jan 2008 10:13:10 -0800, al*************@hotmail.com wrote:

Hey guys,

I am trying to import data from an Excel spreadsheet 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 worksheet
name.

The error sounds like it can not locate my spreadsheet. Any ideas why I
am getting this message? Is there an easier way to do this import?

Thanks,

I haven't set up a worksheet as a linked sever but I do read from excel
all the time:

insert into foobar
select *
from openrowset
('Microsoft.Jet.oledb.4.0',
'excel 8.0;database=dir\filename.xls',
[sheet$])

The dollar sign is required for the sheet name.

Watch out for data types conversions and verify what is loaded. I've
been burned a couple of times with these problems. Usually traced to
excel not knowing data types. In these cases, create a csv file and use

insert into foobar
select *
from opendatasource
('Microsoft.Jet.oledb.4.0',
'Data Source=directory;
extended properties="text;hdr=yes;"')...filename#fileext

Make sure OpenDataSource and OpenRowSet are enabled.