2

Have a really weird problem with reading xlsx file(I'm using OleDbDataReader).

I have a column there that consist of the following data:

  • 50595855
  • 59528522
  • C_213154
  • 23141411

The problem is that when I read this column the reader shows me that the third row is empty. The column format in Excel is set to 'General'. But when I set the format to 'Text', everything works fine and reader sees the data in that row.

So just for a sake of experiment, I prefixed first two rows with letter and made it look like following :

  • C_50595855
  • C_59528522
  • C_213154
  • 23141411

And the reader reads everything without problem even when the column format is set to 'General'.

So Excel apparently somehow analyses data in the column before loading it, and it gets confused when first cells of the column look like numeric and some of the rest are texts..

It is really weird to me as either there is data in the cell or there isn't.

Anyone have any ideas why this is happening?

Any help will be much appreciated.

Regards, Igor

IgorShch
  • 149
  • 1
  • 4
  • 21

1 Answers1

4

As you surmised it's an issue caused by mixed data types. If you search on "OleDBDataReader mixed types" you'll get some answers. Here's an MSDN page that describes the problem:

"This problem is caused by a limitation of the Excel ISAM driver in that once it determines the datatype of an Excel column, it will return a Null for any value that is not of the datatype the ISAM driver has defaulted to for that Excel column. The Excel ISAM driver determines the datatype of an Excel column by examining the actual values in the first few rows and then chooses a datatype that represents the majority of the values in its sampling."

... and the solution(s):

"Insure that the data in Excel is entered as text. Just reformatting the Excel column to Text will not accomplish this. You must re-enter the existing values after reformatting the Excel column. In Excel, you can use F5 to re-enter existing values in the selected cell.

You can add the option IMEX=1; to the Excel connect string in the OpenDatabase method. For example:

Set Db = OpenDatabase("C:\Temp\Book1.xls", False, True, "Excel 8.0; HDR=NO; IMEX=1;")

"

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • Thanks a lot for reply, Doug. Really appreciate it. – IgorShch Jul 11 '12 at 17:12
  • 1
    FWIW: I discovered a similar problem whereby numeric values were being _rounded_ by the Excel ISAM driver before being converted to strings. For example, **188743687** was returned to the OleDbDataReader as the string "**188743680**". The solution was to re-enter all values after the format of the column was set to Text. Note: this happened on Windows 7 (64-bit) with Office 2010 (32-bit), but was not an issue on Windows XP and Office 2007 (both 32-bit). Note 2: the values seemed to be rounded to the nearest multiple of 16. – Rachel Hettinger Oct 16 '13 at 21:16
  • 1
    @RachelHettinger, Yikes! – Doug Glancy Oct 16 '13 at 21:25
  • After a little more research I determined that at some point in the retrieval of the data, the values are stored as a single-precision floating point and are thus truncated to 23-bits. – Rachel Hettinger Oct 17 '13 at 18:10