[Last Call] Learn how to a build a cloud-first strategyRegister Now


Union two DataTables / reading input from Excel and text file

Posted on 2009-12-28
Medium Priority
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
  • 3
  • 2
LVL 42

Expert Comment

by:Meir Rivkin
ID: 26137553
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

by:Meir Rivkin
ID: 26137560
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?

Author Comment

ID: 26137649
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

by:Meir Rivkin
ID: 26137889
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


Accepted Solution

mmeisel earned 0 total points
ID: 26515163
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.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month17 days, 20 hours left to enroll

831 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