Scenario

The input file of an interface is a flat file in the CSV format and some fields have commas as content. These fields are wrapped in double quotes.

Solution

The solution consist of the of the steps described in the following paragraphs.

Unflattening the file

While it is not the scope of this article to explain how to unflatten a file, I will mention that a Flat File Schema has to be created, and I do that using the Flat File Schema Creation Wizard. Also, a custom pipeline has to be created and the Flat File Disassembler pipeline component has to be added to it. For the purpose of this article I have a created a Send pipeline and dropped the Flat File Disassembler in the Assemble stage of the pipeline. This can also be done solely using a Transform shape in an orchestration.

Configuring the Flat File schema to interpret double quotes as wrapping characters

The secret to this is to configure the Flat File Schema to be aware that when inside double quotes, the comma character is not a file delimiter. 

Implementation

The Wrap Character can be defined for each field or as a default for the entire schema.

If configuring at the field level:
  • Select the field on the schema editor in Visual Studio and look for the Wrap Character property in the Properties window. 
  • Change the Wrap Character Type from None to Character.
  • Enter the desired character in the Wrap Character field. On this particular case, enter double quotes (").
If configuring as default for the entire schema:
  • Select the Schema node in the schema editor in Visual Studio and look for the Default Wrap Character property in the Properties window. 
  • Change the Default Wrap Character Type from None to Character.
  • Enter the desired character in the Default Wrap Character field. On this particular case, enter double quotes (").
  • Select all the elements in the schema that should be using the Default Wrap Character and look for the Wrap Character Type in the Properties window.
  • Select Default Wrap Character from the dropdown list. That way all the elements will use the Default character entered at the Schema node.
This configuration can be tested easily in Visual Studio by assigning an Input Instance to the schema and selecting Validate Instance from the Schema context menu in Solution Explorer. 

Conclusion

Now the Flat File Schema indicates to its consumer, be it the Flat File Disassembler, the Flat File Assembler or Visual Studio Instance Validation, that characters between quotes are not to be considered field delimiters.

See Also

Another important place to find an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.