Strange Column name returned from Excel via OLEDB.GetOleDbSchemaTable

[G_Waddell]

Hi,

I've written a routine for a customer to import sales orders into their Sage system. Everything was going well but the client has come back with a particular excel sheet that is not importing. He is getting a message to tell him the sheet is not in a valid format which comes from the results of a validation function I have in my routine. I have looked at the Excel workbook and there is only one sheet. The sheet only has two rows, one for the column headers (all there,) and a second containing a single order.

I debugged through my routine and the product column is coming back as "product_" even though there is no underscore character there hence it is failing the sheet as it can not find the "product" column.

My questions are:
1. What is causing the underscore character?
2. If I adapt my code to allow an underscore character at the end will I then have to call the column in my Select statement as "product_" instead of "product"?

I want to use OLEDB in order to sort the sales items in terms of customer so I can input a single order per customer per sheet rather than multiple orders for single items.

here is my code:

function ExcelIsValid() As boolean
Dim XLConn As System.Data.OleDb.OleDbConnection
Dim bCustomer, bProductcode, bProduct, bQty, bUnitPrice as boolean
Dim DT as datatable
Dim DR as datarow
Dim TableArray() as string
Dim i as integer

try
    ExcelIsValid = false
    XLConn = new OleDb.OleDbConnection(connStr) 'connStr = Global connection string pointing to Excel workbook
    if XLConn.State <> ConnectionState.Open then XLConn.Open
    'get the list of spread sheets in the book
    DT = XLConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)
    If DT.Rows.Count > 0 then
        for each DR in DT.Rows
            redim preserve TableArray(ubound(TableArray)+1) 'enlarge array while keeping data
            TableArray(i) = DR("TABLE_NAME")
            i+=1
        next
    end if
    'now we hopefully have an array of sheet names
    for i =0 to ubound(TableArray)-1
        table_Name = tableArray(i) 'Table_Name is global string 
        DT = XLConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, tblName, Nothing})
        bCustomer=False: bProductcode=False: bProduct=False: bQty=False: bUnitPrice =False 'reset per sheet
        for each DR in DT.rows
            Select Case Trim(LCase(DR("Column_Name")))
                case "product"
                    bProduct = true
                case "product_code"
                    bProduct_code =true
                case "qty"
                    bQty = true
                case "customer"
                    bCustomer =true
                case "unit_price"
                    bUnitPrice = true
            End Select
            If bCustomer andAlso bProductCode andAlso bProduct andAlso bQty andAlso bUnitPrice then
                ExcelIsValid = true
                exit function
            end if
        next

    next
catch ex as exception
    ExcelIsValid = false    
    debug.writeline ex.message  
End Try

End Function

[G_Waddell]

I copied and pasted the excel sheet content into a new blank excel workbook and this worked ok, I'd still love to know what caused it.