Assign values to CSV file from another

Question

I have two csv files which contains calendar for 10 cars and for 15 drivers. Target is to join both calendars.

Car.csv looks like this

Car 1;A
Car 2;A
Car 3;I
Car 4;A
Car 5;A
Car 6;I
Car 7;A
Car 8;A
Car 9;Y
Car 10;A

Driver.csv looks like this

Driver 1;V
Driver 2;V
Driver 3;Y
Driver 4;A
Driver 5;A
Driver 6;V
Driver 7;A
Driver 8;I
Driver 9;I
Driver 10;V
Driver 11;V
Driver 12;A
Driver 13;A
Driver 14;A
Driver 15;A

Desired output have to be

Car 1;Driver 4
Car 2;Driver 5
Car 3;Driver 8
Car 4;Driver 7
Car 5;Driver 12
Car 6;Driver 9
Car 7;Driver 13
Car 8;Driver 14
Car 9;Driver 3
Car 10;Driver 15

My code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim car As String() = IO.File.ReadAllLines("path\car.csv").ToArray
        Dim drivers As String() = IO.File.ReadAllLines("path\driver.csv").ToArray
        Dim sb As New List(Of String)
        For Each line In car ' loop throw car
            Dim Fields1 = line.Split(";"c)
            For Each line1 In drivers 'loop throw driver
                Dim Fields2 = line1.Split(";"c)
                Dim driver As String = Fields2(0)
                If Fields1(1) = Fields2(1) Then
                    Fields1(1) = driver
                    sb.Add(String.Join(";", Fields1))
                End If
            Next
        Next
        IO.File.WriteAllLines("path\joined.csv", sb.ToArray())
    End Sub

Unfortunately output looks wrong

Car 1;Driver 4
Car 2;Driver 4
Car 3;Driver 8
Car 4;Driver 4
Car 5;Driver 4
Car 6;Driver 8
Car 7;Driver 4
Car 8;Driver 4
Car 9;Driver 3
Car 10;Driver 4

How to avoid assigning of already used values? I tried to paste in inner Loop Fields2(1)="used", but it doesn't won't work. And I got lost here.

Because I'm more familiar with parsing csv in vb.net I wrote code with it, but additionally I need it for whole calendar with 31 columns and in Excel VBA, but this is another story.

Thank you for answers!

Answer 1

This should get you pretty close.

Private Sub MatchCarsButton_Click(sender As Object, e As EventArgs) Handles MatchCarsButton.Click

    Try

        Dim cars As List(Of String) = File.ReadAllLines("cars.csv").ToList
        Dim drivers As List(Of String) = File.ReadAllLines("drivers.csv").ToList
        Dim result As List(Of String) = MatchVehicles(cars, drivers)
        File.WriteAllLines("out.csv", result)

    Catch ex As Exception

        MessageBox.Show(String.Concat("An error occurred :", ex.Message))

    End Try

End Sub

Private Function MatchVehicles(cars As List(Of String), drivers As List(Of String)) As List(Of String)

    Dim result As New List(Of String)

    For Each car As String In cars
        Dim carVals() As String = car.Split(";"c)
        For Each driver As String In drivers
            Dim driverVals() As String = driver.Split(";"c)
            If carVals(1) = driverVals(1) Then
                result.Add(String.Format("{0};{1}", carVals(0), driverVals(0)))
                drivers.Remove(driver) ' remove them from the list
                Exit For ' exit the loop
            End If

        Next
    Next

    Return result

End Function