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!

Each time you match a driver, either remove it from the array or mark it matched otherwise the same one will continue to be matched each time. — Jon Roberts
– Jon Roberts, Commented May 15, 2020 at 16:09
@JonRoberts Yes, I understend that I have to put somewhere hasmutch boolean, but I just can’t figure it out. — Иван Олександров
– Иван Олександров, Commented May 15, 2020 at 17:07

Andrew Mortimer · Accepted Answer · 2020-05-15 17:43:12Z

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

answered May 15, 2020 at 17:43

Andrew Mortimer

2,3707 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Иван Олександров Over a year ago

Thank you @Andrew! It work now for one column, will try to do rest by myself.

Collectives™ on Stack Overflow

Assign values to CSV file from another

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related