|
|
Posted by Jim Johannsen on 01/26/08 01:51
On Wed, 16 Jan 2008 10:13:10 -0800, alvinstraight38@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.
Navigation:
[Reply to this message]
|