How to do Nested For Each Excel Row

CurrentRow(1) and Row(1) value are the same
CurrentRow(2) and Row(2) value are the same

Assuming all the workflow is working until the last For Each with If Activity that makes me confuse why it doesn’t loop when it finished the first process in that For Each and If condition?
Can anybody help to explain and give me solution for this?

Hi @YUDISTIRO_Evan

Can you please explain what you are trying to acheive?

You need not check currentrow = Nothing because when there is a row only then it comes into loop

and also IsNothing(currentrow) is the syntax to check nothing or Null

cheers

Hello @Anil_G

Have you checked the flow in debug mode in StepInto mode? If not please give it a try, that will help to understand the flow.

Else plz explain the actual requirement. As you are using the multiple if condition in both Parent and child loop, if it doesnt meet then the loop will not start.

Thanks

  1. So actually there are two excel sheets that I use in this process.

  2. From this workflow I want to process the first sheet with blank value then I use CurrentRow = Nothing.

  3. Then if there is no blank value in the first sheet, I do the same process in the second sheet to process the blank value of the second sheet Row = Nothing

  4. After I process all the blank value row in each sheet, I want to process each row in the second sheets but each row in the second sheet should be the same value as the first sheet and if there is still a same value in the second row as the value in the first sheet then I want it to be processed in loop, so I use Row().tostring = CurrentRow().tostring, but as of now the process didn’t work as I expected, it didn’t process in loop in the last For Each with that If activity condition, so I assume there is something wrong either in the for each or the if condition.

So that’s it, if there is any feedback please help me.

So I have explained the detail of the process before this, and I have tried the debug mode and step into to see the process workflow but the flow didn’t go to the right direction at the last for each and if activity so that’s what I’m assuming as the error and need some help either I need to do something with the last for each or the if condition that’s not clear? as for the example of my workflow is the same as the picture I put before with the same variable and condition there.

Oh I think this is the correct one

Hi @YUDISTIRO_Evan

What do you mean by processing blank values?

You want to remove the blank rows or write some data because as of now i do not see anything that you are doing for blank check

And also currentrow = Nothing is wrong syntax…and if you want to check if any of the column in current row is blank then currentrow.itemarray.contains(string.empty) is more appropriate

And the comparison of currentrow.tostring= row.tostring will also be always true because both return system.data.datarow …here as well you are not comparing the values ypu are comparing the type of those rows

If you can share a sample input and smaple output that would be help because as of now i see what you are trying and what you wrote are two different things and of which , what you want to do is also not very clear to advice on what to do next

Cheers

Cheers

What do you mean by processing blank values?

  • I mean I want to put the status “Error: Some fields are blank” in the status column

And also currentrow = Nothing is wrong syntax…and if you want to check if any of the column in current row is blank then currentrow.itemarray.contains(string.empty) is more appropriate
-Oh thank you for the knowledge, I just know it

And the comparison of currentrow.tostring= row.tostring will also be always true because both return system.data.datarow …here as well you are not comparing the values ypu are comparing the type of those rows
-Yeah, I tried to use CurrentRow(“ColumnA”).tostring=Row(“ColumnA”).tostring too but when I tried it in loop when there is another same row value, the loop failed

If you can share a sample input and smaple output that would be help because as of now i see what you are trying and what you wrote are two different things and of which , what you want to do is also not very clear to advice on what to do next


-So the first thing I want to put status for each blank row for each sheets (sheet1 and sheet2) then I need to put Receiving System and Roles in loop for each User ID which first need to be CurrentRow(“A”).tostring=Row(“B”).tostring
But somehow for each activity failed to loop it

hopefully my answer will help and please help me

@YUDISTIRO_Evan

As your requirement is to check blanks the previous itemarray is not wnough as the last column is always empty for you. Secondly the column names you are using a A ,B etc which are wrong…

While reading excel using read range if you check the add header checkbox then the first row is taken as headers so you have to use corresponding names else if add headers is unchecked then column names will be auto populated with column1 ,column2 like this …

So follow below steps

Lets say data is read into dt1(firstexcel) and dt2(second excel)

  1. For each row in datatable in dt1
  2. Inside that use a if condition with
    (IsNullOrEmpty(Currentrow("columnName")) OrElse CurrenRow("ColumnName").ToString.Trim.Equals(String.Empty)) Or (IsNullOrEmpty(Currentrow("columnName2")) OrElse CurrenRow("ColumnName2").ToString.Trim.Equals(String.Empty))
    Add more conditions if you want to include more columns
  3. In the then block use CurrentRow("TargetColumn") = "Duplicate row(Whatever you want to write)"

Till now it is for the first part.for second part you dont need nested if but follow there steps

  1. For each row in datatable dt2
  2. Inside loop use filter datatable input as dt1 and output as dtoutput
    conditions "Columnname1" is equal to or Contains Currentrow("Columnname1").ToString
  3. Use if condition dtoutput.rowcount>0 this is to check if a matched row is found for dt2 in dt1
  4. In then use the assign that you want to do

cheers

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