GetOleDbSchemaTable bug (!) not all columns

  • Thread starter Thread starter Radu
  • Start date Start date
R

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
 
Back
Top