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

Posted on 2007-07-21
Last Modified: 2013-11-26

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  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
Question by:AWestEng
    LVL 34

    Accepted 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.

    LVL 1

    Author Comment

    I also need to convert the datatype string to the correct datatype for the new table
    LVL 34

    Expert Comment

    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.


    LVL 1

    Author Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
    The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
    THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now