ADO returns null from Excel for some columns (with all numeric values) 
Author Message
 ADO returns null from Excel for some columns (with all numeric values)

I am reading an Excel file using ADO connection. The excel file has 3
columns Part, Qty1 and Qty2.

I am  getting stuck with a puzzling problem on a sample excel file that I
received from a client. ADO returns null for Qty1 and Qty2  in one of the
rows (row 3 from data shown below). All the cells in these two columns are
formatted as numbers and have numeric values.  So it does not seem to be a
problem with mixed data types. Am not sure what else is causing this.

Part    Qty1    Qty2
--------------------
aaa1     88    88
bbb2    17    17
ccc3     10    10
ddd4    10    10
eee5     50    50

Any help to troubleshoot this issue will be greatly appreciated.

Thanks much,
Seetha.



Sat, 23 Oct 2004 23:49:02 GMT  
 ADO returns null from Excel for some columns (with all numeric values)
I would open the excel file and examine those cells with the
=IsText(C3)
function.

Excel might be storing the numbers as text rather than numbers - whether
this would make a difference to ADO, I can't say, but it certainly is a
recurring problem posted in the Excel groups.

Regards,
Tom Ogilvy


Quote:
> I am reading an Excel file using ADO connection. The excel file has 3
> columns Part, Qty1 and Qty2.

> I am  getting stuck with a puzzling problem on a sample excel file that I
> received from a client. ADO returns null for Qty1 and Qty2  in one of the
> rows (row 3 from data shown below). All the cells in these two columns are
> formatted as numbers and have numeric values.  So it does not seem to be a
> problem with mixed data types. Am not sure what else is causing this.

> Part    Qty1    Qty2
> --------------------
> aaa1     88    88
> bbb2    17    17
> ccc3     10    10
> ddd4    10    10
> eee5     50    50

> Any help to troubleshoot this issue will be greatly appreciated.

> Thanks much,
> Seetha.



Sun, 24 Oct 2004 00:51:24 GMT  
 ADO returns null from Excel for some columns (with all numeric values)
Thanks a lot for your help. This is exactly the case. Excel seems to store
the info as text for all rows except the third one.  And ADO does not like
this kind of mixed data types in the same column.
Why would excel want to store the info in different formats when all the
cells are formatted as numbers and has similar data? Do you know if there is
something (a patch maybe) that we can apply to stop this from happening ?

Thanks again,
Seetha.


Quote:
> I would open the excel file and examine those cells with the
> =IsText(C3)
> function.

> Excel might be storing the numbers as text rather than numbers - whether
> this would make a difference to ADO, I can't say, but it certainly is a
> recurring problem posted in the Excel groups.

> Regards,
> Tom Ogilvy



> > I am reading an Excel file using ADO connection. The excel file has 3
> > columns Part, Qty1 and Qty2.

> > I am  getting stuck with a puzzling problem on a sample excel file that
I
> > received from a client. ADO returns null for Qty1 and Qty2  in one of
the
> > rows (row 3 from data shown below). All the cells in these two columns
are
> > formatted as numbers and have numeric values.  So it does not seem to be
a
> > problem with mixed data types. Am not sure what else is causing this.

> > Part    Qty1    Qty2
> > --------------------
> > aaa1     88    88
> > bbb2    17    17
> > ccc3     10    10
> > ddd4    10    10
> > eee5     50    50

> > Any help to troubleshoot this issue will be greatly appreciated.

> > Thanks much,
> > Seetha.



Sun, 24 Oct 2004 02:11:42 GMT  
 ADO returns null from Excel for some columns (with all numeric values)
It is a common problem when data is brought into a worksheet from external
sources - I can't say why it happens.

You can select all the cells and format them as number or general.  Then put
a 1 in a blank cell,  select it, do edit copy, then select the cells and do
edit =>PasteSpecial, choose values and multiply. This will usually cause
them to be reevaluated as numbers.

Regards,
Tom Ogilvy


Quote:
> Thanks a lot for your help. This is exactly the case. Excel seems to store
> the info as text for all rows except the third one.  And ADO does not like
> this kind of mixed data types in the same column.
> Why would excel want to store the info in different formats when all the
> cells are formatted as numbers and has similar data? Do you know if there
is
> something (a patch maybe) that we can apply to stop this from happening ?

> Thanks again,
> Seetha.



> > I would open the excel file and examine those cells with the
> > =IsText(C3)
> > function.

> > Excel might be storing the numbers as text rather than numbers - whether
> > this would make a difference to ADO, I can't say, but it certainly is a
> > recurring problem posted in the Excel groups.

> > Regards,
> > Tom Ogilvy



> > > I am reading an Excel file using ADO connection. The excel file has 3
> > > columns Part, Qty1 and Qty2.

> > > I am  getting stuck with a puzzling problem on a sample excel file
that
> I
> > > received from a client. ADO returns null for Qty1 and Qty2  in one of
> the
> > > rows (row 3 from data shown below). All the cells in these two columns
> are
> > > formatted as numbers and have numeric values.  So it does not seem to
be
> a
> > > problem with mixed data types. Am not sure what else is causing this.

> > > Part    Qty1    Qty2
> > > --------------------
> > > aaa1     88    88
> > > bbb2    17    17
> > > ccc3     10    10
> > > ddd4    10    10
> > > eee5     50    50

> > > Any help to troubleshoot this issue will be greatly appreciated.

> > > Thanks much,
> > > Seetha.



Sun, 24 Oct 2004 10:38:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Using ADO to Connect to Excel returns null for numeric types

2. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

3. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

4. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

5. Text as ADO; returning numeric or null values on char(1) column

6. Null values reading Excel spreadsheets into ADO recordsets

7. Return Null instead of 0 from numeric function?

8. Returning a databound numeric field to Null

9. Null value in a Numeric field

10. Null values in numeric fields

11. Null values in numeric fields

12. null value for numeric field in Access

 

 
Powered by phpBB® Forum Software