CsvHelper - 设置标题行和数据行 [英] CsvHelper - Set the header row and data row

问题描述

我有如下所示的示例数据:

 1 这是文件中的随机一行23 SOURCE_ID|NAME|START_DATE|END_DATE|VALUE_1|VALUE_245 文件中的另一个随机行678910 吉尔伯|弗雷德|2019-JAN-01|2019-JAN-31|ABC|DEF11 ALEF|ABC|2019-FEB-01|2019-AUG-31|FBC|DGF12 吉尔伯|弗雷德|2019-JAN-01|2019-JAN-31|ABC|TEF13 FLBER|RED|2019-JUN-01|2019-JUL-31|AJC|DEH14 GI|JOE|2020-APR-01|2020-DEC-31|GBC|DER

我无法保存对文件的更改.即，我无法在消费前操作/清理原始文件.任何操作都需要在内存中即时完成.但是如果文件很大(例如，我目前正在测试一些超过 500 万条记录的文件)怎么办.

我正在使用

解决方案

我可以让它与 ShouldSkipRecord 一起工作.唯一的问题是，如果任何随机行有一个|"，它就会失败.分隔符.

using (var reader = new StreamReader(filepath))使用 (var csv = new CsvReader(reader, CultureInfo.InvariantCulture)){csv.Configuration.Delimiter = "|";//设置分隔符csv.Configuration.ShouldSkipRecord = row =>row.Length == 1;使用 (var dr = new CsvDataReader(csv)){var dt = 新数据表();dt.Load(dr);dgvTst04_View.DataSource = dt;//将datagridview源设置为datatable}}

如果您知道有多少列，您可以将其设置为跳过列数少于该列数的任何行.

csv.Configuration.ShouldSkipRecord = row =>row.Length <6;

I have sample data that looks like this:

 1  This is a random line in the file
 2  
 3  SOURCE_ID|NAME|START_DATE|END_DATE|VALUE_1|VALUE_2
 4
 5  Another random line in the file
 6  
 7  
 8  
 9  
10  GILBER|FRED|2019-JAN-01|2019-JAN-31|ABC|DEF
11  ALEF|ABC|2019-FEB-01|2019-AUG-31|FBC|DGF
12  GILBER|FRED|2019-JAN-01|2019-JAN-31|ABC|TEF
13  FLBER|RED|2019-JUN-01|2019-JUL-31|AJC|DEH
14  GI|JOE|2020-APR-01|2020-DEC-31|GBC|DER

I am unable to save changes to the file. Ie, I can't manipulate/clean the original files before consumption. Any manipulation will need to be done on the fly in memory. But what if the files are large (eg, I am currently testing with some files that are 5m+ records).

I am using CsvHelper

I have already referred to the following threads for guidance:

CSVHelper to skip record before header

Better way to skip extraneous lines at the start?

How to read a header from a specific line with CsvHelper?

What I would like to do is:

• Set row where header is = 3 (I will know where the header is)
• Set row where data starts = 10 (I will know where the data starts from)
• Load data into data table, to be displayed into datagridview

If I need perform a combination of stream manipulation before I pass this into the CsvHelper, then do also let me know if that's the missing piece? (and any assistance on how I can actually achieve that under one block of code with be greatly appreciated)

So far I have come up with the below:

string filepath = Path.Combine(txtTst04_File_Location.Text, txtTst04_File_Name.Text);

using (var reader = new StreamReader(filepath))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{

    // skip rows to get the header
    for (int i = 0; i < 4; i++) 
    {
        csv.Read();
    }

    csv.Configuration.Delimiter = "|"; // Set delimiter
    csv.Configuration.IgnoreBlankLines = false;
    csv.Configuration.HasHeaderRecord = true;
    
    // how do I set the row where the actual data starts? 

    using (var dr = new CsvDataReader(csv))
    {
        var dt = new DataTable();
        dt.Load(dr);
        dgvTst04_View.DataSource = dt; // Set datagridview source to datatable
    }

}

I get the below result:

Do let me know if you would like me to expand on any point.

thanks!

EDIT:

New linked post created here trying to resolve the same objective, but in a different way but getting a new error: Filestream and datagridview memory issue with CsvHelper

解决方案

I can get it to work with ShouldSkipRecord. The only problem is it will fail if any of the random lines has a "|" delimiter in it.

using (var reader = new StreamReader(filepath))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
    csv.Configuration.Delimiter = "|"; // Set delimiter
    csv.Configuration.ShouldSkipRecord = row => row.Length == 1;
    
    using (var dr = new CsvDataReader(csv))
    {
        var dt = new DataTable();
        dt.Load(dr);
        dgvTst04_View.DataSource = dt; // Set datagridview source to datatable
    }

}

If you know how many columns there are, you could set it to skip any rows that have less than that many columns.

csv.Configuration.ShouldSkipRecord = row => row.Length < 6;

这篇关于CsvHelper - 设置标题行和数据行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！