4

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?

trilson86
  • 939
  • 1
  • 9
  • 20

1 Answers1

13

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(@"""", "'");
shamp00
  • 11,106
  • 4
  • 38
  • 81
  • 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. – trilson86 Jan 16 '14 at 08:51
  • Sure `RecordLine` is settable. See the note [at the bottom of the page here](http://filehelpers.sourceforge.net/events.html). Which version are you using? You sure you are not trying to set `Record` instead? – shamp00 Jan 18 '14 at 16:22
  • There's no { set; } accessor on the property: http://filehelpers.sourceforge.net/FileHelpers.ReadRecordEventArgs.RecordLine.html – trilson86 Jan 19 '14 at 17:42
  • The property is settable in [FileHelpers 2.9.9](http://staging.nuget.org/packages/FileHelpers-Stable/2.9.9). The 2.0.0.0 version is from 2007. You could: upgrade; modify the source; or go with your existing hack. – shamp00 Jan 19 '14 at 22:27