VB.Net - Combine two text files into one with matching first column

PNRT
PNRT used Ask the Experts™
on
Hi Experts.  I have two comma delimited text files.  One with four columns and one with two columns. All values are strings.   The first column of each file is an ID string common to both but not necessarily in the same order.  They both have the same number of rows and they shouldnt be any blanks in the files.

I need to take the second column of the file with two columns into the file with four columns, making five columns.   The data in each file must correspond with the ID number of the first columns in both files.

It would be good to have the data in a third file if thats OK
I hope this makes sense

Any help with the code on this would be greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Please see the below code snippet logic

and use this link to reexport your DataTable to CSV
http://dotnetguts.blogspot.com/2007/01/exporting-datatable-to-csv-file-format.html
string filename1 = "d:\\file1.csv";
            string filename2 = "d:\\file1.csv";
            FileInfo file1 = new FileInfo(filename1);
            FileInfo file2 = new FileInfo(filename2);
            DataTable tbl1;

            using (OleDbConnection con =
                   new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
            {
                using (OleDbCommand cmd = new OleDbCommand(string.Format
                                          ("SELECT * FROM [{0}]", file1.Name), con))
                {
                    con.Open();

                    // Using a DataTable to process the data
                    using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                    {
                        tbl1 = new DataTable("MyTable");
                        adp.Fill(tbl1);



                    }
                }
            }

            // Reading file 2
            using (OleDbConnection con =
                    new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
            {
                using (OleDbCommand cmd = new OleDbCommand(string.Format
                                          ("SELECT * FROM [{0}]", file2.Name), con))
                {
                    con.Open();

                    // Using a DataTable to process the data
                    using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                    {
                        OleDbCommandBuilder cmdBuilder;
                        cmdBuilder = new OleDbCommandBuilder(adp);

                        DataTable tbl2 = new DataTable("MyTable");
                        adp.Fill(tbl2);
                        // Do you manipulation operation
                       // Export to CSV
                    }
                }
            }

Open in new window

Author

Commented:
Thank you very much for your reply.

I converted the code to VB which seemed to be fine and shows no errors
The conversion is below.
But when run I get an error in the line  - adp.Fill(tbl1)
This is the error.

The Microsoft Jet database engine could not find the object 'A1.csv'.  Make sure the object exists and that you spell its name and the path name correctly.

It seems a simple error, but I have renamed the file, put it in different folders and made sure it will open
but the error persists.   I remed out that line and got the same error for the second file

Also, where you have put "manipulation operation", please could you explain how I would read the datatable in order to produce a text file with the resultant 5 columns.

Many Thanks again
 
Dim filename1 As String = "c:\ATest\A1.csv"
        Dim filename2 As String = "c:\ATest\A2.csv"
        Dim file1 As New FileInfo(filename1)
        Dim file2 As New FileInfo(filename2)
        Dim tbl1 As DataTable

        Using con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';")
            Using cmd As New OleDbCommand(String.Format("SELECT * FROM [{0}]", file1.Name), con)
                con.Open()

                ' Using a DataTable to process the data
                Using adp As New OleDbDataAdapter(cmd)
                    tbl1 = New DataTable("MyTable")

                    adp.Fill(tbl1)
                End Using
            End Using
        End Using

        ' Reading file 2
        Using con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';")
            Using cmd As New OleDbCommand(String.Format("SELECT * FROM [{0}]", file2.Name), con)
                con.Open()

                ' Using a DataTable to process the data
                Using adp As New OleDbDataAdapter(cmd)
                    Dim cmdBuilder As OleDbCommandBuilder
                    cmdBuilder = New OleDbCommandBuilder(adp)

                    Dim tbl2 As New DataTable("MyTable")
                    ' Do you manipulation operation
                    ' Export to CSV
                    adp.Fill(tbl2)
                End Using
            End Using
        End Using
Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015
Commented:
What about something like this:
Sub Main()
    Dim file1 As New Dictionary(Of String, String)

    ' Load dictionary with ID (key) and data (value)
    Using reader As New System.IO.StreamReader("test1.txt")
        While Not reader.EndOfStream
            Dim str As String = reader.ReadLine()
            Dim temp() As String = str.Split(",")

            file1.Add(temp(0), str)
        End While
    End Using

    ' Update each key in dictionary with data from second file
    Using reader As New System.IO.StreamReader("test2.txt")
        While Not reader.EndOfStream
            Dim str As String = reader.ReadLine()
            Dim temp() As String = str.Split(",")

            file1(temp(0)) = String.Concat(file1(temp(0)), ",", temp(1))
        End While
    End Using

    ' Write out dictionary to new file
    Using writer As New System.IO.StreamWriter("result.txt")
        For Each item As KeyValuePair(Of String, String) In file1
            writer.WriteLine(item.Value)
        Next
    End Using

End Sub

Open in new window

kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
This is a duplicate question of http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/Q__26301579.html

Which question are you seeking answers for?

Author

Commented:
This was a duplicate question as the first was left unanswered.  I shall attempt to delete the first one.

Your solution worked immediately as requested - thank you


kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
No problem. Glad to help. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial