FileHelper escape delimiter

Question

I am using FileHelper 2.0 for parsing my csv data. Is there any option that filehelper can properly handle escaped delimiter? That it can identify field as data and not as delimiter.

Our csv format: escape comma (,) with \,

Example data:

name, lastname

nico\,le,opeka

Current code:

[DelimitedRecord(",")] 
public class contactTemplate
{
  public string firstName;
  public string lastName;
}

How can I get firstName = nico,le and lastName = opeka. FileHelpers splits by comma , and now it returns:

firstName -> nico\

lastName -> ,le,opeka

Cato

Answer 1

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

[DelimitedRecord(",")] 
public class contactTemplate
{
  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  public string firstName;
  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  public string lastName;
}

Then you need to quote all the fields which contain an escaped character. You can use a BeforeReadRecord event for this.

FileHelperEngine engine = new FileHelperEngine(typeof(contactTemplate)); 
engine.BeforeReadRecord += BeforeEvent; 

private void BeforeEvent(EngineBase engine, BeforeReadRecordEventArgs e)
{
    if (e.RecordLine.Contains("\"))
    {
        string[] parts = SplitStringRespectingEscapeCharacter(eRecordLine);
        parts = QuoteAnyPartsWhichContainEscapeCharacter(parts);
        parts = RemoveAnyEscapeCharacters(parts);
        e.RecordLine = parts.Join;
    } 
}

You can find some code to get you started on your customized split function here.

Trinidadtrinitarian

I suppose there is a typo for function SplitStringResepctingEscapeCharacter(eRecordLine) -> SplitStringRespectingEscapeCharacter(eRecordLine). If input string is like this: ni"co\,le,opeka after function QuoteAnyPartsWhichContainEscapeCharacter parts would look like this: part0 -> "ni"co\,le" part1 -> opeka and again we have problem with escaped character ". – Cato

Within the quote function, you can do whatever you like with the existing strings. If you need to replace existing " characters with something else that's possible too, just add another step. Or alternatively, use an obscure quote character in the FieldQuoted attribute. – Trinidadtrinitarian

Or in the BeforeEvent method fill in contactTemplate. r[index].firstName = part1, r[index].lastName = part2. I also found a way to surround part with ' ' and if part contains ' we escape it with \' and for delimitedrecord we use string ',' instead of character ' and the problem is solved. In any case text has to be processed / prepared before FileHelper can use it. As it seems FileHelper does not have escape delimiter support. Thank you for BeforeReadRecord. – Cato

Great. Nice find with ',' as delimiter. – Trinidadtrinitarian