import excel to sql

Question

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

Answer 1

0

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

answered Jun 15 '11 at 11:55 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

Good eye, good eye!

Jun 15 '11 at 12:07 PM Oleg

Hi Pavel,

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

"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error."
"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Could not find installable ISAM.]"

Jun 15 '11 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

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.

Jun 16 '11 at 04:00 AM Pavel Pawlowski

(comments are locked)

Answer 2

0

Alternatively you could use a DTS package to do the excel import to sql database.

answered Jun 15 '11 at 04:39 AM

aRookieBIdev
2k ● 24 ● 40 ● 46

yes kannan, but just trying the same in query

Jun 15 '11 at 04:56 AM sathishkumar

(comments are locked)

Answer 3

0

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

answered Jun 15 '11 at 10:49 AM

Dave Myers
123 ● 13 ● 15 ● 16

Hi Dave, No reason, trying to know how to import data using query..

Jun 15 '11 at 11:01 PM sathishkumar

(comments are locked)

import excel to sql

Follow this question

Related Questions