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.Column s, New
Object() {Nothing, Nothing, strSheetName, Nothing})
'Check if we have at least the "PIN" column:
If schemaTable.Select("COLUMN_NAME='PIN'").GetUpperBo und(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