EXCEL AND OLEDB

Yama

unread,

Feb 24, 2004, 1:56:10 PM2/24/04

to

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

Tom Ogilvy

unread,

Feb 24, 2004, 2:07:29 PM2/24/04

to

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" <anon...@discussions.microsoft.com> wrote in message
news:4F63942D-D237-4457...@microsoft.com...

Yama

unread,

Feb 24, 2004, 2:31:08 PM2/24/04

to

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

Yama

unread,

Feb 24, 2004, 2:56:07 PM2/24/04

to

Hello,

I was able to duplicate this behavior. If the column majority data type is STRING then anything NUMERIC will return as a NULL or as a blank (empty) field.

How can I resolve this?

Yama

----- Yama wrote: -----

Jake Marx

unread,

Feb 24, 2004, 2:56:22 PM2/24/04

to

Hi Yama,

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. If the data type is determined to be numeric,
then Excel will ignore non-numeric fields (and possibly vice versa).

There are a few workarounds you could try:

1) Add 8 rows of "fake" data to the top of your data set. For example, if
you want to force the column to be text, then you could use "aaa" in each
row. You can hide these rows to avoid confusion.

2) You can tell the ODBC driver that you want to scan more or less rows than
8 (even all rows) in order to determine data type. Although there is a
parameter that is supposed to do this, it doesn't work. Here are a few
relevant articles that discuss the issue and workaround:

http://support.microsoft.com/default.aspx?scid=kb;en-us;257819

http://support.microsoft.com/default.aspx?scid=kb;EN-US;189897

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Tom Ogilvy

unread,

Feb 24, 2004, 4:49:35 PM2/24/04

to

in the first article it also says:

To work around this problem for read-only data, enable Import Mode by using
the setting "IMEX=1" in the Extended Properties section of the connection
string. This enforces the ImportMixedTypes=Text registry setting. However,
note that updates may give unexpected results in this mode. For additional
information about this setting, click the article number below to view the
article in the Microsoft Knowledge Base:

So maybe you can do something with the IMEX property.

--
Regards,
Tom Ogilvy

"Jake Marx" <msn...@longhead.com> wrote in message
news:Os27HBx%23DH...@tk2msftngp13.phx.gbl...

Yama

unread,

Feb 24, 2004, 5:11:06 PM2/24/04

to

Hello Tom & Jake,

You both contributed with such dexterity! Thank you very much.

Tom your solution for adding the IMEX=1 worked great (I just want a read-only solution; hence, your input was great):

'--// Using VB.NET and ASP.NET
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim ds As New DataSet

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\yamabiz.com\ProductCatalog.xls;" & _
"Extended Properties=""Excel 8.0;IMEX=1""" '--// GREAT JOB TOM

Dim da As New OleDbDataAdapter("SELECT [Product ID], [Description], [Date Created], [ISBN] " & _
" FROM [Sheet1$]", strConn)
da.TableMappings.Add("Table", "ProductCatalog")
da.Fill(myDataset)

DataGrid1.DataSource = ds.Tables(0).DefaultView

For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
Dim s As String = ds.Tables(0).Rows(i)("Product ID").ToString
Response.Write(s + "<br>")
Next

DataGrid1.DataBind()
End Sub

Thank you very much to both of you guys,

Jake Marx

unread,

Feb 24, 2004, 5:34:46 PM2/24/04

to

Tom Ogilvy wrote:
> in the first article it also says:
>
> To work around this problem for read-only data, enable Import Mode by
> using the setting "IMEX=1" in the Extended Properties section of the
> connection string. This enforces the ImportMixedTypes=Text registry
> setting. However, note that updates may give unexpected results in
> this mode. For additional information about this setting, click the
> article number below to view the article in the Microsoft Knowledge
> Base:
>
> So maybe you can do something with the IMEX property.

Good catch, Tom - I missed that....

onedaywhen

unread,

Feb 25, 2004, 4:50:06 AM2/25/04

to

"Jake Marx" <msn...@longhead.com> wrote in message news:<Os27HBx#DHA...@tk2msftngp13.phx.gbl>...

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

--

Jake Marx

unread,

Feb 25, 2004, 10:29:31 AM2/25/04

to

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