Hello

I have a spreadsheet I am trying to Query as follow and it works

"SELECT [Product ID] FROM Sheet1$

But for some odd reason productIDs that are only numeric displays as blanks whereas non numeric productIDs do display

So I tried a different query

"SELECT CAST([Product ID] AS VARCHAR(25)) FROM Sheet1$

But I got an error message. How far can I take TSQL to query a spreadsheet

Yama

It makes a determination on the data type in the first couple of cells, I
believe, then once determined, only selects cells that match that type.

--
Regards,
Tom Ogilvy

"Yama" wrote in message
...
Hello,

I have a spreadsheet I am trying to Query as follow and it works:

"SELECT [Product ID] FROM Sheet1$"

But for some odd reason productIDs that are only numeric displays as
blanks whereas non numeric productIDs do display.

So I tried a different query:

"SELECT CAST([Product ID] AS VARCHAR(25)) FROM Sheet1$"

But I got an error message. How far can I take TSQL to query a
spreadsheet?

Yama

Hello Tom,

That's the obvious deduction! :-)
How to SELECT every cell in a specific format. In SQL I can either use a CAST or a CONVERT. How about in EXCEL Query language?

Yama

----- Tom Ogilvy wrote: -----

It makes a determination on the data type in the first couple of cells, I
believe, then once determined, only selects cells that match that type.

--
Regards,
Tom Ogilvy

"Yama" wrote in message
...
Hello,
I have a spreadsheet I am trying to Query as follow and it works:
"SELECT [Product ID] FROM Sheet1$"
But for some odd reason productIDs that are only numeric displays as
blanks whereas non numeric productIDs do display.
So I tried a different query:
"SELECT CAST([Product ID] AS VARCHAR(25)) FROM Sheet1$"
But I got an error message. How far can I take TSQL to query a
spreadsheet?
Yama

"Jake Marx" wrote in message ...

I don't think you can force Excel to grab all data without using a
workaround. The ODBC driver scans the first 8 rows of data in each column
to determine the data type.

The quoted 8 rows is a registry setting:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

Setting the value to 0 (zero) should force ADO to scan all column
values before choosing the appropriate data type.

--

onedaywhen wrote:
"Jake Marx" wrote in message
...

I don't think you can force Excel to grab all data without using a
workaround. The ODBC driver scans the first 8 rows of data in each
column to determine the data type.

The quoted 8 rows is a registry setting:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

Setting the value to 0 (zero) should force ADO to scan all column
values before choosing the appropriate data type.

Right - and point #2 in my post and the second KB article referred to this
workaround.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]