Extracting the data using two excel file having one common column

Help Activities
, , ,
1

I have two different excel file(Country , State) so i want to extract the using both the excel files.
i want the output like image i have uploaded and i have also uploaded the two excel file
Country.xlsx (8.4 KB)
State.xlsx (8.3 KB)

Screenshot 2022-08-22 121920
Thanks for your help

2

Hi @sunain_chahar,
please follow these steps.
1- Read both files in different datatables.
2-build datatable for your desirable datatable for your output.
3-use loop in first file datatable and inside loop use add row(output datatable which you have built through build datatable)
4-repeat 3 step for 2nd file

3

Thanks for the reply
Can you explain in detail

4

little bit wait I am sending you a code making on your files

5

LINQ Approach:

Prepare target DataTable with build datatable and model an empty datatable with the cols, Country, State, City - dtResult

Assign Activity:
dtResult =

(From d1 In dtCountryState.AsEnumerable
Let mtc = dtStateCity.AsEnumerable.Where(Function (d2) d2("State").toString.Trim.ToUpper.Equals(d1("State").toString.Trim.ToUpper))
Let cts = String.Join(",", mtc.Select(Function (c) c("City").toString.Trim))
Let ra = New Object(){d1("Country"), d1("State"), cts}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

grafik
grafik860×232 21.9 KB

grafik
grafik480×554 59.2 KB

6

can you share your workflow please

7

Find starter help here:
JoinDT_1Col_ConcatGroupMembers.xaml (14.2 KB)

About LINQ:
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

One of my colleagues @kumar.varun2 is preparing an alternate approach with a dictionary lookup strategy. Just give him some little time as well

8

Hi @sunain_chahar

As informed by @ppr, you can work out the solution using the dictionary lookup approach.

  • Build the Output table template (dt_Output)

    • image

  • Read the input excel files (dt_Country, dt_State)

  • Create the Dictionary using the LINQ (StateCityDict)

(
	From row In dt_State.AsEnumerable
	Group row By k=row("State").ToString.Trim.ToUpper
	Into grp = Group
	Select Tuple.Create(k, String.Join("/", grp.Select(Function(gr) gr("City").ToString)))
).ToDictionary(Function(t) t.Item1, Function(t) t.Item2)
9

Main.xaml (13.1 KB)
State.xlsx (9.2 KB)
Country.xlsx (8.4 KB)
pleasse check @sunain_chahar