VB excel Null values !! Please help me !!

A

Aruna Tennakoon

Hi everyone,

I have these lines in my excel file,

Item Code Qty
9119299 3
9119399 3
AR350001 1

I am reading this file using VB 6.0 and I am using
"Microsoft.Jet.OLEDB.4.0" as the provider to read this excel file. For the
first two lines I am getting null vales in VB.. ? and other line is okey ..
any idea why is that happen and how to fix this problem. Please help me ..
!!!!

-Aruna
 
A

AA2e72E

I copied your data into a workbook and I can read it all. So, I suspect, the clue to your problem lies in the connection string and/or SQL your are using to read the data. What are your connection string and SQL?
 
A

AA2e72E

I do not understand & I cannot see your attachment. I tried using the ODBC driver instead

Cnn="Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book2.xls;
Sql="Select * from [Sheet1$]
Set ADORS=CreateObject("ADODB.RecordSet"
ADORS.Open Sql,Cn

I get the following data in the record set

9119299
9119399

I understand this result: the driver determines that column 1 is numeric and the third row is non numeric and it therefore does not read it (replaces it with null).

I also get the same result with the JET 4.0 driver

If, in your xls file, columns 1, rows 2 & 3 are right justified, they are numeric, row 3 should be left justifies as it is a literal. All rows in Column 2 should be right justified

I haven't got any further suggestions.
 
A

AA2e72E

I had another thought

I think your sheet has non numeric content in column 1, rows 4/5 onwards. So, when the driver/provider scans the default number of rows in every column to determine the data type, it concludes that Column 1 is character. As a consequence, the value in row 2 & 3 being numeric are discarded and replaced by nulls
 
A

Aruna Tennakoon

Hi,

I replaced the driver and now it seems to be okay.. Thanks a lot
for your valuable time ..

Thanks again
-Aruna


AA2e72E said:
I do not understand & I cannot see your attachment. I tried using the ODBC driver instead.

Cnn="Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book2.xls;"
Sql="Select * from [Sheet1$]"
Set ADORS=CreateObject("ADODB.RecordSet")
ADORS.Open Sql,Cnn

I get the following data in the record set:

9119299 3
9119399 3
1
I understand this result: the driver determines that column 1 is numeric
Click to expand...
and the third row is non numeric and it therefore does not read it (replaces
it with null).
I also get the same result with the JET 4.0 driver.

If, in your xls file, columns 1, rows 2 & 3 are right justified, they are
Click to expand...
numeric, row 3 should be left justifies as it is a literal. All rows in
Column 2 should be right justified.
 
Top