Multiple Worksheets into one Collection

EnochFoul

New Member
Hi

I am attempting to add multiple Excel sheets to one collection in order to combine the data contained in each sheet. Is it possible to loop through multiple excel sheets and add the chunks of data into a collection without overwriting each time? I am looping through each sheet in turn and trying to add the data to the same collection but each time i attempt it the data gets overwritten.


Thanks
 

VJR

Well-Known Member
Hi

I am attempting to add multiple Excel sheets to one collection in order to combine the data contained in each sheet. Is it possible to loop through multiple excel sheets and add the chunks of data into a collection without overwriting each time? I am looping through each sheet in turn and trying to add the data to the same collection but each time i attempt it the data gets overwritten.


Thanks
Hi EnochFoul,

Yes this is very much possible. Get the collection into an intermediate collection (lets call it TempCollection). Once the data is in the TempCollection, use the 'Append Rows to Collection' action to append the TempCollection into another new collection called FinalCollection. At the end, the FinalCollection is the one that has the rows of all the sheets.
 
Yah ok thanks.. Is there a possible way to know the number of sheets with data inan excel?(no of sheets may vary)..
And suppose if i have 5sheets then how do i use this Activate worksheet to switch between those 5sheets one by one?
 
Last edited:
Ques on #2 in thus thread.. At first run, Temp collection has the excel data. Final collection is empty.. And i try to append them.. Im not able to append them because the final collection is empty.. The number of columns are not defined. How to splve this?
 

Attachments

  • IMG20181225175654.jpg
    496.7 KB · Views: 90

sivagelli

Well-Known Member
Try this-
1. Create a Data Item called 'counter' with initial value of 1
2. In your loop stage use a decision stage with condition- counter = 1
3. In the Yes path
  • use the actions 'Get Worksheet as collection' and write to your final collection
  • Increment the 'counter' using calc stage
  • Connect the Calc stage to loop end
4. In the No path
  • use the actions 'Get Worksheet as collection' and write to your temp collection
  • Append Temp collection to final collection
  • Connection 'Append' action to loop end
The 'Yes' Path will be executed once and will define your final collection. And, the No path will iterate from the second worksheet and will get the data to temp collection and Appends the data to final collection.

This should solve the problem statement, IFF all the excel sheets have same number of columns and same column names.
 
Last edited:
*What is the use of yes path? Counter ll never increase in NO path ..
*To append Tempcollection to Finalcollection, do i need to predefine the column names in Finalcollection? Im not able to append if i dont predefine the column names.. What if i dont know the column names? How can i append them?
 

sivagelli

Well-Known Member
*What is the use of yes path? Counter ll never increase in NO path ..
*To append Tempcollection to Finalcollection, do i need to predefine the column names in Finalcollection? Im not able to append if i dont predefine the column names.. What if i dont know the column names? How can i append them?
To avoid defining collection, you have to deal with the decision stage.
The flow execution will move into yes path for only once and the structure of the final collection will be defined. And, Incrementing the counter will make the condition fail from the second iteration which makes the temp collection to append to the final collection successfully.

Hope this helps in understanding better!
 
Ss now i understand it thanks.. A little correction in ur reply #11 for others to refer correctly, decision stage should have counter<1.. So that it will enter the yes path once.. Thanks a lot
 

sivagelli

Well-Known Member
Ah! Yes, edited my post. Thanks!
The condition has to be Counter=1; for the first iteration to be successful, as the initial value is set to 1.
 

kulranjan

New Member
Is there an alternative way to merge your sheets as the above method is not working and causing System.OutOfMemory Exception
 
Top