• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1312
  • Last Modified:

Vb.Net: Copy data from the one DataTable to another DataTable


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=;" & _
                           "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

            Return dt
        Catch ex As Exception
            Throw ex
        End Try

        '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)
        Return dt
    End Function

So What I need help with is to copy the data from the CSVDataTable to the SQLDataTable
  • 2
  • 2
1 Solution
Something like this

       'Fix values and copy the data from dt to the SQLDataTable
        For Each MyRow As DataRow In dt.Rows
            Dim dr As DataRow = mySQLDataTable.NewRow
            dr(0) = myRow(0)
            dr(1) = CInt(myRow(1))
            dr(2) = CDate(myRow(2))

The field/column numbers and the conversions are obviously just examples, but that's the general approach.  And it assumes you know - in the sense that you are able/willing to hard-code - which columns need which conversions.

AWestEngAuthor Commented:
I also need to convert the datatype string to the correct datatype for the new table
That's what this does

            dr(1) = CInt(myRow(1))

It assumes the value in myRow(1) is a String and it converts it, in that case, to an integer, before putting it in dr(1).   As I said, you will have to use whatever are the appropriate conversion functions for the datatypes concerned.


AWestEngAuthor Commented:
Oki.. thx :)

I need some help with inserting the data via a batch insert

My code deosn't work, But I will post a new question for that

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now