OleDB & mixed Excel datatypes : missing data

Question

I have an Excel worksheet I want to read into a datatable - all is well except for one particular column in my Excel sheet. The column, 'ProductID', is a mix of values like ########## and n#########.

I tried to let OleDB handle everything by itself automatically by reading it into a dataset/datatable, but any values in 'ProductID' like n###### are missing, ignored, and left blank. I tried manually creating my DataTable by looping through each row with a datareader, but with the exact same results.

Here's the code :

// add the column names manually to the datatable as column_1, column_2, ...
for (colnum = 0; colnum < num_columns; colnum ++){
  ds.Tables["products"].Columns.Add("column_" +colnum , System.Type.GetType("System.String")); 
}
while(myDataReader.Read()){
  // loop through each excel row adding a new respective datarow to my datatable 
  DataRow a_row = ds.Tables["products"].NewRow();
  for (col = 0; col < num_columns; col ++){
    try {  a_row[col] = rdr.GetString(col);  }
    catch {  a_row[col] = rdr.GetValue(col).ToString(); }
  }
  ds.Tables["products"].Rows.Add(a_row);
}

I don't understand why it won't let me read in values like n######. How can I do this?

Answer 1

There are two ways to handle mixed datatypes & excel.

Method 1

• Open up your excel spreadsheet and set the column format to the desired format manually. In this case, 'Text'.

Method 2

• There is a "hack" that consists of appending "IMEX=1" to your connection string like so:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myfile.xls;Extended Properties=Excel 8.0;IMEX=1

• This will attempt to handle mixed Excel formats according to how it is set in your registry. This can be set locally by you, but for a server, this is probably not an option.