GetOleDbSchemaTable bug (!) not all columns

[Radu]

Hi. I'm trying to read the structure of some Excel 2000 file that the
user is expected to upload. The file must be an excel file (solved)
and it must contain a column named "PIN". That's all. Now about the
column name.

I have the code:

--------------------------------------------------------------------------------------------------------
cnExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & strTableName & ";Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1"";"
cnExcel.Open()
..
..
..
'Retrieve schema information about the excel file (i.e the name of
the
first sheet in the worksheet).
schemaTable = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})
strSheetName = schemaTable.Rows(0).Item("Table_Name").ToString
..
..
..
'Retreive the collection of columns in this excel file...
schemaTable = cnExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
New
Object() {Nothing, Nothing, strSheetName, Nothing})
..
..
..
'Check if we have at least the "PIN" column:
If schemaTable.Select("COLUMN_NAME='PIN'").GetUpperBound(0) < 0 Then
--------------------------------------------------------------------------------------------------------

I have tried with multiple files. Sometimes it
- returns schemaTable.Rows.Count=0 (no columns !!!)
- returns all columns, and I can see them nicely in the
immediate
window
- returns only some columns - say the file has 5 columns, but I
can
only see 4 - schemaTable.Rows.Count = 4 and ?schemaTable.Rows(4)!
COLUMN_NAME.ToString tells me that this index does not exist

I have tried moving the PIN column back and forth in the excel file,
and to my astonishment.... sometimes it works.

Example:

Col1 PIN Col2 Status
1 1 63 AAA
2 1 27 AAA
3 1 82 AAA
3 1 40 AAA
4 1 52 AAA
1 1 5 AAA
2 1 11 AAA
3 1 11 AAA
3 1 17 AAA


gives
?schemaTable.Rows(0)!COLUMN_NAME.ToString
"Col1"
?schemaTable.Rows(1)!COLUMN_NAME.ToString
"Col2"
?schemaTable.Rows(2)!COLUMN_NAME.ToString
"PIN"
?schemaTable.Rows(3)!COLUMN_NAME.ToString
Run-time exception thrown : System.IndexOutOfRangeException - There is
no row at position 3.
(No Status ???)


Col1 Col2 Status PIN
1 653 AAA 1
2 27 AAA 1
3 892 AAA 1
3 40 AAA 1
4 52 AAA 1
1 5 AAA 1
2 151 AAA 1
3 113 AAA 1
3 147 AAA 1


?schemaTable.Rows(0)!COLUMN_NAME.ToString
"Col1"
?schemaTable.Rows(1)!COLUMN_NAME.ToString
"Col2"
?schemaTable.Rows(2)!COLUMN_NAME.ToString
Run-time exception thrown : System.IndexOutOfRangeException - There
is
no row at position 2.
(No PIN ??? No Status ??? Now two columns dropped ???)

This one

Col1 Col2 Col3 PIN Col4
1 5 AAA 1 2
2 6 AAA 1 3
3 7 AAA 1 4
3 8 AAA 1 5
4 9 AAA 1 6
1 10 AAA 1 7
2 11 AAA 1 8
3 12 AAA 1 9
3 13 AAA 1 10

gives

?schemaTable.Rows(0)!COLUMN_NAME.ToString
"Col1"
?schemaTable.Rows(1)!COLUMN_NAME.ToString
"SS_Col2"
?schemaTable.Rows(2)!COLUMN_NAME.ToString
Run-time exception thrown : System.IndexOutOfRangeException - There
is
no row at position 2.

.... and so on. I have tried many combinations. This situation doesn't
have neither rhyme nor reason. Can somebody hep me, please ?

Thanks a lot.
Alex