Convert row into Column in Datatable in Studio

[Rohi2023 (Roseline Joy)]

Hi,
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!

[Rohi2023 (Roseline Joy)]

Formatting didn’t quite work well. So below is what the excel currently looks like:

Here is what I want it to look like:

[Yoichi]

Hi,

Can you try the following sample?

Sample20230420-1L.zip (8.8 KB)

Regards,

[Rohi2023 (Roseline Joy)]

Thank you so much for responding! I will try this at work tomorrow and revert.

[Rohi2023 (Roseline Joy)]

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?

[Yoichi]

Hi,

In JSON, it’s necessary to be surrounded by double quote if data type is string. hence number is not unnecessary to surrounded by it.

JSON

Regards,

[Rohi2023 (Roseline Joy)]

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”?
E.G:
{
“firstName”: “John”,
“lastName”: “Smith”,
“isAlive”: true,
}

BECOMES THIS:

"{
““firstName””: ““John””,
““lastName””: ““Smith””,
““isAlive””: true,

}"

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.

Do I need to worry about this and remove it?

[Yoichi]

Can you elaborate?

The above workflow doesn’t add double quote. How did you get the above string?

Regards,

[Rohi2023 (Roseline Joy)]

I’m reading a log file that I pulled from our server (unfortunately I can’t share this due to confidentiality). I then extract the xml and json contents in this log file to a dataTable → Excel file.

[Yoichi]

Hi,

It seems expression of vb literal. Perhaps you should check your method to extract it to string type, if you have problem in the subsequent process.

Regards,