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.
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:
<nickname>The "Inescapable" Value</nickname>
<notes>Notes Line 1
Notes Line 2</notes>
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.
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
- 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.