c# FileHelpers load into DataTable a csv with variable length lines

Question

Following on this post that comes close to answer my question, I need some help in settings up FileHelpers. I have bank statements with some extra info above the actual transaction data, thus the file looks like below:

Some Header 1,Some Header 2,And Header 3
<summary of the entire file on 5 lines>

Date,Transaction Type,Description,Amount,Running Balance
<actual transaction data, on 5 columns each line>

I am interested in capturing all the fields (in a DataTable), including the summary. Basically, I would like the datatable to be sized according to the max number of columns in any line.

Prasanth has suggested an alternative, but I don't understand what _fileContent is:

using (MemoryStream stream = new MemoryStream(_fileContent)) //file content can be file as byte array

I have written code in VBA for years and have recently started an Excel Com-AddIn in c#, so I am more of a novice I guess.

Thank you in advance! Dani

score 0 · Answer 1 · answered May 05 '18 at 13:18

With Cinchoo ETL - an open source library, you can load the variable length CSV file. Sample below shows how to

string csv = @"Id, Name, City
    1, Tom, NY
    2, Mark, NJ, 100
    3, Lou, FL
    4, Smith, PA
    5, Raj, DC";

StringBuilder sb = new StringBuilder();
using (var p = ChoCSVReader.LoadText(csv)
    .WithFirstLineHeader(true) //Ignore the header line to handle the variable length CSV lines
    .Configure(c => c.MaxScanRows = 5) //Set the max scan rows to the highest to figure out the max fields
    .Configure(c => c.ThrowAndStopOnMissingField = false)
    )
{
    foreach (var rec in p)
        Console.WriteLine(rec.DumpAsJson());    
}

Checkout CodeProject article for some additional help.

Disclaimer: I'm the author of this library.

score 0 · Answer 2 · answered May 18 '18 at 16:58

FileHelpers MultiRecordEngine might help with this, provided you're able to write a record selector that can look at a string record and decide with format you want to use to read the line.

Typically, this works best when you've got an obvious indicator of record type -- in this case, the first character of the line indicates a record type:

 if (recordLine.Length == 0)
            return null;  // no record will be read

        int action = int.Parse(recordLine.Substring(0, 1));
        switch (action) {
            case 0:
            case 1:
                return typeof(RecTypeOne);
            case 2:
                return typeof(RecTypeTwo);
            case 3:
                return typeof(RecTypeThree);

            default:
                return null;  // again, no record is read

In your case, you might be able to make this decision based on the number of commas in the line, which implies number of fields, though an actual deterministic record type indicator would be preferable, IMO.

c# FileHelpers load into DataTable a csv with variable length lines

2 Answers2