For Each Row Help!

Help
1

I have a spreadsheet where I need to copy data from each row and paste into a web application.
There are two types of data I need to copy from the spreadsheet and I am wondering if I can get some help with how to do this.

First I need to copy the header information, example below.

Company Posting Sequence Explanation
542 1 Blue Bridges
542 1 Blue Bridges
542 1 Blue Bridges
750 1 Coalgate PMP
750 1 Coalgate PMP

I only want to copy the information once each time the Company Number changes. I have come up with the below workflow so far.

Read Range Excel activity to dt1

Assign → CompanyNumber = “”

ForEach Row in dt1
If → CompanyNumber.Equals(row(0).ToString)
True - do nothing
False - paste header details

This works as expected.

The part I am having issues with is pasting the child lines for each company number. Example of the lines below. All data is in the same spreadsheet.

Company Posting Sequence Explanation Count Rate Factor Cost Centre
542 1 Blue Bridges .5618 * 631587
542 1 Blue Bridges .5963 * 652961
542 1 Blue Bridges .6514 * 628794
750 1 Coalgate PMP .9853 * 532978
750 1 Coalgate PMP .6528 * 564891

I can only get it to go through one line. I need to paste all the lines where the company number is the same. Can someone help me with this please? I am new to RPA and I have not found anything in the searches to help me.

2

Have you tried with Ling queries (Select)?
Can you share your Workflow?

3

I am not sure what that is?

4

Can you show me how it should look in application?
Instead of select there is a UiPath activity to filter data. And based on filtered data, you can use another for each sequence.

5

May i know what was the issue we were facing
Cheers @workwithit

6

I can only get the bot to go through the first line. I want that to happen for the first half of the process, but the second half of the process I need the bot to paste an unknown number of lines where the company number is the same. So if I have company 452 listed 6 times I need to get the data from all 6 lines and paste all 6 lines into the web application.

7

Fine
so how you are getting the datatable from the excel along the range in READ RANGE activity
Cheers @workwithit

8

image
image1015×479 32.5 KB

9

image
image794×797 25.2 KB

10

Book1.xlsx (10.6 KB)
Here is an example of the spreadsheet I am using.

11

@workwithit - do you want to paste only for a single company (e.g. all lines that are company 542), or do you want to group each company and then paste each line (e.g. 3 lines that say 542, then the next iteration of robot does 2 lines that say company 750, etc) ?

12

I want to paste all lines for company 542 and then save it and move to the next company.

13

My proposition is:
Create two datatables.
In first DT you will unique values for company in second DT you will hold child values for that company (you use filter activity for that :slight_smile: )

14

Here you go. This LINQ solution will group everything by Company ID and separate into it’s own datatable. Then in your workflow (where I have the write range portion) you can paste into the application.

You do not need anything else that you’ve written, such as the original for each to grab header information as that is already built in. Let me know if you have any questions :slight_smile:

workwithit.xaml (9.0 KB)

EDIT: The LINQ statement is put into an assign and returns an IEnumerable(Datatable) variable which I’ve called dt1Groups.

Assign dt1Groups = From row In dt1
                      Let company = row.Field(Of Double)("Company")
                      Group row By company Into Group
                      Select Group.CopyToDataTable()
15

@workwithit
Have you tried playing around with the “Filter Data Table” wizard? You can remove or keep all the rows of your datatable that contain the rule you set. i.e., build a DT of just one company with all the lines for it.

Then, for that DT, use a For Each Row and some string manipulation to extract the data that comes after the company name (maybe into a List or Array).

Then loop for the next company…

16

First read cell A1 → And pass data to NEWYourString

Assing
CompanyNumber = NEWYourString

Then use your for each row

If → row(0).ToString.Equals(CompanyNumber)
…

17

@workwithit - did the LINQ solution work? Any questions on it?