Excel sheet duplicated from dt

Learn Academy Feedback
, , , ,
1

Hello i have a function that logs stuff into a excel sheet. I am using a build data table at start then i have a excel app scope with a write range in it to get the headers in teh excel. I am then using a for each ( to proccess some files) This its where the problem happeens as i the robot works it loggs different outcomes that can happend. Only problems it duplicates the line for each time it writes into the excel sheet. It looks like its storing everything in memory and writes the new line including all the old once. I am using a app scope with a add data row and a append rangein the for each

2

Hi @langsem,

the problem becomes clear when you step through the sequence of activities.

  • when it add datarow to the datatable for the first time, the datatable contains just 1 row, write range also 1 row only and excel contains 1 row.
  • but on the second add datarow, the datatable contains both the first row and second row, write/append range contains 2 rows, excel now contains 3 rows.
  • so when you write/append range on the second time, it the duplicated the first row twice.
  • so when you write/append range on the third time, it duplicated the first row thrice, second row twice.
  • etc.

Did you see what’s wrong ?

3

Yes know whats wrong, but dont know how to solve it. Can i use a remove duplicate function before it append range ?

4

You need to move the write/append range to outer scope.

5

what you mean, move it outside of for each ?

6

Yes. I cannot see your complete workflow so you have to try it yourself.

7

I cant, because in my workflow i have different actions. and the reason why i use append range several times in the for each its because if something failes it will write to the logg

8

the biggest problem with moving the append range out of the scope its because, lets say the robot crashes, then i will lose the data table and it wont append to range

9

Can i maybe to like this, put the entire for each into a try catch, then if it failes it writes to the logg ?

10

You can also initialize the datatable variable that you add datarow at the beginning of the for each. That way, it will not have duplicate values.

Assign activity: dt = New DataTable

1 Like
11

wont it get duplicates as long as the dt are inside the for each ? not sure if i understand how that can help ? as it will result in the same

12

How the robot works its that i reads through a bunch of pdf files. Then it creates a data table ( to assign headers) then it loops through each pdf file and perform certaint tasks to them based on what value it has in the pdf. If a value its wrong it then appends a status to the excel sheet and moves to next pdf file ← that causes the complicated thing, because it has to log all actions and thats been performed inside the for each.I can move the append range outside of the for each but problem then its that if the robot crashes i will lose the data table.

13

To handle server or application crashes, your need a transactional framework that read each pdf file and writes each pdf data and does logging. You may want to consider the REFramework.

Robot crashes on the other hand, could be totally different issue. Why should you create a script to handle UiPath Robot crashes when the script depends on the UiPath Robot to run? Are you certain the UiPath Robot crash will be after the try-catch ? Before?

14

REFramework its not the kind of logging i need. The logging that happens in the excel its based on the pdf values. Its to keep track of the pdf not the process itself.

Then i am certaint it wont lose the datatable as i have to move the append range outside the for each

15

Have you created folders to move your pdf files?

  1. Completed - pdf processed successfully. Here is where the pdf file is moved into after writing successfully to Excel
  2. Pending - pdf waiting to be processed. Here is also where the script reads the pdf at every start

Read the pdf to text. Text extraction and checking on the value to set status in Excel.

16

yes i have the structure like this
mailfolder ( all pdf files ) → onwork( pdf files thats currently being handled) → then resultfolder( proccesed files )

17

Robot crashes is not something you can automate.

18

I know, thats not the case either. But want to make sure that i dont lose the data table

19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.