none
Using OLEDB with VB.NET to read Excel workbook RRS feed

  • Question

  • I am reading an Excel file using OLEDB connection using a VB.NET program and getting a dataset of my selected worksheet. The dataset is a single worksheet that I input using a SELECT statement and it returns the correct number of rows.

    My problem is that the cells formatted as date are responding in an inconsistent manner. All of the cells are formatted as date on the work sheet and when I read the dataset row in my VB.NET program, some dates are 100% correct, others are DBNull value, and others are coming in as a general format number representation of the date. All other data is 100% correct - it just these pesky date fields that are giving me grief.

    All of the dates are contained in "merged" columns (2 or more) and I am referencing the column by its relative number (A=0, B=1, etc). If reference the 1st column of the merged group.

    Has anyone had any of the same experiences and if so how did they get solved? I am under the gun with a project and I need to get this situation resolved.

    Thanks to all who respond.
    Monday, March 7, 2011 2:05 PM

Answers

  • Not sure what your connection string looks like, but I would make certain to add the IMEX argument and give it a value of one. You may also need to change the TypeGuesRows entry in the Registry depending upon which OLEDB Provider you are using.

    http://support.microsoft.com/kb/189897

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 7, 2011 2:58 PM
  • This is the limitation of Jet and ACE OLEDB providers when datatypes are mixed in same column. You can read all the values as strings if you set IMEX=1 in Extended Properties of the connection string. It would force OLEDB provider to treat all the cell values as strings. Then you would need to convert specific items into proper datatype inside of client code.

    I have created .NET Managed provider with read-only capabilities that returns all the values in their native format. You can try it from my web site

     


    Val Mazur (MVP)

    http://www.xporttools.net

    Friday, March 11, 2011 2:37 AM
    Moderator

All replies

  • Not sure what your connection string looks like, but I would make certain to add the IMEX argument and give it a value of one. You may also need to change the TypeGuesRows entry in the Registry depending upon which OLEDB Provider you are using.

    http://support.microsoft.com/kb/189897

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 7, 2011 2:58 PM
  • This is the limitation of Jet and ACE OLEDB providers when datatypes are mixed in same column. You can read all the values as strings if you set IMEX=1 in Extended Properties of the connection string. It would force OLEDB provider to treat all the cell values as strings. Then you would need to convert specific items into proper datatype inside of client code.

    I have created .NET Managed provider with read-only capabilities that returns all the values in their native format. You can try it from my web site

     


    Val Mazur (MVP)

    http://www.xporttools.net

    Friday, March 11, 2011 2:37 AM
    Moderator