need help in xml parsing a field with carriage returns

[dexter90]

Hi,

I have a xml file and one of the field is in below format with carriage returns

<msg_data>{1:F21IBNJUS33AXXX3294012033}{4:{177:1808010833}{451:0}}{1:F01IBNJUS33AXXX3294012033}{2:I996BCOMPTPLXXXXN}{4:&#13;
:20:ANSWER3288012013&#13;
:21:ANSWER0768228235&#13;
:76:URGENT&#13;
.&#13;
:11S:950&#13;
180726&#13;
0768228235&#13;
:20:ANSWER0768228235&#13;
:21:ST/0020/20180725&#13;
:76:PLEASE PROVIDE MORE DETAILS FOR&#13;
CREDIT EUR 295.00&#13;
REF.5878118723777906&#13;
:11R:950&#13;
180726&#13;
0768228235&#13;
:20:ST/0020/20180725&#13;
:25:PT50003300004540157517305&#13;
:28C:145/1&#13;
:60F:D180724EUR314631,19&#13;
:61:1807250725D0,2NCOM20180724//S076684928800001&#13;
IBNJUS33XXX&#13;
:61:1807250725D0,7NCOM5878118723777906//S076785500000001&#13;
45401575173&#13;
:61:1807250725D5,NCOM20180724//S076684928800001&#13;
IBNJUS33XXX&#13;
:61:1807250725D17,5NCOM5878118723777906//S076785500000001&#13;
45401575173&#13;
:61:1807250725C295,NTRF5878118723777906//S076785500000001&#13;
45401575173&#13;
:61:1807250725C2000,NTRF03MT180724190473//S076785191200001&#13;
2076108073&#13;
:61:1807250725D60000,NTRF20180724//S076684928800001&#13;
IBNJUS33XXX&#13;
:61:1807250725C88516,NTRFA075318724G100TY//S076785509600001&#13;
1010065602&#13;
:62M:D180725EUR283843,59&#13;
-}{5:{CHK:630C7C6F5AC7}}</msg_data>

---------------------------------------------------------------------------------------------------------------------------

When I am using the xml parser the whole lines are not being parsed in one cell only partial data is getting in. How can I solve this?

{1:F21IBNJUS33AXXX3294012033}{4:{177:1808010833}{451:0}}{1:F01IBNJUS33AXXX3294012033}{2:I996BCOMPTPLXXXXN}{4:
:20:ANSWER3288012013
:21:ANSWER0768228235
:76:URGENT
.
STILL WAITING FOR YOUR REPLY
.
REGARDS
LUIS CHIPELO-WPC
:11S:950
180726
0768228235
:20:A...

[AndrewSu]

@dexter90 , could you provide an example of what you want the output to look like? 

[dexter90]

I am trying to convert xml to csv file.

I have attached a sample input file. There is a field msg_data which is having newlines and I am not able to parse the field in a single cell. Any suggestions is appreciated.

[DavidSkaife]

Hi @dexter90 

Are you meaning getting it to look like this?

I simply ran it through a Data Cleansing tool and removed Tabs, Line Breaks, and Duplicate Whitespace for that field.

If that's not it you will need to explain/show what your expected output is :)

[dexter90]

I tried that but it is not giving the complete output for the field, I see it is only giving partial output after using Data cleansing tool.

{1:F21IBNJUS33AXXX3294012033}{4:{177:1808010833}{451:0}}{1:F01IBNJUS33AXXX3294012033}{2:I996BCOMPTPLXXXXN}{4: :20:ANSWER3288012013 :21:ANSWER0768228235 :76:URGENT . STILL WAITING FOR YOUR REPLY . REGARDS LUIS CHIPELO-WPC :11S:950 180726 0768228235 :20:A...

[DavidSkaife]

Hi @dexter90 

Ahhh ok, i think i see what you're meaning. The data is there Alteryx just truncates it for performance purposes if it's greater than 256 characters.

If you output it to a CSV file it should contain all the data you're expecting. If it is still truncating then you can try adjusting the size of the field using a Select Tool.

[Ben_H]

Is that from the results window or a browse tool?

The results window will truncate values, but the actual data is there if you stick a browse on the end of the workflow.

Regards,

Ben