topic RE: Nested Collection(Json Collection) write in Excel in Product Forum

Nested Collection(Json Collection) write in Excel

arghya.b — Fri, 12 Feb 2021 10:46:00 GMT

Hi,

Want to know if any option is there to write a nested collection directly to excel.
Like below Collection.

--------------------------
ID | Collection
--------------------------
1 | Row 1 of 5
2 | Row 1 of 2
3 | Row 1 of 12
-------------------------

------------------------------
Arghya Bhattacharyya
RPA Developer
Asia/Kolkata
------------------------------

RE: Nested Collection(Json Collection) write in Excel

Ben.Lyons1 — Fri, 12 Feb 2021 14:40:00 GMT

Hi Arghya,

I'm afraid there's no way Excel can allow a table to be written into a single cell.

Your best bet would be the extract the data from each table into a text/string format. You could separate columns with / and rows can be put onto new lines e.g.

Column1 / Column2 / Column3
Row2 / Row2 / Row2
Row3 / Row3 / Row3

I think my best advice would be to think how you would try to solve as a human, then try to replicate that structure in your process/action.

Kind Regards

------------------------------
Ben Lyons
Product Consultant
Blue Prism
UK
------------------------------

RE: Nested Collection(Json Collection) write in Excel

AndreyKudinov — Tue, 16 Feb 2021 22:17:00 GMT

You can serialize it, but I doubt it is something you actually want.
You can't simply write data with 3 dimensions into a 2d table.
However, you can iterate over collection rows, create a worksheet for each row and write each nested collection in it's own worksheet.
You can also make another top-level table with links to individual worksheets/ranges that you create.

edit: or you can put all on one sheet, just calc sheet end after each iteration and paste new row's collection after it... depends what you need.
------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: Nested Collection(Json Collection) write in Excel

arghya.b — Wed, 24 Mar 2021 13:20:00 GMT

Thank you Ben.
Actually i was looking for any pre build VBO by which we can write nested collection in excel by traversing row by row.
Thanks i have written a code stage to do that.

------------------------------
Arghya Bhattacharyya
RPA Developer
Asia/Kolkata
------------------------------