Union two DataTables / reading input from Excel and text file

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

Who is Participating?
mmeiselConnect With a Mentor Author Commented:
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.

Meir RivkinFull stack Software EngineerCommented:
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))

Meir RivkinFull stack Software EngineerCommented:
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?
mmeiselAuthor Commented:
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?
Meir RivkinFull stack Software EngineerCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.