1

I need to read data from an Excel sheet as part of a data conversion. Certain columns contain mostly numeric data but may contain some alphanumeric data somewhere in the excel sheet. The problem is, my conversion sees the alphanumeric values as null (or blank. Using the .ToString() method returns "").

To connect to Excel, I'm creating an oledb connection, creating an OleDbDataAdapter, then filling a DataSet with the adapter.

Here's the VB code for connecting:

private _oleadpt As OleDbDataAdapter
private  _oleconnection As New OleDbConnection

       Dim olecomm As OleDbCommand                
       '_database comes from a settings file and is the full path to an excel document
        Dim connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _database & ";Extended Properties=""Excel 8.0;HDR=YES;"""
        _oleconnection.ConnectionString = connstring

        olecomm = New OleDbCommand
        olecomm.CommandText = "SELECT RegionalBranch, DocumentType, TiffFileNumberReference, VersionNumber, RTSItemNumber, ItemSearch, HeatNumber, RTSVendorNumber, PurchaseOrderNumber, Branch, " + _
            "Quality, CreationDate, CreationTime, ReceiverNumber, ChathamItemNumber, ChathamVendorNumber, ChathamDivision, Processed FROM [Sheet1$]"
        olecomm.Connection = _oleconnection

        _oleadpt = New OleDbDataAdapter(olecomm)
        Dim commandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(_oleadpt)

        ds = New DataSet
        _oleconnection.Open()
        _oleadpt.Fill(ds)

I think the data adapter is determining the datatype for the columns based on the first however-many rows it looks at. It decides the columns with numbers are numeric which is the start of my problem.

Changing the formatting of the column in Excel doesn't seem to affect the datatypes in my data set.

Is there a way to tell the dataadapter or dataset what type of data to use for a column? Or should I try to cast the data in my SQL statement?

Any help would be much appreciated!

allen.mn
  • 467
  • 3
  • 11
  • See this answer; it should fix you up: http://stackoverflow.com/questions/3081708/excel-cell-formatting-problem – codechurn Feb 09 '12 at 01:17

1 Answers1

2

I tested out the comment above and found that if you are using the Microsoft.Jet.OLEDB.4.0 provider to read the Excel File, the MaxScanScanRows=0 setting is ignored (as documented in this KB). If however I switch to the Microsoft.ACE.OLEDB.12.0 provider it works as expected.

codechurn
  • 3,870
  • 4
  • 45
  • 65
  • Thanks for the help! It seems the jet 4.0 driver will always scan exactly 8 rows which wasn't helpful to me. Just changing to ACE 12.0 didn't fix the problem. Even when I added maxscanrows=0. The solution was to add IMEX=1 to the extended properties in the connection string. That treats all rows as text, which is ultimately what I wanted anyway. This article was helpful to me too: http://www.connectionstrings.com/excel-2007. +1 for sending me in the right direction! – allen.mn Feb 09 '12 at 16:00
  • Yup, sorry if it was not clear. You need IMEX=1 and MaxScanRows=0 in the extended properties. Glad you got it working! – codechurn Feb 09 '12 at 18:25