1

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?

josh
  • 671
  • 5
  • 10
  • Hmmm, just thinking about logic I can suggest you to just try to cast them to date at first time, if it not work, try to cast to number, than if it is nor number, well is a string. Not clean, not even close to fency but it will work. – Andrew Paes Jul 20 '15 at 18:03
  • Sorry, but it won't work, because BOTH casts will succeed in a date field. I want to know whether I have a date or a number in that field. – josh Jul 20 '15 at 18:06

1 Answers1

1

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.

Eric J.
  • 147,927
  • 63
  • 340
  • 553
  • Ad 1. No, OleDb returns a string data type if you put IMEX=1 in the connect string. Ad 2. thanks for the hint, I will give it a try. – josh Jul 20 '15 at 18:08
  • I tried EPPlus, and it seems a very nice library. It returns the data types of all fields correctly! Thanks for the valuable hint!. Upvoted! – josh Jul 20 '15 at 22:45
  • 1
    @EddyJawed: That is correct. If you need to work with CSV files, I recommend http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader. I'm not aware of a good open source solution for old format Excel files (and doubt anyone is working on one), though there are a few commercial options. – Eric J. Aug 05 '15 at 16:06