Re: Data Mix in ADO recordset column.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

SP wrote on Fri, 1 Jul 2005 07:23:02 -0700:

> I am pulling out data from excel thorugh ADO recordset object. One of the
> columns contains numeric data. If even one cell in that column contains
> characters other than numeric we need to catch it. What happens is if a
> cell contains alpha, then ADO brings that cell content as null but other
> cell contents in that column have correct numeric data. my question is
> whether ADO can handle different data types in a column?

It's not an ADO problem, it's the Excel driver that determines what datatype
to use for the column by scanning a pre-determined number of rows. If the
alpha is within these rows then the column with be set as char or varchar,
if it's not it'll be set as int. You can work around this using the
instructions in http://support.microsoft.com/kb/194124/EN-US/

Dan


.

Relevant Pages

  • Data Mix in ADO recordset column.
    ... I am pulling out data from excel thorugh ADO recordset object. ... If even one cell in that column contains ... characters other than numeric we need to catch it. ... contents in that column have correct numeric data. ...
    (microsoft.public.data.ado)
  • Re: Ron de Bruins code "Copy a range from all files in a folder and subfolders (optional)" ???
    ... In a Database you cannot mix data types, a column must be all numbers or all text. ... are different data types in the column ADO will copy only the Data type that have the majority. ... If you want to copy only one cell from each workbook then use A3:A3 and not A3 in the code. ... Dim sh As Worksheet, destrange As Range, destrange2 As Range, destrange3 As Range ...
    (microsoft.public.excel.programming)
  • Re: Ron de Bruins code "Copy a range from all files in a folder and subfolders (optional)" ???
    ... In a Database you cannot mix data types, a column must be all numbers or all text. ... are different data types in the column ADO will copy only the Data type that have the majority. ... If you want to copy only one cell from each workbook then use A3:A3 and not A3 in the code. ... Dim sh As Worksheet, destrange As Range, destrange2 As Range, destrange3 As Range ...
    (microsoft.public.excel.programming)
  • RE: Problem reading data from Excel spreadsheet from DTS
    ... I found this reference in another post: ... Excel Inserts Null Values ... numeric data worked, ... > spreadsheets where a cell that has data in it is being read as NULL from DTS. ...
    (microsoft.public.sqlserver.dts)
  • Re: ADODB.Recordset from workbooks : numbers only?
    ... ADO is basically treating you Excel sheet as a table in a database. ... , formula and cell references have no meaning, only the result in each cell. ... Set rsData = New ADODB.Recordset ...
    (microsoft.public.excel.programming)