How i do i read this excel using nested for each loop?

image

It needs to retrieve all bank accounts and load it in application. There is a case that each account can have more than 1 payment methods. How do i loop the payment methods?

A quick response would be highly appreciated. Its urgent.

1 Like

Hi,
I don’t want to get too technical, but here is how I would do it…
—Get unique values in “Bank Branch” column
—Loop through unique values
-----Loop through each row for each unique item

So, you would design this with below “pseudocode” - and this is not actual code, but in part:

Excel Scope
    Read Range to dt1 as data table
    branches as Array<of String> = dt1.AsEnumerable.Select(Function(r) r("Bank Branch").ToString.Trim ).ToArray.Distinct

    For each b In branches // with TypeArgument as String
        For each row In dt1.AsEnumerable.Where(Function(r) r("Bank Branch").ToString.Trim = b ).ToArray // with TypeArgument as DataRow
            TypeInto // with row("Account Name").ToString.Trim
            TypeInto // with row("Account Number").ToString.Trim
            // and so on

I believe this way keeps things clutter-free and efficient, but there’s various methods to accomplish the same thing.

Hopefully, that helps. Also, do some searches on the forums for some additional information on how to use loops with Excel.

Regards.

1 Like

I need to load all the records in the system. So if you see the data excel.
I need to create 3 bank accounts in the system that is ‘123’,’ 234’ and ‘345’ with their respective details.

So Excel >> Read range stores all value in DT1 data table.
So far it is good.

Now the challenge is, if you see the excel, I have to run another loop for Payment Method/documents.
There are 3 accounts but 4 records, 1 account has 2 payment methods.

How do i take the inner loop for the payment methods to make this work?

1 Like

See this, which is also in my last post:
dt1.AsEnumerable.Select(Function(r) r("Bank Branch").ToString.Trim ).ToArray.Distinct

That will create an Array of String of all the unique values in the “Bank Branch” column. Use that array in your For each, and filter your table by each unique value. This is shown also in my last post here:
dt1.AsEnumerable.Where(Function(r) r("Bank Branch").ToString.Trim = b ).ToArray

That will create an Array of DataRows, and use that in the inner loop, as shown in my previous post.

Then, reference each item using the column name with the row.
row("Account Name").ToString.Trim for example.

Regards.

1 Like

Sorry, I realized you said account name. Just change “Bank Branch” to “Account Name”, which is just the column name when assigning the distinct accounts array. And like I said, it’s all there in the For eaches that I presented in my first post. - just adjust the column names if needed.

Regards

1 Like

Thank you for the quick responses.
Can u share me a sample xaml for the nested for each loop pls.

1 Like

mallika.xaml (13.0 KB)
Here’s a sample, that Reads the source excel data, then runs the rows through a loop per account name as your requirement.

I put in variables to represent your column names, so you can make adjustments. And, I put in 2 Type Intos, but you can change how you process the items if desired.

Regards.

1 Like