VB.net total sum from a csv file

Question

I have CSV file that contains some values. It's looks like this:

1;11,19;91,69
1;2,8  ;26,54
2;0    ;2,79
2;7,8  ;0
3;0    ;35,44
1;0    ;8,89
2;27,33;16,5
3;0    ;35,49
2;0    ;42,69
2;0    ;93,09

I need to write new strings to new CSV which contains sums of columns 1 and 2 sorted by column 0. It have to looks like this:

1;13,99;127,12
2;35,13;155,07
3;0    ;70,93

At the start I tried to calculate sums of columns, but even there I got a problem and can't figure out how I have to do this. And I am to far of my target with sorting by values of column 0. Now my code looks like this.

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim file As String() = IO.File.ReadAllLines("path\input.csv")
        Dim sum As Decimal
        sum = 0
        Dim sb As New List(Of String)
        For Each line In file
            Dim col = line.Split(";"c)
            Dim result As Decimal = sum + col(1)
            Dim result1 As Decimal = sum + col(2)
            sb.Add(String.Join(";", col(0) & ";" & result & ";" & result1))
        Next
        IO.File.WriteAllLines("path\output.csv", sb.ToArray())
    End Sub

I seen the same question with answers for Python, but I have no idea how to transform code to VB.Net. EDIT: Have to say, that numbers in column 0 are just examples and there are more then 50 unique values.

Answer 1

To clarify, actually you don't need to sort the entries, you should Group them by the first column and sum the second and third ones of each group.

Here's another solution:

Imports System.IO
Imports System.Linq
Imports System.Globalization

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim inputFile = "path/input.csv"
    Dim outputFile = "path/output.csv"
    Dim ci = New CultureInfo("de-DE")

    File.WriteAllLines(outputFile, File.ReadLines(inputFile).
                        Select(Function(x) x.Split(";"c)).
                        GroupBy(Function(x) Integer.Parse(x(0))).
                        Select(Function(x)
                                    Return String.Format(
                                    "{0};{1};{2}",
                                    x.Key,
                                    x.Sum(Function(y) Decimal.Parse(y(1), ci)),
                                    x.Sum(Function(y) Decimal.Parse(y(2), ci)))
                                End Function).ToArray)
End Sub

Answer 2

This should get you fairly close:

Dim result = _
    lines _
        .Select(Function(x) x.Split(";"c)) _
        .Select(Function(x) New With _
        { _
            .c1 = x(0), _
            .c2 = Decimal.Parse(x(1).Trim(), System.Globalization.CultureInfo.GetCultureInfo("de-de")), _
            .c3 = Decimal.Parse(x(2).Trim(), System.Globalization.CultureInfo.GetCultureInfo("de-de")) _
        }) _
        .GroupBy(Function(x) x.c1) _
        .Select(Function(x) New With { .c1 = x.Key, .c2 = x.Sum(Function(y) y.c2), .c3 = x.Sum(Function(y) y.c3) }) _
        .ToArray()

That gives me the numbers you wanted.