Problem using OleDbDataAdapter to retrieve data from Excel worksheet

Question

Firstly, I want to say that here I am in deep water, since I just make some changes in the code written by someone else in the company, using the OleDbDataAdapter to “talk” with Excel, m is not familiar with this. There is one mistake that I simply cannot follow.

I am trying to use an OleDbDataAdapter to read in an excel file with about 450 lines.

In code, this is done as follows:

connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source='" + path + "';" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\""); connection.Open(); OleDbDataAdapter objAdapter = new OleDbDataAdapter(objCommand.CommandText, connection); objAdapter.Fill(objDataSet, "Excel"); foreach (DataColumn dataColumn in objTable.Columns) { if (dataColumn.Ordinal > objDataSet.Tables[0].Columns.Count - 1) { objDataSet.Tables[0].Columns.Add(); } objDataSet.Tables[0].Columns[dataColumn.Ordinal].ColumnName = dataColumn.ColumnName; objImport.Columns.Add(dataColumn.ColumnName); } foreach (DataRow dataRow in objDataSet.Tables[0].Rows) { ... } 

Everything seems to be working fine, except for one. The second column is filled mainly with four numeric digits, such as 6739, 3920 and so on, but the rows of the rows have alphanumeric values, such as 8201NO and 8205NO. These five cells are reported as having empty content instead of their alphanumeric content. I checked in excel and all cells in these columns are marked as Text.

This is an xls file, by the way, not an xlsx.

Does anyone have a clue why these cells are displayed as empty in the DataRow, but are the numbers shown well? There are other columns with alphanumeric content that are shown just fine.

Answer 1

What happens is that excel tries to assign the data type to a spreadsheet column based on the first few values ​​in that column. I suspect that if you look at the properties in this column, it will say that it is a numeric column.

The problem occurs when you start trying to query this table using jet. When he thinks he is dealing with a numeric column and finds the value varchar, he calmly returns nothing. Not even a cryptic error message.

As a possible work, you can transfer one of the alpha-numeric values ​​to the first row of data, and then try parsing. I suspect you will start to get values ​​for alphanumeric strings, then ...

Take a look at this article . In more detail this question. he also talks about possible work around which:

However, according to the JET documentation, we can override the registry setting through the Connection String, if we set IMEX = 1 (as part of the Extended Properties), JET will set all the column types to UNICODE VARCHAR or ADVARWCHAR regardless of the ImportMixedTypes key value.

Answer 2

IMEX=1 means "Read mixed data as text."

However, there are some errors. Jet will only use a few lines to determine if the data is mixed, and if so, these lines are numeric, you will get this behavior.

See connectionstrings.com for more details:

Check the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located REG_DWORD registry "TypeGuessRows". So that the key does not allow Excel to use only the first 8 rows to guess the data type of the columns. Set this value to 0 to scan all rows. This may damage the work. Also note that adding the IMEX = 1 option may cause the IMEX function to be installed only after 8 lines. Use IMEX = 0 instead to force the TypeGuessRows = 0 registry (scan all rows) to work.

Answer 3

I would suggest using OleDb data provider stuff to access Excel if you can help him. I had nothing but problems, precisely for the reasons that others spoke of. Performance is also usually atrocious when dealing with large spreadsheets.

You can try this open source solution: http://exceldatareader.codeplex.com/