Can''t find file: MSDASQL Openrowset, Excel Driver import

Question

Post reply

Can''t find file: MSDASQL Openrowset, Excel Driver import

MS Verbsthenoun

Ten Centuries

Points: 1153
More actions
December 18, 2005 at 7:27 pm

#88845

For some reason SQL Server is refusing to "find" this file. I've quintple checked the path, using explorer to view the dir, then copy/pasting the directory, and doing the same with the file name to rule out any erroneous typos in the path.
I checked permissions on the directories and the file, they're all "normal" nothing restrictive.
What piece am I missing? Or is there some little typo keeping it from finding the file?
Code below, with error afterwards:
SELECT * INTO MarketsPreTemp
FROM OpenRowSet('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};
DBQ=C:\MarketsImport\Markets.xls;
DriverID=790;',
'SELECT * FROM Markets')
/*
DefaultDir=C:\MarketsImport;
FIL=excel 8.0;
MaxBufferSize=2048;
MaxScanRows=8;
PageTimeout=5;
ReadOnly=1;
SafeTransactions=0;
Threads=3;', */
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Markets'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Any suggestions appreciated. Thanks.

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

Does the file exist in C:\MarketsImport on the server ?

The query is run on the SQL box so the file must be on that server.

Alternatively, you could create a share and use a UNC name for the file.

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 2

Yes, the file is on the same server. I've imported DBASEIV files using similar syntax and locations on the same system.

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

Does the file contain a sheet called "Markets".

The table in the select statement 'SELECT * FROM Markets' needs to match a sheet in the workbook.

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 4

Ah. That could be it then, I thought it was dependent on the filename.

I'll get back on that, but I suspect that would be it. Thanks!

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 5

Hmm, That still doesn't seem to work.

There are three worksheets in the Markets.xls file

AlphaByState

AlphaByMarket

AllAreaCodes

Btw, they originally had spaces in the names, and I removed the spaces to "play it safe", but the client probably won't like that.

So, all I modified was the select statement from Markets to AlphaByState, and still no luck.

Any other suggestions?

Is there anyway I can enable a more verbose "debug" to get better idea of what it is and is not finding?

Thanks.

happycat59 One Orange Chip Points: 29389 More actions · Answer 6

I think the answer is that the name of the sheet used in the Select statement needs to end with $. In the workbook itself, the name of the sheet does not have the $. In your example, Excel shows the name of the sheet as Market. In the OpenRowSet query, the equivalent is [Market$].

SELECT * INTO MarketsPreTemp

FROM OpenRowSet('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};

DBQ=C:\MarketsImport\Markets.xls;',

'SELECT * FROM [Markets$]')

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 7

Hmm, that's didn't seem to help either... Different error:

SELECT * INTO MarketsPreTemp

FROM OpenRowSet('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};

DBQ=C:\MarketsImport\Markets.xls;

DriverID=790;',

'SELECT * FROM [Markets$]')

Results:

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

OLE DB provider 'MSDASQL' reported an error.

[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] 'Markets$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

happycat59 One Orange Chip Points: 29389 More actions · Answer 8

This looks like you do not have a sheet called "Markets" in the workbook Markets.xls. Your Select statement works fine for me.

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 9

I tried it with the other sheet names:

AlphaByState

AlphaByMarket

AllAreaCodes

And that fixed it. I had posted these sheet names earlier, so thought you were indicating the filename again.

I guess I should clarify, this is SQl Server 2000, if that makes any difference down the road for anyone else.

Anyhow, it works now as:

' SELECT * FROM [AlphaByState$]')

Thanks!

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 10

Hmm, it imported, but it added about 12,000 (NULL) rows (there were only about a 100 in the sheet). Any ideas why it would do that?

Thanks.

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 11

Additional information:

Import of AlphaByMarket causes the 12,000+ null records.

However, AlphaByState and AllAreaCodes work correctly.

In looking further, it appears the client had for whatever reason over 12,000 empty

entries in the first sheet of the file (AlphaByMarket), so the system

is doing what it should.

Just a matter of deleting the empty rows from the xls file.

Thanks again for the help!

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 12

It took me a bit to figure this one out, and I still don't know a faster way to handle it. It's apparent that the connection for the file remains 'open" for a while after running the script. This makes trying to import the other sheets fail if I try shortly after importing one sheet.

If I wait a few minutes, the connection clears up and I can import another sheet from that file.

So the question is how do I close the connection right after I get the data I need from the first sheet, so that I can open it again for the second sheet?

Thanks.