2

i implemented a normal 'for each'-Loop (not the excel specific for each row) in my UIPath Project.

The for-each Loop looks through a datatable with previously retrieved data from an excel file.

The for-each-loop then itererates through the Data with an if-else behind it.

Lets say it is as follows:

for each item in dataTable:

if (content of item == "10")
{
write cell: "Test" into A + index.ToString() 
// leads for instance to writing into excel column 'A1' 
}

else 
{ write cell: "ElseTest" into for C + index.ToString() }

-- I used the syntax just for presentation-purposes.. :D

-> So the problem is: writing into the cells takes so much time, where else putting out a MessageBox with random text inside the if and elses is done in milli-seconds, so the for-each-loop can't be the problem...

I ran that process with task manager opened and found out that Excel starts up, CPU percantage increases heavily, immidiately jumps to 0%... same happens again, for each iteration through the loop.

Why is that? Is there a more optimised way to do that?

I need the for-each structure, because I need to check if it's either value 1 or value 2 inside the cell...

Picture of my Excel Scope

Wolfgang Radl
  • 2,319
  • 2
  • 17
  • 22
franz909
  • 182
  • 2
  • 15
  • Could you share a screenshot of your workflow, please? – Wolfgang Radl Jan 15 '19 at 16:12
  • I have added a screenshot, do you guys have any suggestions? @WolfgangRadl – franz909 Jan 17 '19 at 06:56
  • This is not a direct answer to your question, hence the comment. You can treat Excel as a `DataSource` (I.e. a database) and query it directly. You could even do an 'UPDATE ... WHERE ...' to bypass all the looping. Here's some additional info in case you are interested: https://forum.uipath.com/t/how-to-use-excel-as-datasource-and-execute-sql-query/34753 – Roel Strolenberg Jan 17 '19 at 14:40
  • @RoelStrolenberg thanks a lot, extra info always appreciated! – franz909 Jan 17 '19 at 20:13

2 Answers2

0

I think opening excel on each iteration is the time consuming part so I suggest

  1. open excel before your for each
  2. use attach window activity (https://activities.uipath.com/docs/window-scope) inside your foreach to set focus on excel file
  3. close excel after your foreach or when you are done with the excel file

I would strongly recommend reframework (https://github.com/UiPath/ReFrameWork/tree/master/Framework) if you are not using it. It is an excellent starting point for RPA projects.

If you are using reframework you can open your excel file in InitAllApplications.xaml do your operations in Process.xaml and close your excel inside CloseAllApplications.xaml.

NTP
  • 4,338
  • 3
  • 16
  • 24
  • What makes you think the author opens Excel on each iteration? If fact, Franz states that he's operating on a `DataTable` object that was previously retrieved by an Excel activity. – Wolfgang Radl Jan 15 '19 at 15:45
  • Valid point - if he opened and closed Excel on each iteration, that would explain a delay - and also why he'd only see one instance of Excel in the Task Manager at a time. Guess we need to see the process map first. – Wolfgang Radl Jan 15 '19 at 16:11
0

One possible option is to use the Read Range activity, manipulate the DataTable object itself, and then at the end write said object back to the same range using the Write Range activity.

Here's an example. My sheet contains 100 rows with all numbers from 1 to 100 in column A. Whenever there's a "1" in the cell, the data will be overwritten (in the DataTable object).

enter image description here

Wolfgang Radl
  • 2,319
  • 2
  • 17
  • 22
  • Thanks! Where do you get the "Rows" from in "dtSheet1.Rows(i)" ? That solution would work for me, if I knew how to write onto the specific row of my datatable.. because the datatable wants an of a specific type - but my input is a String. How can I access the row and write a String into it like you did in the above example? – franz909 Jan 17 '19 at 10:53
  • You can access one individual cell on a row like this: `dtSheet1.Rows(i).Item(0)`. Instead of providing the index, you may use the Column's name as well (e.g. `.Item("foo")`. – Wolfgang Radl Jan 17 '19 at 18:46
  • Thanks a lot! I puzzled and played around on this for some minutes and figured it out myself. “self-problem-solving” has the best learning effect! :D – franz909 Jan 17 '19 at 20:12