Search code examples
c#exceloledb

Reading Excel sheet with OleDb having String, Number and Date fields in the same column

OleDb offers comfortable possibilities to read excel files. One of the limitations for me seem to be the case when a column has different data types not being convertible from a string to other data types unambiguously. An example is a column having string entries, number entries and Date entries.

TryParse actions might succeed for a Double as well as for a DateTime, providing no unambiguous data type.

The following simple table gives an example.

enter image description here

My question: is it possible to handle this case with OleDb at all? If yes how? If not, which other possibilities exist except from reading the Excel file with COM Interop?

Solution

  • As far as I know, OleDb will attempt to figure out the data type of each column and fail in this case.

    If not, which other possibilities exist except from reading the Excel file with COM Interop?

    EPPlus is a capable, .NET (no COM) open source solution available via NuGet for reading .XLSX files. I moved from OleDb to EPPlus some years ago for my projects that deal with Excel data.