I’m new here, great community! I have a string file containing XML tags, Json tags, and other string formats. I need to write the req and resp data to a table (then to excel). I have delimeters in the file (“#####”). I use the delimeters as both column and row separators. This works well enough - it extracts the data into the table as rows. However, the thing is I was to convert the table in this manner:
let’s say we have 2 rows like this:
row 1: REQUEST DATA row 2:< message …>
This goes on and on where odd rows will have “REQUEST DATA” or “RESPONSE DATA” and subsequent (even rows) will have the actual xml or json payload.
What I want to do is:
I want to put subsequent rows after “REQUEST DATA” or “RESPONSE DATA” as another column next to it. so that I will have: Column 1 Column2 REQUEST DATA < message …>
How can I go about doing this please?
Thanks in advance!
Hi Yoichi, thanks so much - it worked perfectly! One more thing, what can I do about the double quotes that is appearing in the cells? This is appearing for strings (payload) but not for numeric values. Is it because it is auto-detecting data types?
Yes I get that the actual Json payload will have the escape quotes, but why is the whole string in the cell surrounded by quotes? In the string file, lines like RESPONSE DATA are not in quotes. After writing to excel, if I click on the cell, the string value is not in quotes, but if I copy and paste to a notepad or somewhere else, it comes like this “RESPONSE DATA”?
I understand the extra quotes on the strings in the payload, but why is there quotes surrounding the whole string? Again, this doesn’t show in the excel, and when I read the row in studio and output the row values, there are no extra quotes.