x

import excel to sql

Hi,

When i am trying to import data from excel to sql i receiving following error message "OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error."

Can anybody help me to solve this error
more ▼

asked Jun 15, 2011 at 03:35 AM in Default

sathishkumar gravatar image

sathishkumar
234 22 24 26

What are you using to do this? The best way to import data is SSIS.
Jun 15, 2011 at 03:49 AM Mrs_Fatherjack

i am using this in sql 2000 my query is

SELECT * INTO MedParts_Drawings
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;Database= F:\New Folder\NEW_ MedtronicParts_Second Project.xls',
'SELECT * FROM [MedtronicParts_Drawings$]')
Jun 15, 2011 at 03:51 AM sathishkumar
what version of sql Server 2000? x64 or x32?
Jun 15, 2011 at 03:54 AM Pavel Pawlowski
version SQL 8.00.194 x32
Jun 15, 2011 at 04:04 AM sathishkumar
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If you have really the space as it is in you example Database= F:\...., then the space probably causes a problems.

When doing a test on my machine, the below statement works correctly

SELECT
*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;Database=C:\tmp\TestExcel.xls',
            'SELECT * FROM [TestSheet$]')

But when executing a below command with the space in the Database= C:\...

SELECT
*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;Database= C:\tmp\TestExcel.xls',
            'SELECT * FROM [TestSheet$]')

A below error is thrown:

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" 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 "(null)".

more ▼

answered Jun 15, 2011 at 11:55 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Good eye, good eye!
Jun 15, 2011 at 12:07 PM Oleg

Hi Pavel,

If i executed the query without space im getting the same error..two types of error

  1. "OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error."

  2. "OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

[OLE/DB provider returned message: Could not find installable ISAM.]"
Jun 15, 2011 at 10:58 PM sathishkumar

Try to check this KB article whether it is related to you: [http://support.microsoft.com/kb/209805/en-us][1]

Anyway.. Does the SQL Service account access to the excel file you are trying to import. You need to take in mind, that the SQL Server Service account is accessing the file and not your account.

[1]: http://support.microsoft.com/kb/209805/en-us
Jun 16, 2011 at 04:00 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Alternatively you could use a DTS package to do the excel import to sql database.
more ▼

answered Jun 15, 2011 at 04:39 AM

aRookieBIdev gravatar image

aRookieBIdev
2.3k 51 56 61

yes kannan, but just trying the same in query
Jun 15, 2011 at 04:56 AM sathishkumar
(comments are locked)
10|1200 characters needed characters left
Is there a reason you must use this in a query? There is an import data wizard available that does a great job for loading data
more ▼

answered Jun 15, 2011 at 10:49 AM

Dave Myers gravatar image

Dave Myers
123 15 15 16

Hi Dave, No reason, trying to know how to import data using query..
Jun 15, 2011 at 11:01 PM sathishkumar
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x66

asked: Jun 15, 2011 at 03:35 AM

Seen: 1787 times

Last Updated: Jun 15, 2011 at 04:01 AM