1

Consider my input data as below:

<xmlnode>line1    
line2    
line3    
</xmlnode>

Right now, I have a map which maps input data to a flatfile schema. I am saving the flatfile as CSV.

Issue is :if input data is having newlines, then the csv format is getting corrupted. The content of 'xmlnode' should go to one single csv column.

Is there is any setting I need to handle this at flat file schema?

beast
  • 107
  • 12

2 Answers2

1

Create a functoid with code like the following:

return input.Replace("\r", "").Replace("\n", " ");

The idea is to replace any \r\n with a single space (and handle cases where there's a newline with no carriage return). Should fix your problem.

If this is a problem that will occur routinely on multiple/all nodes from your input, then you might consider running that as a regular expression on the entire message as a string after mapping (rather than having every node pass through your scripting functoid).

As Dan suggessted in Comments, double quotes is also required to save data with \n (new line) in one cell of a csv.

beast
  • 107
  • 12
Dan Field
  • 20,885
  • 5
  • 55
  • 71
  • Hi Dan, I want to store the new lines also in single cell in csv. I dont want to wrap the data by replacing to whitespace. – beast Jul 06 '15 at 16:56
  • Yes, the code I provided would do that. You'd have to run it in a scripting functoid taking input from the offending node and outputting to the correct node. It would replace any new line characters with a space. – Dan Field Jul 06 '15 at 17:32
  • In that case, enclose the data in quotes. see here: http://stackoverflow.com/questions/6516222/how-to-write-data-on-multiple-lines-but-within-the-same-cell-of-csv – Dan Field Jul 06 '15 at 22:04
  • HI Dan, I tired that code. Just as I thought, its wrapping into single line in csv when both tired replacing both. With replacing only carriage return, new record is forming. (edited) – beast Jul 06 '15 at 22:06
  • Because that way you can specify whatever's between the quotes is part of the cell. You should do that for all your cells if you do it for any. – Dan Field Jul 06 '15 at 22:08
  • Hi Dan, It worked. With Double quotes and replacing carriage return. Thank u so much. – beast Jul 06 '15 at 22:32
1

You need to set the "Wrap Character" and "Wrap Character Type" settings in your flat file schema for that field to quote (") and 'Character' respectively. I've used this for the same issue.

Flat File Wrap Character

Note: There is a "Default Wrap Character" and "Default Wrap Character Type" in the schema settings but BizTalk cleverly defaults the type on fields to "None" rather than "Default" so you still have to go and change the fields even if you set the default.

Bert
  • 372
  • 2
  • 11