MichMat
asked on
Importing CSV into DataTable datatype problem VB 2010
Hi
Im importing a CSV file into a datatable, it all goes well, except that I have a few columns that have rows which contain eaither a number or "????" . Unfortunatly the code that I have converst this column of data into double and my "????" are then turned into zero.
I would like to change the data columns datatype but I havent been able to do so even though I change the type prior to filling the table (see code). how do force the columns to be read as text ?
Michal
Im importing a CSV file into a datatable, it all goes well, except that I have a few columns that have rows which contain eaither a number or "????" . Unfortunatly the code that I have converst this column of data into double and my "????" are then turned into zero.
I would like to change the data columns datatype but I havent been able to do so even though I change the type prior to filling the table (see code). how do force the columns to be read as text ?
Michal
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path.GetDirectoryName(CSVName) & "\;Extended Properties=""text;HDR=YES;FMT=Delimited"""
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
Dim da As OleDb.OleDbDataAdapter
'Open Data Adapter to Read from Text file
Dim str As String = "SELECT * FROM [" & Path.GetFileName(CSVName) & "]"
da = New System.Data.OleDb.OleDbDataAdapter(str, objConn)
'Fill dataset using dataadapter
da.FillSchema(SystemTable, SchemaType.Mapped)
For Each column As DataColumn In SystemTable.Columns
If column.ColumnName = "WINDIV" Then column.DataType = System.Type.GetType("System.String")
If column.ColumnName = "PLCDIV" Then column.DataType = System.Type.GetType("System.String")
If column.ColumnName = "SPDIV" Then column.DataType = System.Type.GetType("System.String")
Next
da.Fill(SystemTable)
objConn.Close()
ASKER
throws up an error Im sorry to say that the column data type is not recognised
see: http://www.functionx.com/vbnet/oledb/Lesson02.htm
There are two primary pieces of information you must specify for each column: its name and its type. Therefore, the syntax of creating a column is:
ColumnName DataType Options
Change:
column.DataType = System.Type.GetType("Syste m.String")
To this:
columnName.DataType = System.Type.GetType("Syste m.String")
or
columnName.DataType = System.Type.GetType("Syste m.Char")
or
columnName.DataType = System.Type.GetType("Syste m.SqlStrin g")
There are two primary pieces of information you must specify for each column: its name and its type. Therefore, the syntax of creating a column is:
ColumnName DataType Options
Change:
column.DataType = System.Type.GetType("Syste
To this:
columnName.DataType = System.Type.GetType("Syste
or
columnName.DataType = System.Type.GetType("Syste
or
columnName.DataType = System.Type.GetType("Syste
forgot to tell you to replace columnName above with your actual column name.
ASKER
Hi ,
Thanks for helping , so far all I can gathre that Microsoft will relentlesly 'help' me !
System.ArgumentException: Invalid cast from 'Double' to 'Char'.Couldn't store <4> in WINDIV Column.
the SQLSTring get the same datatype error as mentioned in previous post.
It seam a crazy behaviour, I can load the table by using streemreader and get what I need but this process takes a minute and a bit to load the table , splitting each line as opposed to less then 10 seconds with the way that I have here.
Thanks for helping , so far all I can gathre that Microsoft will relentlesly 'help' me !
System.ArgumentException: Invalid cast from 'Double' to 'Char'.Couldn't store <4> in WINDIV Column.
the SQLSTring get the same datatype error as mentioned in previous post.
It seam a crazy behaviour, I can load the table by using streemreader and get what I need but this process takes a minute and a bit to load the table , splitting each line as opposed to less then 10 seconds with the way that I have here.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi ,
Thanks , that is a veru usefull link. I didnt have any luck with varchar, I guess that JET is very limited in what it will accept. I have been able to change the tables columns as we seen , but JET still converts the data to what ever it thinks it should be and then tries to fit a square peg into a round hole. ie Double into a char or even string.
My only option may be to force the columns to be cast when they are read ...... that would mean writing out a select statment with 162 columns !! But I havent figured out how to cast , I have used it in SQL but Jet just refuses to accept it , but I havent done much reaserch in that area yet
Thanks , that is a veru usefull link. I didnt have any luck with varchar, I guess that JET is very limited in what it will accept. I have been able to change the tables columns as we seen , but JET still converts the data to what ever it thinks it should be and then tries to fit a square peg into a round hole. ie Double into a char or even string.
My only option may be to force the columns to be cast when they are read ...... that would mean writing out a select statment with 162 columns !! But I havent figured out how to cast , I have used it in SQL but Jet just refuses to accept it , but I havent done much reaserch in that area yet
change this portion of your code: column.DataType = System.Type.GetType("Syste
to this: column.DataType = System.Type.GetType("Strin