|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Error: Cannot change DataType of a column once it has datafile. 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,
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 Show quote "Matt Fielder" <mfielderREMOVECAPS@nospam.nospam> schreef in bericht news:eQ2ZKTn6GHA.4232@TK2MSFTNGP02.phx.gbl... > 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 > 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. Show quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:epcOP3o6GHA.4996@TK2MSFTNGP04.phx.gbl... > 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" <mfielderREMOVECAPS@nospam.nospam> schreef in bericht > news:eQ2ZKTn6GHA.4232@TK2MSFTNGP02.phx.gbl... >> 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 >> > > 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. Show quote "Matt Fielder" <mfielderREMOVECAPS@nospam.nospam> wrote in message news:eimOluv6GHA.2288@TK2MSFTNGP05.phx.gbl... >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. > > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message > news:epcOP3o6GHA.4996@TK2MSFTNGP04.phx.gbl... >> 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" <mfielderREMOVECAPS@nospam.nospam> schreef in bericht >> news:eQ2ZKTn6GHA.4232@TK2MSFTNGP02.phx.gbl... >>> 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 >>> >> >> > > 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.)  |
|||||||||||||||||||||||
Other interesting topics