Wrong data type returned for field from Excel
TOMH
I created a DSN to connect to a simple Excel 2000
worksheet.
I created a DataEnvironment element with a connection that
worked, and a Command that showed me all columns contained
within.
However, even though the columns are formatted as text, if
a cell in one of the columns contains values like "1245-
MOD", the ODBC driver seems to be signalling to the
recordset that it is a "adDouble" data type.
I have tried everything I can think of and done a ton of
reading.
Is there a simple way to correct this?
My workaround was writing an ActiveX automation program
that opened Excel un the background and searched ranges of
cells but this is a dog.
Rockn-Roll
are connecting. I use the connection object and use the
Open method, then use the OpenRecordset method to set my
recordset object. However, I've learned that the Excel
transport not only tries to convert the data, but it's
very very slow for even a modestly sized sheet.
In my opinion, using automation is the way to go cause
it's so much faster, however I don't try manipulating the
sheet with the object cause that is way slooow as you have
discovered.
What I do is use the automation object to save the file as
an ASCII text file with quotes around every column...for
some reason it's blazingly fast...then I can use the
Access object to import directly into a predefined
table...again it's the slickest thing since teflon coated
oil...or sometimes I just write a parser myself if it's an
adhoc quickie, and I don't want the Access Object overhead
cause it loads ODBC and DAO along with it...even if it
isn't going to be used, though I think in Access 2000,
there's a way to load the object without the data support
and juse use the object methods and properties to
manipulate data.
>.
>
TOMH
number of true rows used in a worksheet under excel??
The Rows.Count method returns the max rows for a sheet,
Also, is there a seek method to quickly find a value in a
column?/?
>.
>
Douglas Laudenschlager [MS]
Tom,
The Excel ISAM driver makes its decision on the row's datatype based on a
sampling, usually of the first 8 rows. If the datatypes are mixed, it
chooses the "majority" type, leaning in favor of numbers (adDouble) if
there's a tie. Values of the "minority" type are returned as NULLs, so even
once we figure out this datatype issue, you're not necessarily out of the
woods yet... there are unfortunately a number of quirks working with Excel
and ADO.
Are the first several rows in your column, in fact, numeric only?
Would you consider emailing me the workbook to take a look at?
-Doug
Using ADO with Excel: Resources and Known Issues
March 6, 2002
Microsoft Knowledge Base articles can be found at:
http://search.support.microsoft.com/kb/c.asp
Choose to search by "specific article ID number"
Enter the article number including the "Q"
dotNet
------
Q306022 HOW TO: Transfer Data to an Excel Workbook Using Visual Basic .NET
Q306572 HOW TO: Query and Display Excel Data Using ASP.NET, ADO.NET
Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .NET
Q311731 HOW TO: Query and Display Excel Data Using ADO.NET, VB .NET
Q316756 PRB: Error Using ADO.Net OleDbDataAdapter to Modify Excel Workbook
(pending)
Q316831 PRB: Cannot Configure Data Connections to Excel Files (pending)
Q316934 HOW TO: Use ADO.Net to Retrieve and Modify Records in an Excel
Workbook (pending)
Q317719 HOW TO: Display Data in Excel Using ASP.NET WebForm (pending)
Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .Net
(pending)
General
-------
Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
Q306397 INFO: Use Excel with SQL Server Linked Servers and Distributed
Queries
Known Issues
------------
Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver (Rows
To Scan)
Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (mixed
data types)
Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected File
Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error (DTS
& Excel)
Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
Q294410 ACC2002: Nulls Replaced w/ Next Field's Data Exporting to Excel
Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
Q314763 FIX: ADO Inserts Data into Wrong Excel Columns
Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found
Transferring Data into Excel
----------------------------
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation
Other How-To Subjects
-----------------------
Q244761 XL2000: How to Use ADO to Return Data to a ListBox or ComboBox
Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use
Q302094 HOWTO: Fill/Retrieve Excel Data Using Arrays From VB .Net
Q307063 HOW TO: Write Binary Files to the Browser Using ASP.NET & VB
Known Issues Working within Excel
---------------------------------
Q263498 BUG: Run-Time Error 5 Using Add Method of QueryTables Collection
Q215154 XL2000: Excel does not support OLE DB Data Links
Q228633 OFF2000: "Catastrophic Failure" Error Running Samples.xls Macro
Q291199 XL2002: "Invalid Use of New Keyword" Error Using ADODB Library
Q225059 XL2000: "Invalid Use of New Keyword" Error Using ADODB Library
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Rockn-Roll
parameter to get the whole sheet] property which returns a
range object...then use the Range.Rows.Count property to
return the row count.
To quickly find a value use Range.Find("Text2Find") which
returns a Range object...then use the Range.Address() [use
the empty parameter to get the address of the first
cell]. Also use Range.FindNext to continue searching
until Range.Address() returns the first cell that was
found...ie...Excel has searched all the way down to the
bottom of the sheet and looped back to where the first
instance was located.
>.
>
Cindy Meister -WordMVP-
Not just Excel and ADO, but also mail merge to Word, I'm afraid... Wreaks havoc with things like international zipcodes.
So I'm very glad to pick up a few more bits of info on how this is working. Is there any more on how Excel passes data types squirrelled away in a KB article or MSDN, somwehere?
> The Excel ISAM driver makes its decision on the row's datatype based on a
> sampling, usually of the first 8 rows. If the datatypes are mixed, it
> chooses the "majority" type, leaning in favor of numbers (adDouble) if
> there's a tie. Values of the "minority" type are returned as NULLs, so even
> once we figure out this datatype issue, you're not necessarily out of the
> woods yet... there are unfortunately a number of quirks working with Excel
> and ADO.
>
-- Cindy Meister