Inconsistancy with Importing from Excel using OpenRowSet

Question

Inconsistancy with Importing from Excel using OpenRowSet

Scott Grabus

SSC Enthusiast

Points: 114
More actions
June 26, 2007 at 9:17 am

#179233

I am noticing that using the following to insert data from an Excel spreadsheet into a SQLServer table will only work some of the time (about 3 out of every 5 times executed).
INSERT INTO table1
(some_id,   address_line_1, address_line_2, address_line_3, city, state, zip_code, country, last_name,   first_name,
middle_name, phone, mobile_phone, email)
SELECT convert(varchar(10),[SID]),
   convert(varchar(40),[Address1]),
   convert(varchar(40),[Address2]),
   convert(varchar(40),[Address3]),
   convert(varchar(30),[City]),
   convert(varchar(4),[State]),
   convert(varchar(15),[Zip]),
   convert(varchar(6),[CountryCode]),
   convert(varchar(25),[LastName]),
   convert(varchar(20),[FirstName]),
   convert(varchar(20),[MiddleName]),
   convert(varchar(30),[Phone]),
   convert(varchar(30),[MobilePhone]),
   convert(varchar(255),[Email])
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\server\share\Excel_Addresses.xls;',
'SELECT [SID],[Address1],[Address2],[Address3],[City],[State],[Zip],[CountryCode],[LastName],[FirstName],[MiddleName],[Phone],[MobilePhone],[Email] FROM [Excel_Addresses$]')
When it doesn't work, I get the following error, which, of course, tells me nothing:
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
The permissions on the excel file are wide open so I'm not sure what would be interfering. Is there something else I should be adding to the OpenRowSet parameters? And like it said it works some of the time, but I'd like to get it to work all of the time.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

is there a change in the data which is causing jet to bail suck as a column it thought was an int then suddenly a string? Like zip, phone, mobilephone or something like that?

You say it bails 2 out of 5. Does the source data change at all?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Scott Grabus SSC Enthusiast Points: 114 More actions · Answer 2

Thanks Cris, but no, my test spreadsheet is the same set of data each try. That's why it's so annoying.

Crispin Proctor SSCoach Points: 18161 More actions · Answer 3

Most odd. Can you run a few tests in SSIS? it still uses JET but you can redirect any error rows and see what is different about them.

How many rows we talking?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Scott Grabus SSC Enthusiast Points: 114 More actions · Answer 4

Yep, I can try it out in SSIS and see what it reports, if anything. Thanks for the suggestion. And it's only about 250 rows. I tried it with a smaller case of 20 rows and the same thing was happening.

Gracias!

Scott Grabus SSC Enthusiast Points: 114 More actions · Answer 5

SSIS had no problems with it. All I got was for each column in the Excel spreadsheet was something like the following:

Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [84]: Truncation may occur due to inserting data from data flow column "SID" with a length of 255 to database column "s_id" with a length of 10.

So what are you thinking? And please don't say, "then just use SSIS."

Crispin Proctor SSCoach Points: 18161 More actions · Answer 6

Ummm, use SSIS?

The only thing I can think is a difference between your JET drivers and those on the server. (Are you using a seperate server?)

When running SSIS, did you run it locally? i.e. Your drivers. and when SQL, using the server's?

If so, try applying the Jet SP to the server (and your machine) and see if that helps.

http://support.microsoft.com/default.aspx/kb/239114

Sorry, no pearls of wisdom...

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Hi Scott

I'm working with SQL2K and importing from Excel files using server linking, however I get a similar error message...

Server: Msg 7399, Level 16, State 1, Line 3

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

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

... if the spreadsheet is open.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Scott Grabus SSC Enthusiast Points: 114 More actions · Answer 8

Thanks Chris, I did learn that very early on. However, the spreadsheet was not opened in all subsequent tries while getting this error.

Scott

Scott Grabus SSC Enthusiast Points: 114 More actions · Answer 9

In SSIS, I tried something else...I created an Execute T-SQL Task and put my insert statement in as the statement to execute, and it failed with a the same message. I'll try the T-SQL SP and report back.

Thanks guys.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

No worries mate, it was worth a try. I've also had this error if the file doesn't exist (someone sends it over with a typo in the name).

FYI here's sample code for server linking. "ACTIVE ITEMS" is the name of the worksheet.

DECLARE @MasterStockFile VARCHAR(100)
SET @MasterStockFile  = 'D:\Reporting\WARW\' + 'ITEM MASTER FROM LAWSON - 18 Jun 07.xls'

-- Create server link
EXEC sp_addlinkedserver 'MasterStock', 
 @srvproduct = '', 
 @provider = 'Microsoft.Jet.OLEDB.4.0', 
 @datasrc = @MasterStockFile, 
 @provstr = 'Excel 8.0;'

EXEC sp_addlinkedsrvlogin 'MasterStock', 'false'  
GO

-- Query the datasource
SELECT CAST([ITEM] AS int) AS [ITEM], [DESCRIPTION], [USER-FIELD2], [MANUF-NBR]
FROM [MasterStock]...['ACTIVE ITEMS$']

EXEC sp_dropserver 'MasterStock', 'droplogins'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Scott Grabus SSC Enthusiast Points: 114 More actions · Answer 11

I didn't get a chance to install the SP yet, but one thing I did notice was that when it fails, it always fails after 20 seconds. I tried adding the "connection timeout=40" param but it doesn't like it in OpenDataSource or OpenRowSet. And Microsoft doesn't list any examples of code using this parameter, so I don't know if my syntax is correct.