.csv文件中的值更新取决于第二个.csv中的值

2024-05-04 • 问答

我有两个csv文件，其中包含一些数据。其中之一看起来像这样：

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;

第二个scv看起来像这样

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

我比较日期的目标也为“ drid”，如果两个文件中的日期相同，则获取“ res”的总和，并在第一个csv中替换“ res”的值。结果必须如下所示：

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;

要在vb.net中获得该结果我该怎么做？我尝试使用LINQ Query，但是没有结果，因为我是新手，我没有找到在两个文件中声明变量然后进行比较的方法。好的，使用.bat，我将两个csv都合并到一个big.csv中，并尝试从同一文件中获取结果，但是再次失败。最后一个代码是：

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

我写了一个新文件，而不是更改现有文件。我使用了StringBuilder，因此运行时不必创建和丢弃那么多字符串。与字符串不同，StringBuilder是可变的。我解析了日期的不同格式，并使用.Date忽略了时间。

rownum

这应该可以解决问题：


Option Strict Off 'To allow late binding.

Dim file1 As String = "c:\users\file1.csv"
Dim file2 As String = "c:\users\file2.csv"
Dim lst1 As New List(Of Object)
'Read all lines but the first one. Skipping -> drid;aid;1date;2date;res;
Dim file1Lines() As String = IO.File.ReadAllLines(file1).Skip(1).ToArray
'Read all lines of the second file but the first line. Skipping -> datetime;res;drid
Dim file2Lines() As String = IO.File.ReadAllLines(file2).Skip(1).ToArray

For Each line As String In file1Lines.Where(Function(a) Not String.IsNullOrEmpty(a))
    Dim arrLine() As String = line.Split({";"},StringSplitOptions.RemoveEmptyEntries)
    'Create a temporary anonymous object for each entry of the main file.
    Dim item As New With {
        .drid = Convert.ToInt32(arrLine(0)),.aid = Convert.ToInt32(arrLine(1)),.date1 = DateTime.ParseExact(arrLine(2).Replace("""",""),"dd.MM.yyyy HH:mm",Globalization.CultureInfo.InvariantCulture),.date2 = DateTime.ParseExact(arrLine(3).Replace("""",.res = Decimal.Parse(arrLine(4).Replace(",","."),Globalization.NumberStyles.Float)
    }
    lst1.Add(item)
Next

'The second file.
For Each line As String In file2Lines.Where(Function(a) Not String.IsNullOrEmpty(a))
    Dim arrLine() As String = line.Split({";"},StringSplitOptions.RemoveEmptyEntries)
    Dim newItem As New With {
        .DateTime = DateTime.ParseExact(arrLine(0).Replace("""","yyyy-MM-dd HH:mm:ss",.res = Decimal.Parse(arrLine(1).Replace(",Globalization.NumberStyles.Float),.drid = Convert.ToInt32(arrLine(2))
    }

    'Search for matches in the second file.
    Dim item = (From a In lst1 Where a.drid.Equals(newItem.drid) AndAlso a.date1.Date.Equals(newItem.DateTime.Date)).FirstOrDefault

    'If any,do the sum.
    If item IsNot Nothing Then
        item.res += newItem.res
    End If
Next

'Overwrite the first file.
Using sw As New IO.StreamWriter(file1)
    For Each item In lst1
        Dim dt1 As String = Convert.ToDateTime(item.date1).ToString("dd.MM.yyyy HH:mm")
        Dim dt2 As String = Convert.ToDateTime(item.date2).ToString("dd.MM.yyyy HH:mm")

        'Maintaining the same order and format of the entries.
        sw.WriteLine($"{item.drid};{item.aid};""{dt1}"";""{dt2}"";{item.res};")
    Next
End Using

输出：

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;

但是，如果我建议的话，本地数据库或可序列化的对象将使您的生活更加美好。

祝你好运。

.csv文件中的值更新取决于第二个.csv中的值

houjunli2010 回答：.csv文件中的值更新取决于第二个.csv中的值

大家都在问