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

avatar image

sathishkumar
234 23 24 29

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

avatar image

Pavel Pawlowski
22.7k 10 15 26

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

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

avatar image

aRookieBIdev
2.8k 56 65 71

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

avatar image

Dave Myers
123 15 15 18

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.

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:

x70

asked: Jun 15, 2011 at 03:35 AM

Seen: 2120 times

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

Copyright 2016 Redgate Software. Privacy Policy