How to COMBINE a Group of Binary Data Content (Excel Files ) as a Single Whole?

How to COMBINE a Group of Binary Data Content (Excel Files ) as a Single Whole?

  

I would like to group a bunch of spreadsheets and display them as a single whole. You can see on this uploaded Entity that I have uploaded several sheets using Multi-Upload:

 

When I do it like this, I'm only able to fetch and display on screen the topmost row (Filesize 12709). The rest are ignored. I understand that I may need to LOOP or ListAppend them as a single whole, but my efforts have failed so far.

What I want to do is to combine them as a single whole so all data of the excel files are displayed as one (they all have same column format).

The Question:

What would be a good approach to achieve this? I'm thinking of a double ForLoop-esque approach since I'm already able to display one of the rows. I just need to tell Outsystems to include the other rows (using nested loops? )

Current Diagram:

Good day, Helios.

First of all create the Structure that holds all the column of your excel. Then create local variable with the type List:YourStructure(ListOfRows), and other variable for one row(tempRow). After that fetch all the excels, and make foreach loop for excel. Use "Excel To Record List", and make another loop for each row. And then add each row columns to the tempRow.columns. Then add tempRow to ListOfRows.

That's it. At the end you have to have list of all the rows from each excel. You can use "Record List To Excel" and export it as one excel.

Hi Helios,

Abay's solution is basically correct, but there's no need to loop over the result set of the Excel to Record List: you can directly perform a ListAppendAll to append all records, no need to loop and add them one by one.

That said, why do you want to store the uploaded Excels instead of the data? The typical use case of uploading is directly parsing the uploaded files and storing the content in the database, not storing the binaries and retrieve those when they're needed.

Oops... Yes. appendAll is much better choice. Fogot about its existence. ??

Kilian Hekhuis wrote:

Hi Helios,

Abay's solution is basically correct, but there's no need to loop over the result set of the Excel to Record List: you can directly perform a ListAppendAll to append all records, no need to loop and add them one by one.

That said, why do you want to store the uploaded Excels instead of the data? The typical use case of uploading is directly parsing the uploaded files and storing the content in the database, not storing the binaries and retrieve those when they're needed.



Abay Koldeybekov wrote:

Oops... Yes. appendAll is much better choice. Fogot about its existence. ??

Kilian Hekhuis wrote:

Hi Helios,

Abay's solution is basically correct, but there's no need to loop over the result set of the Excel to Record List: you can directly perform a ListAppendAll to append all records, no need to loop and add them one by one.

That said, why do you want to store the uploaded Excels instead of the data? The typical use case of uploading is directly parsing the uploaded files and storing the content in the database, not storing the binaries and retrieve those when they're needed.



Looking at my Current Diagram, so this is overkill? I should just use ListAppendAll?
I'm just learning the ropes yet, so I don't basically understand everything. I just learn from examples and from the docs.


Yeah. Instead of all these you can put there listAppendAll() and get rid of the second loop.

Helios wrote:

Abay Koldeybekov wrote:

Oops... Yes. appendAll is much better choice. Fogot about its existence. ??

Kilian Hekhuis wrote:

Hi Helios,

Abay's solution is basically correct, but there's no need to loop over the result set of the Excel to Record List: you can directly perform a ListAppendAll to append all records, no need to loop and add them one by one.

That said, why do you want to store the uploaded Excels instead of the data? The typical use case of uploading is directly parsing the uploaded files and storing the content in the database, not storing the binaries and retrieve those when they're needed.



Looking at my Current Diagram, so this is overkill? I should just use ListAppendAll?
I'm just learning the ropes yet, so I don't basically understand everything. I just learn from examples and from the docs.




Hi Helios,

As I understand it, the Excel files are uploaded and temporarily stored in the database, and you need to process them, and store all the records one by one in the database? In that case, you indeed need the loop since you need to store the records one by one. My advise of the ListAppendAll was about creating a single List from the various files, but if you need to process and store, then looping is needed.

Abay Koldeybekov wrote:

Yeah. Instead of all these you can put there listAppendAll() and get rid of the second loop.

Helios wrote:

Abay Koldeybekov wrote:

Oops... Yes. appendAll is much better choice. Fogot about its existence. ??

Kilian Hekhuis wrote:

Hi Helios,

Abay's solution is basically correct, but there's no need to loop over the result set of the Excel to Record List: you can directly perform a ListAppendAll to append all records, no need to loop and add them one by one.

That said, why do you want to store the uploaded Excels instead of the data? The typical use case of uploading is directly parsing the uploaded files and storing the content in the database, not storing the binaries and retrieve those when they're needed.



Looking at my Current Diagram, so this is overkill? I should just use ListAppendAll?
I'm just learning the ropes yet, so I don't basically understand everything. I just learn from examples and from the docs.




Okay thanks, but if you notice my GetUploadCaches.List Loop, it's Incomplete, because it's missing the Complete Cycle Path, don't know how to CLOSE it.
ERROR: "Cycle Path must return to the For Each element in Screen Action.."

Anyway, I'll try what you suggested first. My GetUploadCaches.List is so wrong right now tho.

Kilian Hekhuis wrote:

Hi Helios,

As I understand it, the Excel files are uploaded and temporarily stored in the database, and you need to process them, and store all the records one by one in the database? In that case, you indeed need the loop since you need to store the records one by one. My advise of the ListAppendAll was about creating a single List from the various files, but if you need to process and store, then looping is needed.

Yep, what happens is that there's a Multiple Upload widget where you upload several/many Excel files. I'm going to display these MULTI-Uploaded spreadsheets as one sheet (you can because they have the same column format and structure). I can already display the very first sheet but the rest are ignored, hence I tried adding a GetUploadCaches.List FOR LOOP but I don't know how to CLOSE it's complete Cycle Path so it's highlighted as red (ERROR: "Cycle Path must return to the For Each element in Screen Action..""). I'll be trying the ListAppendAll() now.


You can not end the cycle like that. For error handling there is a 'Raise an exception' thing. And after you throw an exception, you will need to catch it. Like in an image. The flow should always be consistent. 


Helios wrote:

Kilian Hekhuis wrote:

Hi Helios,

As I understand it, the Excel files are uploaded and temporarily stored in the database, and you need to process them, and store all the records one by one in the database? In that case, you indeed need the loop since you need to store the records one by one. My advise of the ListAppendAll was about creating a single List from the various files, but if you need to process and store, then looping is needed.

Yep, what happens is that there's a Multiple Upload widget where you upload several/many Excel files. I'm going to display these MULTI-Uploaded spreadsheets as one sheet (you can because they have the same column format and structure). I can already display the very first sheet but the rest are ignored, hence I tried adding a GetUploadCaches.List FOR LOOP but I don't know where to connect it's complete Cycle Path so it's highlighted as red. I'll be trying the ListAppendAll() now.

Solution

Helios,

Your for loop is pointing to the wrong node, switch the following pointers:


Solution

Justin Babel wrote:

Helios,

Your for loop is pointing to the wrong node, switch the following pointers:


Oh MY!!!! This was the missing piece. Right-click, 'SWAP CONNECTORS' solved this entire thing! 

THANK YOU SO MUCH!


Thank you so much for your HELP:

Justin Babel

Kilian Hekhuis

Abay Koldeybekov

You're welcome, for what's it worth. Next time I'll look closer to images and detect myself the connectors must be swapped :).