.NET优化CSV文件匹配效率及CSV字段更新方案咨询

我有两个CSV文件：

• 一个存储考勤打卡记录与默认时薪信息
• 另一个存储基于岗位、工作地点等条件的特殊时薪数据（例如默认时薪为16美元/小时，但在B地点从事XYZ岗位时，时薪为19美元/小时）

最初用嵌套循环遍历两个文件做匹配，功能正常，但打卡文件有1-2万行、特殊时薪文件约1100行，导致运行速度极慢且资源占用过高。

后来把特殊时薪文件加载到运行时的DataTable中，逐行对打卡文件进行DataTable.Select查询，现在速度极快。但有个疑问：是否可以在遍历CSV文件时直接更新其中一个字段，还是必须在遍历过程中重写整个文件？

原代码（嵌套循环版本） #

Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(punchdatafile)
    MyReader.TextFieldType = FileIO.FieldType.Delimited
    MyReader.SetDelimiters(",")
    Dim currentRow As String()
    Do While Not MyReader.EndOfData
        Try
            currentRow = MyReader.ReadFields
            Dim d_field0 As String = currentRow(0) 'EmployeeID
            Dim d_field3 As String = currentRow(3) 'Jobcode
            Dim d_field4 As String = currentRow(4) 'Punchdate
            Dim d_field7 As String = currentRow(7) 'PayRate
            Dim d_field11 As String = currentRow(11) 'Location

            Using Rate_Reader As New Microsoft.VisualBasic.FileIO.TextFieldParser(ratedatafile)
                Rate_Reader.TextFieldType = FileIO.FieldType.Delimited
                Rate_Reader.SetDelimiters(",")
                Rate_Reader.HasFieldsEnclosedInQuotes = True

                Dim rate_currentrow As String()
                Do While Not Rate_Reader.EndOfData
                    rate_currentrow = Rate_Reader.ReadFields
                    Dim r_field1 As String = rate_currentrow(1) 'RateID
                    Dim r_field3 As String = rate_currentrow(3) 'Rate
                    Dim r_field4 As String = rate_currentrow(4) 'RateJobCode
                    Dim r_field6 As String = rate_currentrow(6) 'RateDate
                    Dim r_field9 As String = rate_currentrow(9) 'RateLocation

                    If d_field0 = r_field1 Then '匹配到员工ID
                        If d_field3 = r_field4 Then '匹配到岗位代码
                            If d_field11 = r_field9 Then '匹配到工作地点
                                Console.WriteLine(r_field3)
                            End If
                        End If
                    End If
                Loop
            End Using

        Catch ex As Exception

        End Try
    Loop
End Using

优化后代码（DataTable查询版本） #

Dim dt As DataTable = New DataTable
Dim dr As DataRow

dt.Columns.Add("id")
dt.Columns.Add("rate")
dt.Columns.Add("jobcode")
dt.Columns.Add("location")
dt.Columns.Add("date")

Using Rate_Reader As New Microsoft.VisualBasic.FileIO.TextFieldParser(ratedatafile)
    Rate_Reader.TextFieldType = FileIO.FieldType.Delimited
    Rate_Reader.SetDelimiters(",")
    Rate_Reader.HasFieldsEnclosedInQuotes = True

    Dim rate_currentrow As String()
    Do While Not Rate_Reader.EndOfData
        rate_currentrow = Rate_Reader.ReadFields
        Dim r_field1 As String = rate_currentrow(1) 'RateID
        Dim r_field3 As String = rate_currentrow(3) 'Rate
        Dim r_field4 As String = rate_currentrow(4) 'RateJobCode
        Dim r_field6 As String = rate_currentrow(6) 'RateDate
        Dim r_field9 As String = rate_currentrow(9) 'RateLocation

        dr = dt.NewRow

        dr("id") = r_field1
        dr("rate") = r_field3
        dr("jobcode") = r_field4
        dr("location") = r_field9
        dr("date") = r_field6

        dt.Rows.Add(dr)
    Loop
End Using


Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(punchdatafile)
    MyReader.TextFieldType = FileIO.FieldType.Delimited
    MyReader.SetDelimiters(",")
    Dim currentRow As String()
    Do While Not MyReader.EndOfData
        Try
            currentRow = MyReader.ReadFields
            Dim d_field0 As String = currentRow(0) 'EmployeeID
            Dim d_field3 As String = currentRow(3) 'Jobcode
            Dim d_field4 As String = currentRow(4) 'Punchdate
            Dim d_field7 As String = currentRow(7) 'PayRate
            Dim d_field11 As String = currentRow(11) 'Location

            Dim searchdr() As System.Data.DataRow
            searchdr = dt.Select("id='" & d_field0 & "' AND jobcode='" & d_field3 & "' AND location='" & d_field11 & "'")
            If searchdr.Length > 0 Then
                Console.WriteLine(searchdr(0)("rate").ToString)
            End If


        Catch ex As Exception

        End Try
    Loop
End Using

关于CSV字段更新的解答 #

你无法直接在遍历CSV文件时原地更新某个字段——因为CSV是纯文本文件，内容以线性字符流形式存储，修改中间行的内容会导致后续所有字符移位，操作效率极低且容易破坏文件结构。

实际开发中，正确的做法是：

1. 遍历原CSV文件的每一行
2. 对需要更新的字段进行修改（比如找到匹配的特殊时薪后，替换PayRate字段的值）
3. 将修改后的行写入一个临时文件
4. 遍历完成后，用临时文件替换原文件（或按需求重命名保存）

另外针对你的优化代码，还可以再做两个提升：

• 给DataTable的id、jobcode、location字段添加索引，进一步加快Select方法的查询速度
• 避免用字符串拼接构建查询条件，改用DataView或强类型查询逻辑，规避潜在的语法风险

内容的提问来源于stack exchange，提问作者Jeremy