OleDB & mixed Excel datatypes : missing data

by

Using .Net 4.0 and reading Excel files, I had a similar issue with OleDbDataAdapter – i.e. reading in a mixed data type on a “PartID” column in MS Excel, where a PartID value can be numeric (e.g. 561) or text (e.g. HL4354), even though the excel column was formatted as “Text”.

From what I can tell, ADO.NET chooses the data type based on the majority of the values in the column (with a tie going to numeric data type). i.e. if most of the PartID’s in the sample set are numeric, ADO.NET will declare the column to be numeric. Therefore ADO.Net will attempt to cast each cell to a number, which will fail for the “text” PartID values and not import those “text” PartID’s.

My solution was to set the OleDbConnection connectionstring to use Extended Properties=IMEX=1;HDR=NO to indicate this is an Import and that the table(s) will not include headers. The excel file has a header row, so in this case tell ado.net not to use it. Then later in the code, remove that header row from the dataset and voilà you have mixed data type for that column.

string sql = "SELECT F1, F2, F3, F4, F5 FROM [sheet1$] WHERE F1 IS NOT NULL";

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PrmPathExcelFile + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");

OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();
ds.Tables.Add("xlsImport", "Excel");
da.Fill(ds, "xlsImport");

// Remove the first row (header row)
DataRow rowDel = ds.Tables["xlsImport"].Rows[0];
ds.Tables["xlsImport"].Rows.Remove(rowDel);

ds.Tables["xlsImport"].Columns[0].ColumnName = "LocationID";
ds.Tables["xlsImport"].Columns[1].ColumnName = "PartID";
ds.Tables["xlsImport"].Columns[2].ColumnName = "Qty";
ds.Tables["xlsImport"].Columns[3].ColumnName = "UserNotes";
ds.Tables["xlsImport"].Columns[4].ColumnName = "UserID";

connection.Close();

// now you can use LINQ to search the fields

    var data = ds.Tables["xlsImport"].AsEnumerable();
    var query = data.Where(x => x.Field<string>("LocationID") == "COOKCOUNTY").Select(x =>
                new Contact
                {
                    LocationID= x.Field<string>("LocationID"),
                    PartID = x.Field<string>("PartID"),
                    Quantity = x.Field<string>("Qty"),
                    Notes = x.Field<string>("UserNotes"),
                    UserID = x.Field<string>("UserID")
                });

More Related Contents:

  1. reading Excel Open XML is ignoring blank cells
  2. SumIf using Excel VBA
  3. How can I call a macro which contains a loop but only run one iteration of that loop?
  4. Test or check if sheet exists
  5. How can I find last row that contains data in a specific column?
  6. Excel to CSV with UTF8 encoding [closed]
  7. Is there any event that fires when keys are pressed when editing a cell?
  8. Excel: last character/string match in a string
  9. Does the VBA “And” operator evaluate the second argument when the first is false?
  10. Split cell values into multiple rows and keep other data
  11. Is VBA an OOP language, and does it support polymorphism?
  12. Export each sheet to a separate csv file
  13. What are .NumberFormat Options In Excel VBA?
  14. How to construct Dataframe from a Excel (xls,xlsx) file in Scala Spark?
  15. Checking if a worksheet-based checkbox is checked
  16. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  17. Excel: Searching for multiple terms in a cell
  18. Extract Data from Word Document to an Excel SpreadSheet
  19. Excel macro -Split comma separated entries to new rows [duplicate]
  20. VBA equivalent to Excel’s mod function
  21. Pointers needed for speeding up nested loop macro in VBA
  22. Comparing two columns on sheet1 to two other columns on sheet2 and returning another column in sheet 2.
  23. VBA editor auto-deletes spaces at the ends of lines
  24. ‘1004’: “The sort reference is not valid.”
  25. Select entire column in table using Excel VBA
  26. Reducing WithEvent declarations and subs with VBA and ActiveX
  27. Function Overloading and UDF in Excel VBA
  28. Scientific notation when importing from Excel in .Net
  29. VBA: Modify chart data range
  30. Get parent folder path from file path using cell formula

Leave a Comment