Comma in column header name converting xml to excel

[amritmishra]

Hi All,

I am currently working on an integration where we convert the incoming xml payload after transformations to an xls file as output. I am leveraging FormatConversionBean to achieve this. To configure the header names, I have configured the fieldNames property to pick header names = ‘fromConfiguration". However, I have a column which has a ,(comma) in the header name and the conversion solution splits the single column with ,(comma) into multiple columns.

For example - this is the header we have : If married,single income?- Y/N splits into two columns If Married as column 1 and single income?-Y/N as column2.

Please share your experience, if you have dealt with this issue in the past and the approach you took to resolve this issue.

Attaching a screenshot of the FormatConversionBean config

Approach 1 - I tried using double quotes for the column name, but it still splits the column into two.

Approach 2- Tried using different escape character options but it did not work.

Approach 3- I set addHeaderLine to "None" and tried appending the headers after the conversion, however, this lead to the xls file getting corrupted. I also tried to use different symbols like : | ; and / instead of comma and then using a groovy script to replace them but it corrupts the xls file.

Attaching screenshot of what the column output looks like:

[VijayKonam]

You may have write an adapter module that removes that , from that header before the format bean is applied. Or just another interface that reads the input file, replaces that , and place the file in a folder for the main interface to pick it up and process.

[Ryan-Crosby]

Hi Amrit,

If you enclose your headers in the configuration inside double quotes then Excel should ignore said comma.

Regards,

Ryan Crosby

[amritmishra]

Hey Ryan,

Thanks for taking the time to respond to my question.

I tried double quotes for all the columns like : Column1, Column 2, "If married,single income?- Y/N"

but it did not resolve the issue, still splits the columns.

I also tried double quotes for all the columns like : "Column1, Column2, If married,single income?-Y/N"

but got the same result.

I have updated my question with other approaches I tried.

Regards,

Amrit

[Ryan-Crosby]

amrit.mishra I checked the bean source code on Github and it does a simple split on comma, so you cannot use commas in the fieldNames designation. You would need to alter the Groovy code for the bean to support that use case.

[amritmishra]

Appreciate your input and time. Thanks

[Ryan-Crosby]

amrit.mishra one thing you could try as a complete data hack is to use a special comma like character in that field name -٫

Unicode character U+0066B which effectively presents like a regular comma. Not sure if it would trick the execution of the split but you could try it.