FileHelpers numbers and commas in fields

I have a csv file that I am processing with FileHelpers and I have a situation where both a quote and a comma can appear in a field:

Comma:

323,"PC","28/02/2014","UNI001","5000",0,"Return","Returned Goods, damaged",88.00,15.40,"T1","N",0.00,"R","-",

      

Quote

 148,"SI","13/01/2014","CGS001","4000",1,"5","17" Monitor",266.00,45.39,"T1","Y",311.39,"R","-", 

      

My class:

[DelimitedRecord(",")]
public class Transaction
{
    public int TRAN_NUMBER;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string TypeText;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string DATE;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string TransactionAccount;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string NOMINAL_CODE;

    public int DEPT_NUMBER;

    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string INV_REF;

    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string DETAILS;

    public string NET_AMOUNT;
    public string TAX_AMOUNT;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string TaxCodeName;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string PAID_FLAG;

    public string AMOUNT_PAID;

    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string VatReconText;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string BankReconText;

    public string RECON_DATE;
}

      

I found this thread FileHelpers nested quotes and comma - parse error

engine.BeforeReadRecord += (sender, args) => 
args.RecordLine = args.RecordLine.Replace(@"""", "'");

      

But it only helps with quotes appearing, not commas.

Is it possible to solve both of these problems with FileHelpers or should I look for an alternative solution?

+3


source to share


1 answer


You can implement an event BeforeReadRecord

to "fix" your bad strings.

FileHelperEngine engine = new FileHelperEngine<Transaction>(); 
engine.BeforeReadRecord += BeforeEvent; 

private void BeforeEvent(EngineBase engine, BeforeReadRecordEventArgs e)
{
    var line = e.RecordLine;

    // you have to write the following replacement routine...
    var fixedLine = ReplaceEmbeddedCommasAndQuotesWithSomethingDifferent(line); 

    e.RecordLine = fixedLine; // replace the line with the fixed version
}

      



And after you read the entries, you can process them to reverse the replacement process to correct them.

If you prefer to define all the logic in the FileHelpers class itself, you can implement INotifyRead<Transaction>

instead of using an event.

0


source







All Articles