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.NE
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Loa
Dim ds As New DataSe
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 TO
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).DefaultVie
For i As Integer = 0 To ds.Tables(0).Rows.Count -
Dim s As String = ds.Tables(0).Rows(i)("Product ID").ToStrin
Response.Write(s + "<br>"
Nex
DataGrid1.DataBind(
End Su
Thank you very much to both of you guys
Yam
----- Tom Ogilvy wrote: ----
in the first article it also says
To work around this problem for read-only data, enable Import Mode by usin
the setting "IMEX=1" in the Extended Properties section of the connectio
string. This enforces the ImportMixedTypes=Text registry setting. However
note that updates may give unexpected results in this mode. For additiona
information about this setting, click the article number below to view th
article in the Microsoft Knowledge Base
So maybe you can do something with the IMEX property
--
Regards
Tom Ogilv
Jake Marx said:
Hi Yama
I don't think you can force Excel to grab all data without using
workaround. The ODBC driver scans the first 8 rows of data in each colum
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, i
you want to force the column to be text, then you could use "aaa" in eac
row. You can hide these rows to avoid confusion
2) You can tell the ODBC driver that you want to scan more or less row
tha
8 (even all rows) in order to determine data type. Although there is
parameter that is supposed to do this, it doesn't work. Here are a fe
relevant articles that discuss the issue and workaround
MS MVP - Exce
www.longhead.co
[please keep replies in the newsgroup - email address unmonitored
Yama wrote Hello Tom
That's the obvious deduction! :-
How to SELECT every cell in a specific format. In SQL I can eithe
use a CAST or a CONVERT. How about in EXCEL Query language
Yam
----- Tom Ogilvy wrote: ----
It makes a determination on the data type in the first couple o
cells, I believe, then once determined, only selects cells tha
match that type
Regards
Tom Ogilv
Hello
I have a spreadsheet I am trying to Query as follow and i
works: >> "SELECT [Product ID] FROM Sheet1$
But for some odd reason productIDs that are only numeri
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
