I need some help with reading a csv file into a database.
This is what I want to do. ( I use VS 2005 VB.Net and MySQL 5)
1: Read a CSV file to a datatable (all are string columns)
(1 is solved)
2: Create a new datatable with the correct types from the database, by using a dataadapter that returns an emprty datatable
(2 is solved)
3: Read the CSV datatable, fix the values and copy the data to the SQL datatable.
(3 is NOT solved)
I have solved step 1 and 2, but not step 3.
I have my CSVDataTable (with all data in it) I have my SQLDataTable (emprty datatable)
Bur now I don't know how to copy the data from CSVDataTable to the SQLDataTable.
The values in the CSVDataTable needs to be modified before they are inserted to the new datatable.
And the SQLDataTable are from a MySQL 5 database table so I need to use VarChar and date, tinyint in te datatype
I use MySQL.data to connect to the database
Here is the code I have to fix the table so far..
Private Function CreateSQLTable(ByVal dt As DataTable, ByVal SQLcolumNames() As String) As DataTable
Dim MyConString As String = _
"Persist Security Info=False;" & _
"DATABASE=test;" & _
"PORT=3307;" & _
"SERVER=127.0.0.1;" & _
"Connect Timeout=30;" & _
"user id=test;" & _
Dim SQLQuery As String = "SELECT * FROM resa WHERE FileType= 'OPS' LIMIT 1"
Dim dbconn As New MySqlConnection
Dim objCmd As New MySqlCommand(SQLQuery)
Dim objAdapter As MySqlDataAdapter = Nothing
Dim myDataTable As New DataTable
objCmd.Connection = dbconn
objAdapter = New MySqlClient.MySqlDataAdapter(objCmd)
'Open the connection and execute the reader
dbconn.ConnectionString = MyConString
' Create the empty SQLDataTable
Catch ex As Exception
'delete the first row from CSV datatable (column names)
'Fix values and copy the data from dt to the SQLDataTable
For Each MyRow As DataRow In dt.Rows
For Each myColumn As DataColumn In dt.Columns
''''' Here is where i need the help
'''' How do I transfer the data from one table (string to a table witg diffrent datataypes)
So What I need help with is to copy the data from the CSVDataTable to the SQLDataTable