Updating values in .csv file depend to values in second .csv

Question

I have two csv files, which contains some data. One of them looks like this:

drid;aid;1date;2date;res;
2121;12;"01.11.2019 06:49";"01.11.2019 19:05";50;
9;10;"01.11.2019 10:47";"01.11.2019 11:33";0;
72;33;"01.11.2019 09:29";"01.11.2019 14:19";0;
777;31;"03.11.2019 04:34";"03.11.2019 20:38";167,35;

Second scv looks like this

datetime;res;drid
"2019-11-01 09:02:00";14,59;2121
"2019-11-03 12:59:00";25,00;777

My target to compare day of date also "drid" and if they are the same in both files then get sum of "res" and replace values of "res" in first csv. Result have to looks like this:

2121;12;"01.11.2019 06:49";"01.11.2019 19:05";64,59;
9;10;"01.11.2019 10:47";"01.11.2019 11:33";0;
72;33;"01.11.2019 09:29";"01.11.2019 14:19";0;
777;31;"03.11.2019 04:34";"03.11.2019 20:38";192,35;

What I have to do to obtain that results in vb.net? I tried to use LINQ Query, but with no results, because I'm newbie and I didn't find way to declare variables in two files and then compare it. Ok, with .bat I made join both csv in one big.csv and tried to obtain results from same file, but again without success. Last one code is:

Private Sub Button12_Click(sender As Object, e As EventArgs) Handles Button12.Click
       Dim Raplines As String() = IO.File.ReadAllLines("C:\Users\big.csv")
       Dim strList As New List(Of String)
       Dim readFirst As Boolean
       For Each line In Raplines
           If readFirst Then
               Dim strValues As String() = line.Split(";")
               Dim kn1 As String = strValues(0)
               Dim kn2 As String = strValues(59)
               Dim pvm1 As Date = strValues(2)
               Dim pvm1Changed = pvm1.ToString("dd")
               Dim pvm2 As Date = strValues(3)
               Dim pvm2Changed = pvm2.ToString("dd")
               Dim pvm3 As Date = strValues(60)
               Dim pvm3Changed = pvm3.ToString("dd")
               Dim Las1 As Decimal = strValues(9)
               Dim Las2 As Decimal = strValues(61)
               Dim sum As Decimal = Las1 - Las2
               If kn1 = kn2 And pvm3Changed = pvm1Changed Or pvm3Changed = pvm2Changed Then
                   strValues(9) = sum
                   strList.Add(String.Join(";", strValues))
               End If
           End If
           readFirst = True
       Next
       IO.File.WriteAllLines("C:\Users\big_new.csv", strList.ToArray())
   End Sub

Mary · Accepted Answer · 2019-11-10 03:00:25Z

1

Instead of changing the existing file I wrote a new one. I used a StringBuilder so the runtime would not have to create and throw away so many strings. StringBuilder are mutable unlike Strings. I parsed the different formats of the dates and used .Date to disregard the Times.

Private Sub ChangeCVSFile()
    Dim lines1 = File.ReadAllLines("C:\Users\someone\Desktop\CSV1.cvs")
    Dim lines2 = File.ReadAllLines("C:\Users\someone\Desktop\CSV2.cvs")
    Dim sb As New StringBuilder
    For Each line1 In lines1
        Dim Fields1 = line1.Split(";"c) 'drid;aid;1date;2date;res
        For Each line2 In lines2
            Dim Fields2 = line2.Split(";"c) 'datetime;res;drid
            '                                  
            '                        Trim the exta double quotes   "01.11.2019 06:49"
            Dim d1 = DateTime.ParseExact(Fields1(2).Trim(Chr(34)), "dd.MM.yyyy hh:mm", CultureInfo.InvariantCulture).Date
            '                                                      "2019-11-01 09:02:00"
            Dim d2 = DateTime.ParseExact(Fields2(0).Trim(Chr(34)), "yyyy-MM-dd hh:mm:ss", CultureInfo.InvariantCulture).Date
            If Fields1(0) = Fields2(2) AndAlso d1 = d2 Then
                Dim sum = CDec(Fields1(4)) + CDec(Fields2(1))
                Fields1(4) = sum.ToString
            End If
        Next
        sb.AppendLine(String.Join(";", Fields1))
    Next
    File.WriteAllText("C:\Users\someone\Desktop\CSV3.cvs", sb.ToString)
End Sub

edited Nov 10, 2019 at 3:00

answered Nov 10, 2019 at 2:52

Mary

15.1k3 gold badges20 silver badges27 bronze badges

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

1 Comment

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

Tank you very much! It's work for me with small changes.

Collectives™ on Stack Overflow

Updating values in .csv file depend to values in second .csv

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