csv data with comma values throws error while processing the file through the BizTalk flatfile Disassembler

Question

I'm going to a pick a csv file in BizTalk and after some process I wanted to update it with two or more different systems.

In order to getting the csv file, I'm using the default Flatfile Disassembler for breaking it and constructing it as XML with the help of genereted schema. I can do that successfully with some consistent data however if I use a data with comma in it (other than delimiters), BizTalk fails!

Any other way to do this without using a custom pipeline component? Expecting a simple configuration within the flatfile disassembler component!

Answer 1

So, here's the deal. BizTalk is not failing. Well, it is, but that is the expected and correct behavior.

What you have in an invalid CSV file. The CSV specification disallows the comma in field data unless a wrap character is used. Either way, both are reserved characters.

To accept the comma in field data, you must choose a wrap character and set that in the Wrap Character property in the Flat File Schema.

This is valid:

1/1/01,"Smith, John", $5000

This is not:

1/1/01,Smith, John, $5000