Error: Cannot change DataType of a column once it has data

[SavantMan]

I'm running into a problem when filling a DataTable with data from an Excel file. The DataTable is filled with datatypes that the fill method has "guessed" are correct -- the problem is I need them to be treated as strings, and sometimes they come in as Double. When attempting to change the datatype property of the columns, I get a "Cannot change DataType of a column once it has data." error, which makes perfect sense I suppose. I'm guessing even if the conversion was successful my string data wouldn't be there. So how do I get the data to pull in with all columns having a datatype of string instead of what the fill method "guesses" it should be?

   Private Function ExcelToDataSample(ByVal ExcelFilePath As String, ByVal SheetName As String) As DataTable

      Dim dt As New DataTable
      Dim excelAdapter As New OleDbDataAdapter
      Dim con As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
        "data source=" & ExcelFilePath & ";" & _
        "Extended Properties=""Excel 8.0;HDR=NO""")

      excelAdapter = New System.Data.OleDb.OleDbDataAdapter( _
            "select TOP 10 * from [" & SheetName & "$]", con)

      excelAdapter.Fill(dt)
      
      For Each col As DataColumn In dt.Columns
         If Not col.DataType Is GetType(System.String) Then
            ' "Cannot change DataType of a column once it has data."  Error
            col.DataType = GetType(System.String) '<-- ERROR OCCURS HERE
         End If
      Next

      If Not dt Is Nothing Then
         Return dt
      Else
         Return Nothing
      End If

   End Function

 

[BobRodes]

This is a VB6 forum. You'll want to find a forum for VB.Net or ADO.Net.

HTH

Bob

 

[SavantMan]

Oops - sorry about that --- didn't realize it was a VB6 forum. I did find the answer and figured I'd drop in and post in for others in case they need it. I'll still post the answer since this thread will be searchable.

Changing the ExtendedProperties part of the connection string to include IMEX=1 forces the data to all be imported as string:

"Extended Properties=""Excel 8.0;HDR=NO"""
Should be changed to:
"Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""

 

[BobRodes]

Yeah, I bet they came up with the name for it before there was a vb.net!