How to configure a Flat File Profile for CSV files including line breaks and double-double quotes

Document created by Terese_Baker Employee on Jan 12, 2015Last modified by Adam Arrowsmith on May 3, 2016
Version 3Show Document
  • View in full screen mode

This article describes how to configure a flat file profile to accommodate a CSV file that may contain line breaks and double-double quotes within a text qualified column value.

 

 

Use Case

You need to parse and map a CSV (comma separated value) file with text qualifiers that contains line breaks and/or double quotes escaped by double-double quotes [""] within a column value.

 

For example, a source file:

"Name","Nickname","Notes"
"Test User","The ""Inescapable"" Value","Notes Line 1
Notes Line 2"

 

...should be parsed and mapped as:

<person>

     <name>Test User</name>

     <nickname>The "Inescapable" Value</nickname>

     <notes>Notes Line 1

Notes Line 2</notes>

</person>

 

Approach

Create and configure a Flat File Profile component with Text Qualifiers and Remove Escape options.

 

As of the May 2016 release, all new flat file profiles created will automatically use the new upgraded parser however existing profiles will need to be explicitly upgraded (simply open the profile and click upgrade). There are slight behavioral changes so you will want to carefully regression test after upgrading. If for some reason you need to "undo" the upgrade, simply rollback the flat file component's revision history.

 

Implementation

Configure the Options tab within the flat file profile component as follows:

  • Use Column Headers = true (if file has headers)

  • File Type = Delimited

  • File Delimiter = Comma Delimited

  • Text Qualifier = Double Quote

  • Text Qualifier Escape = "

  • Remove Escape = true

 

 

Considerations

  • There is a current limitation regarding the handling of double-double quotes in which the same flat file profile configuration cannot be used for both reading and writing. If your scenario requires this you will need to create a separate copy of the profile: one with Remove Escape=true for reading and one with Remove Escape=false for writing.
  • For more information see Working with Delimited Flat File Data.
6 people found this helpful

Attachments

    Outcomes