Query an Excel file with C#, issue with the numeric cells

Question

I am querying an Excel file to retrieve data record and display them in a monitoring tool. The thing is that whenever I am trying to select a record with its id, with the following request, it throws an error when executing the query:

ExecuteCommandTextErrorHandling

telling me that the type in the query and the target cell are not compatible.

My request looks like

SELECT TOP 20 [Fields...] 
FROM [RECORD$] 
WHERE (([id] = '1'))

The id column can contains more than just numbers so its cell type is the default type which is standard.

My question is: how can I fetch the record without knowing its type before?

Do I have to enforce the column type, if so how? Do I have to catch the exception and retry without the quotes ?

Here is the code used to query the Excel file and my connection string

 public static DataTable queryXlsFile(String query, OleDbConnection dbConnection)
 {
        OleDbDataAdapter dbCommand = new OleDbDataAdapter(query, dbConnection);
        DataTable dt = new DataTable();
        dbCommand.Fill(dt);
        return dt;
    }

Provider=Microsoft.ACE.OLEDB.12.0; data source=C:\\test\\321.xls; Extended Properties=\"Excel 8.0;HDR=Yes;\";

Thanks in advance.

Answer 1

try adding IMEX=1;

HDR=Yes;IMEX=1"