Help with Excel Automation -Loop

Help Studio
, ,
1

Hello guys, I want to write a program in UiPath studio for: There is an excel (robot list that acts as a base document) that contains information about robots. Each line represents 1 robot. Using a loop that should start from line 9 (since lines 1-8 are all headers), three values ​​should be saved as a variable for each line, i.e. each robot. The values ​​are in column C, D and H. Now the row may be empty, then the loop should simply go to the next row. Thanks! :slight_smile:

2

Hello @marco.roensch

Excel Application Scope (Input: ExcelFilePath)
Read Range (Output: DataTable, SheetName)

For Each Row (Input: DataTable)

    If (Not String.IsNullOrEmpty(row("ColumnC").ToString()))
    
        Row is not empty
        RobotName = row("ColumnC").ToString()
        RobotType = row("ColumnD").ToString()
        Status = row("ColumnH").ToString()

    Use the variables (RobotName, RobotType, Status) as needed
    
    If row is empty, the loop will move to the next row automatically

Thanks & Cheers!!!

3

Hey @Kartheek_Battu

I already thank you. For testing reasons I tried to display the values of the variables. But the execution doesnt stop. Is there anything wrong in the following?

image
image702×656 54.6 KB

image
image607×706 46.9 KB

Regards

4

@marco.roensch

  1. Set a breakpoint on the activity where you want to pause.
  2. Run the workflow in Debug mode.
  3. Execution will pause at the breakpoint, allowing you to view variable values in the “Locals” panel.
  4. Continue execution when ready.
5

@marco.roensch
feel free to share your excel / sample data with us

we would handle with a range offset (e.g. Read range - Range A9). Also we can avoid to span the entire Box (Z1000) as read range reads in the spanning block out of the box)

Or

we go on on rowbase and skip the first X rows

Im Allgemeinen vermeiden wir Umlaute in Dateinamen, auch wenn es ein Relikt aus älteren Zeiten ist

6

please share the excel or sample data to provide better solution

7

ExcelExample.xlsx (14.9 KB)
For sure! Thanks!
Please note that the Excel file is normally an .xlsm file

8

@marco.roensch
We used the simplified EXCEL and keep in mind that in the other EXCEL the structure is more complex (more cols, col merges…)

We applied the principle: Divide and Conquer and scoped the read in range to A9:H1000 like

grafik
grafik586×515 38.7 KB

With the input:

  • we reduced the cols to C,D,H as we dont use ColNames we are using ColIndex
  • We filter out empty / unnneded lines using a linq

grafik

arrColSet = {2,3,7}.Select(Function (x) dtData.Columns(x).ColumnName).toArray
dtCleansed =
(From d In dtData.DefaultView.ToTable(False, arrColSet)
Let iac = d.ItemArray.Select(Function (x) x.ToString.Trim({ChrW(8230),Chr(32)})).toArray
Where Not String.IsNullOrEmpty(String.Join("",iac).Trim())
Select r =d).CopyToDataTable

And will get:
grafik

The trimming we can adapt and it looks to us that the 8230 Char (Auslassungszeichen) was only used for the demo and will not be part of the production data.

9

Thank you for your detailed response!

10

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