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?

Tuna

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(@"""", "'");

Belsen

I thought about this but unfortunately RecordLine isn't publicly settable. I am currently parsing the row manually (within BeforeReadRecord) and setting SkipThisRecord to true. Hacky, but it works. – Tuna

Sure RecordLine is settable. See the note at the bottom of the page here. Which version are you using? You sure you are not trying to set Record instead? – Belsen

There's no { set; } accessor on the property: filehelpers.sourceforge.net/… – Tuna

The property is settable in FileHelpers 2.9.9. The 2.0.0.0 version is from 2007. You could: upgrade; modify the source; or go with your existing hack. – Belsen