linq to csv_使用LINQ和Regex将CSV文件解析为数据表

linq to csv

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex.

解析CSV文件是我们经常要面对的任务，尽管有很多方法可以做到这一点，但作为一个新手，该领域可能会令人困惑，并且工具可能看起来很复杂。

A simple solution to parsing a customized CSV file is to use this function which returns the datatable. You will have to first setup the datatable, and in this simplified scenario, all the fields of the file are imported into the table. (here is a snapshot of the file).

解析自定义CSV文件的简单解决方案是使用此函数，该函数返回数据表。 您将必须首先设置数据表，并且在此简化方案中，文件的所有字段都将导入到表中。 （这是文件的快照）。

Date,Description,FITID,Amo unt

30/10/2011,First transaction,001,9.99

01/11/2011,"Second transaction, Withdraw",002,-3.26

03/11/2011,Third transaction,003,1.08

And since we intend to load this data into a datatable, here is the setup of the table in code. (You can achieve the same using the designer).

日期，说明，FITID，Amo unt

30/10/2011，首次交易，001,9.99

2011/01/11，“第二笔交易，取款”，002，-3.26

2011/03/11，第三次交易，003,1.08

并且由于我们打算将此数据加载到数据表中，因此下面是代码表的设置。 （您可以使用设计器实现相同的目的）。

Dim gTable As New DataTable("MyTable")
With gTable
    .Columns.Add("Date").DataType = GetType(System.DateTime)
    .PrimaryKey = New DataColumn() {.Columns("DelayID")}
    .Columns.Add("Description").DataType = GetType(System.String)
    .Columns.Add("FITID").DataType = GetType(System.String)
    .Columns.Add("Amount").DataType = GetType(System.Double)
    .Columns("FITID").AllowDBNull = False
    .Columns("Amount").DefaultValue = 0
End With

Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
Dim r As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern)

Dim header As String = "Date,Description,FITID,Amount"

Dim lines As String() = System.IO.File.ReadAllLines(strCustomerFile)
Dim custs = From line In lines Where line <> header AndAlso Not String.IsNullOrEmpty(line) Let data = r.Split(line)
        Select New With {.Date = data(0), .Description = data(1), .FITID = data(2), .Amount = CDbl(data(3).Trim)}

NOTE: We use LINQ to "declare" new columns with names corresponding to the file header (these are the same names we applied to the columns in the datatable above, though you can choose different names).

注意 ：我们使用LINQ来“声明”新列，其名称与文件头相对应（这些名称与我们在上面的数据表中的列所应用的名称相同，尽管您可以选择其他名称）。

Finally, we can iterate through the results of the LINQ query to populate the datatable, first declaring a datarow.

最后，我们可以遍历LINQ查询的结果来填充数据表，首先声明一个数据行。

Dim xRow As DataRow
For Each row In custs
    xRow = gTable.NewRow()
    xRow.ItemArray = {row.Date, row.Description, row.FITID, row.Amount}
    gTable.Rows.Add(xRow)
Next

Finally, here is the complete function (including datatable setup) that you can call to return a populated datatable.

最后，这是您可以调用的完整函数（包括数据表设置）以返回填充的数据表。

Function readLINQ(ByVal strCustomerFile As String) As DataTable
    Dim gTable As New DataTable("MyTable")
    With gTable
        .Columns.Add("Date").DataType = GetType(System.DateTime)
        .PrimaryKey = New DataColumn() {.Columns("DelayID")}
        .Columns.Add("Description").DataType = GetType(System.String)
        .Columns.Add("FITID").DataType = GetType(System.String)
        .Columns.Add("Amount").DataType = GetType(System.Double)
        .Columns("FITID").AllowDBNull = False
        .Columns("Amount").DefaultValue = 0
    End With
    Dim lines As String() = System.IO.File.ReadAllLines(strCustomerFile)
    Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
    Dim header As String = "Date,Description,FITID,Amount"
    Dim r As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern)
    Dim custs = From line In lines Where line <> header AndAlso Not String.IsNullOrEmpty(line) Let data = r.Split(line)
            Select New With {.Date = data(0), .Description = data(1), .FITID = data(2), .Amount = CDbl(data(3).Trim)}
    Dim xRow As DataRow
    For Each row In custs
        xRow = gTable.NewRow()
        xRow.ItemArray = {row.Date, row.Description, row.FITID, row.Amount}
        gTable.Rows.Add(xRow)
    Next
    Return gTable
End Function

翻译自: https://www.experts-exchange.com/articles/7329/Parsing-a-CSV-file-into-a-datatable-using-LINQ-and-Regex.html

linq to csv