FileHelpers nested quotes and commas - parsing error

Question

I'm trying to parse a CSV file from hell, using the fantastic FileHelpers library.

It's failing to handle a row of the form:

"TOYS R"" US"," INC.""",fld2,fld3,"<numberThousands>","<numberThousands>","<numberThousands>",fld7,

FileHelper is very good at handling number fields in 'thousands' format (using a custom formatter), even when wrapped in quotes, trailing commas etc, however it's causing issues with the first field.

"TOYS R"" US"," INC.""",fld2,...

This field includes both nested quotes and nested commas. FileHelper doesn't know how to handle this and is splitting it into two separate fields, which subsequently causes an exception to be thrown.

Are there any recommended ways to handle this?

Answer 1

First, you need to make all of your fields optionally quoted.

[DelimitedRecord(",")] 
public class contactTemplate
{
  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  public string CompanyName;
  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  public string fld2;
  // etc...
}

Then you need replace the escaped delimiters with something else (e.g., a single quote) in a BeforeReadRecord event.

var engine = new FileHelperEngine<MyFileHelpersSpec>();

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