Problems with reading data from excel sheet: Some values are null

[Markus Heid]

Hi,
I've got an excel sheet in which i define some kind of matrix. This looks
this way

A B C D E F G

ID 12345

FROM/TO 1 2 3 4 5 6
1 0 3 3,5 0 2,3/4 0

2 7,9/3,6 0 1,2 0 1 1

3 0 0 0 4 0 2

4 1 0 1,2/4 0 1 2

5 2 2,4 1 0 0 6

6 1/5 0 0 0 0 0

I'm trying to read that sheet into a dataset via ado.net in a c# windows
application as descripted in the MSDN Article
http://msdn.microsoft.com/library/en-us/dnadonet/html/odatanet2.asp?frame=true .
But when I'm displaying my dataset in a datagrid some values are null:


ID 12345 (null) (null) (null)
(null) (null)
(null) (null) (null) (null) (null)
(null) (null)
FROM/TO 1 2 3 4 5
6
1 0 3 3,5 0
2,3/4 (null)
2 7,9/3,6 0 1,2 0
1 1
(null) (null) (null) (null) 4
0 2
(null) (null) 0 1,2/4 0
1 (null)
(null) (null) (null) 1 0
0 6
(null) (null) (null) 0 (null)
0 0


All cells in the excel sheet are defined as text. I don't see any pattern
behind this behaivor. Does anyone here knows something about this issue?

Thanks in Advance

Markus

 

[Paul Clement]

¤ Hi,
¤ I've got an excel sheet in which i define some kind of matrix. This looks
¤ this way
¤
¤ A B C D E F G
¤
¤ ID 12345
¤
¤ FROM/TO 1 2 3 4 5 6
¤ 1 0 3 3,5 0 2,3/4 0
¤
¤ 2 7,9/3,6 0 1,2 0 1 1
¤
¤ 3 0 0 0 4 0 2
¤
¤ 4 1 0 1,2/4 0 1 2
¤
¤ 5 2 2,4 1 0 0 6
¤
¤ 6 1/5 0 0 0 0 0
¤
¤ I'm trying to read that sheet into a dataset via ado.net in a c# windows
¤ application as descripted in the MSDN Article
¤ http://msdn.microsoft.com/library/en-us/dnadonet/html/odatanet2.asp?frame=true .
¤ But when I'm displaying my dataset in a datagrid some values are null:
¤
¤
¤ ID 12345 (null) (null) (null)
¤ (null) (null)
¤ (null) (null) (null) (null) (null)
¤ (null) (null)
¤ FROM/TO 1 2 3 4 5
¤ 6
¤ 1 0 3 3,5 0
¤ 2,3/4 (null)
¤ 2 7,9/3,6 0 1,2 0
¤ 1 1
¤ (null) (null) (null) (null) 4
¤ 0 2
¤ (null) (null) 0 1,2/4 0
¤ 1 (null)
¤ (null) (null) (null) 1 0
¤ 0 6
¤ (null) (null) (null) 0 (null)
¤ 0 0
¤
¤
¤ All cells in the excel sheet are defined as text. I don't see any pattern
¤ behind this behaivor. Does anyone here knows something about this issue?
¤

If you have mixed data type values in a column you will need to specify the IMEX parameter in your
connection string:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=e:\\My Documents\\Book2.xls;" +
"Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1" + (char)34;

PRB: Excel Values Returned as NULL Using DAO OpenRecordset
http://support.microsoft.com/default.aspx?scid=kb;en-us;194124


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)