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

[Matt Fielder]

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

 

[Cor Ligthert [MVP]]

Matt,

You can use an extra column in your table for your double value, don't
forget to set in your header that all columns has to be threaten as strings.
(Imex=1 IntermixexData)

http://www.connectionstrings.com/

I hope this helps,

Cor

 

[Matt Fielder]

I don't really understand the answer. the phrase "don't forget to set in
your header that all columns ... as string" indicates that maybe there is a
way of setting the data so that everything comes in to the datatable as a
string - but don't see how.

Matt,

You can use an extra column in your table for your double value, don't
forget to set in your header that all columns has to be threaten as
strings. (Imex=1 IntermixexData)

http://www.connectionstrings.com/

I hope this helps,

Cor

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

 

[Matt Fielder]

Okay, I figured it out:

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"""


Thanks Cor, I really appreciate it.

I don't really understand the answer. the phrase "don't forget to set in
your header that all columns ... as string" indicates that maybe there is a
way of setting the data so that everything comes in to the datatable as a
string - but don't see how.

Matt,

You can use an extra column in your table for your double value, don't
forget to set in your header that all columns has to be threaten as
strings. (Imex=1 IntermixexData)

http://www.connectionstrings.com/

I hope this helps,

Cor

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

 

[Kevin Yu [MSFT]]

Thanks to Cor for the answer.

Matt,

You can also achieve this by creating a typed DataSet, map the double
column to a string column using ColumnMapping will do it for you.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)