Loop condition in all sheet first sheet output empty

[xxGoRpa]

Sequence1.xaml (40.3 KB)
Test Price2.xlsx (12.7 KB)

Hi guys,

i have upload my xaml and my input file call Test Price
I wan to loop all in my worksheet as this is dynamic however i meet into trouble when it write the output in my first sheet is empty

My output file will contain all the worksheet with Cost Price and Project ID
IT can be empty or have value in it due to my Test Price 2 Excel file

can anyone help me out

This is how the flow is

1. Excel Application Scope (Input file)

   • Get all Worksheets (Listallsheets)

2. For Each Sheet in Listallsheets

   • Read Range (Sheet: YourSheet, Output DataTable: dtInput)

3. Assign activity:

   • costCentreRowIndex = -1
   • costCentreColumnIndex = -1
   • projectIdColumnIndex = -1

4. For Each activity (ForEach activity, TypeArgument: DataRow, Values: dtInput.Rows)

   • For Each activity (ForEach activity, TypeArgument: DataColumn, Values: dtInput.Columns)
     • Assign activity:
       • costCentreCell = row(column).ToString()
       • Log Message: "Checking Header: " + column.ColumnName + ", Value: " + costCentreCell
       • If condition: costCentreCell.Contains(“Cost”) And costCentreCell.Contains(“Centre”)
         • Log Message: “Found ‘Cost Centre’ header!”
         • costCentreRowIndex = dtInput.Rows.IndexOf(row)
         • costCentreColumnIndex = dtInput.Columns.IndexOf(column)
         • Break

5. Build DataTable (Output DataTable: dtOutput)

   • Add Data Column (Column Name: Cost Centre, Data Type: String)

   • If activity (condition: costCentreRowIndex <> -1 And costCentreColumnIndex <> -1)

     • Add Data Column activity (Column Name: Project ID, Data Type: String)

6. If activity (condition: costCentreRowIndex <> -1 And costCentreColumnIndex <> -1)

   • For Each Row activity (TypeArgument: DataRow, DataTable: dtInput, StartingIndex: costCentreRowIndex + 1)
     • Assign activity:

       • costCentre = row(costCentreColumnIndex).ToString().Trim()

       • If activity (condition: projectIdColumnIndex <> -1)

         • projectId = row(projectIdColumnIndex).ToString().Trim()

       • Else

         • Assign activity:
           • projectId = String.Empty

       • Add Data Row activity (DataTable: dtOutput, ArrayRow: {costCentre, projectId})

7. Remove Duplicate Rows

8. Write Range activity (SheetName: YourSheet, DataTable: dtOutput)

[Yoichi]

HI,

Why do you use skip method to assign to rowsToProcess? It will be no row because there is valid data only in the first row of the datatable and it will be skipped by Skip(costCentreRowIndex+1).

Regards,

[xxGoRpa]

Hi due to my 2nd sheet and 3rd sheet as they are not in the first row

i think is causing the issue as i was inspecting is there anyway to workaround ??

[Yoichi]

@xxGoRpa

i think is causing the issue as i was inspecting is there anyway to workaround ??

I think it’s unnecessary to use rowsToProcess itself. Why don’t you process row directly (If there is blank row, try to process next row using Continue activity)? Is there any problem?

Regards,

[xxGoRpa]

The original was supposed to use this
For Each Row activity (TypeArgument: DataRow, DataTable: dtInput, StartingIndex: costCentreRowIndex + 1)

[Yoichi]

@xxGoRpa

StartingIndex: costCentreRowIndex + 1

StartIndex should be 0, because there is just 1 row in the firsst sheet and it’s wow index number of the datatable is 0.

Regards,

[xxGoRpa]

@Yoichi

(If there is blank row, try to process next row using Continue activity)?

but i got issue getting this around so i found a way was to use this
rowsToProcess = dt.Rows.Cast(Of DataRow)().Skip(costCentreRowIndex+1)
then for each row in rowsToProcess i proceed

[xxGoRpa]

so u mean i should put 0 instead of costCentreRowIndex + 1 ?

[Yoichi]

At least, the above is necessary to read data in the first sheet.
However, as i don’t consider other sheets, please find the better way to process whole the sheets.

Regards,

[xxGoRpa]

is there any other way for it to solve together with all the sheet at the same time ?

Got it actually i use from chatgpt
For Each Row activity (TypeArgument: DataRow, DataTable: dtInput)

• Assign activity:

  • costCentre = row(costCentreColumnIndex).ToString().Trim()
  • projectId = row(costCentreColumnIndex + 1).ToString().Trim()

• If activity (condition: String.IsNullOrWhiteSpace(costCentre) Or String.IsNullOrWhiteSpace(projectId))

  • Continue activity
  • (This will skip the rest of the activities for the current row if either costCentre or projectId is blank)

• Add Data Row activity (DataTable: dtOutput, ArrayRow: {costCentre, projectId})

[Yoichi]

For now, I just fixed it for the first sheet. Can you check the following sample?

Sample20231130-2.zip (16.7 KB)

Regards,