Union two DataTables / reading input from Excel and text file

Posted on 2009-12-28
Last Modified: 2012-05-08
I'm trying to merge two DataTables using the attached code snippet but keep getting an error.  I've tried using the Merge function but it combines the table in the opposite fashion of a Union (aka side-by-side rather then stack on top of each other).

I read one DataTable from an Excel spreadsheet and another from a tab delimited file.  I read them both without a problem.  The data in both files should be the same, it's just that one is in Excel format and the other is in tab delimited text file.  The problem is that the Excel DataTable has column DataTypes of Double and the Text File DataTable shows everything has a String.  Since the columns are of a different type I get the following error message:

"Input string was not in a correct format.Couldn't store <ABC123> in F1 Column.  Expected type is Double."

After reading both DataTables I want to merge them and write them back to the original Excel spreadsheet.

I'm basically trying to add the tab delimited content to the bottom of the Excel file.  If anyone has any other examples/suggestions that would be great.
Public Shared Function Union(ByVal First As DataTable, ByVal Second As DataTable) As DataTable
        'Result table
        Dim table As New DataTable("Union")

        'Build new columns
        Dim newcolumns As DataColumn() = New DataColumn(First.Columns.Count - 1) {}

        For i As Integer = 0 To First.Columns.Count - 1
            newcolumns(i) = New DataColumn(First.Columns(i).ColumnName, First.Columns(i).DataType)

        'add new columns to result table

        'Load data from first table
        For Each row As DataRow In First.Rows
            table.LoadDataRow(row.ItemArray, True)

        'Load data from second table
        For Each row As DataRow In Second.Rows
            table.LoadDataRow(row.ItemArray, True)

        Return table
    End Function

Open in new window

Question by:mmeisel
    LVL 42

    Expert Comment

    so change the code where u create the columns and set their type.

    instead of:
            For i As Integer = 0 To First.Columns.Count - 1
                newcolumns(i) = New DataColumn(First.Columns(i).ColumnName, First.Columns(i).DataType)


            For i As Integer = 0 To First.Columns.Count - 1
                newcolumns(i) = New DataColumn(First.Columns(i).ColumnName, GetType(String))

    LVL 42

    Expert Comment

    in another note, how u plan to merge the 2 tables if they have values which doesn't match the data type of the column?
    LVL 1

    Author Comment

    sedgwick, thats my problem.  All the values should be String but for some reason when I read the values from Excel it changes some columns Double (i.e. Product number).  So I'm trying to union a product # from Excel (Double) with a product number from a text file (String).

    Would it just be easier to find the first free cell in column A and start writing the new datatable there?  Right now I'm trying to combine new and old data then write it back to the Excel spreadsheet over the existing data.  Or maybe if I could use a .csv file, would that be easier to work with then Excel?
    LVL 42

    Expert Comment

    i'd use your code and just convert to string all rows values.
    its much simpler and quicker.
    if u create the union table with column type as string, the conversion should take place.
    in fact, i couldn't reproduce this error.
    i create 2 data tables with the same columns but different data types and another data table which merges them:

    Dim dt1 As New DataTable()
            dt1.Columns.Add("id", GetType(Integer))
            dt1.Columns.Add("product", GetType(String))
            dt1.Columns.Add("price", GetType(Double))
            dt1.Rows.Add(1, "apple", 1.5)
            dt1.Rows.Add(2, "orange", 2.5)
            dt1.Rows.Add(3, "mango", 3.5)
            Dim dt2 As New DataTable()
            dt2.Columns.Add("id", GetType(String))
            dt2.Columns.Add("product", GetType(String))
            dt2.Columns.Add("price", GetType(String))
            dt2.Rows.Add(1, "apricot", 5.1)
            dt2.Rows.Add(2, "grapes", 6.2)
            dt2.Rows.Add(3, "lemon", 7.3)
            Dim union As New DataTable()
            union.Columns.AddRange(dt1.Columns.Cast(Of DataColumn)().[Select](Function(n) New DataColumn()).ToArray())
            For Each item As DataRow In dt1.Rows
                union.LoadDataRow(item.ItemArray, True)
            For Each item As DataRow In dt2.Rows
                union.LoadDataRow(item.ItemArray, True)

    Open in new window

    LVL 1

    Accepted Solution

    None of the responses answered my question.  I did, however, find a work around:

    Start -> Run -> cmd
    Navigate to the directory where the .txt files are located
    Run this command: copy *.txt (compiled list file name).txt (i.e. copy *.txt test-import.txt)

    This will combine all the .txt files in that directory into the test-import.txt file.  Then all you have to do is right-click on "test-import.txt" and open in Excel.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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