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

'Retrieve 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 11 AAA 1
3 1472 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 SS_Col2 Col3 PIN SS_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