First, we build a datatable that contains the 5 wanted columns at the output with the wanted order (ID, Time In, Time Out, Weight In, Weight Out), called output_DT.
Then we read the excel file, called DT
Then foreach row in DT, we use a filter on a temporary datatable that has the value of DT.
→ if the temporary datatable never has seen the ID, then we create an empty row where we fill in 3 data
→ if the temporary datatable already has seen the ID. The problem is output_dt already has filled only the columns A, B and D, but we need a way to know if the column C or E is filled in to ask the robot to fill it.
- to do so, the robot has to find a case where the ID already exist + column E is empty. If the condition is met, a break happens and a boolean is used to not add a row but only fill in E
- if the robot does not find the case where ID exist + column E is empty, then the boolean is set to true, so the robot create a new row to fill in the information
Let me know if I am still unclear on some elements !