LINQ Query for selecting

Help
#1

I have two excel files. One is the mapping table and the other is input. I need the output according to the mapping table!!!

The problem is the mapping table is 2D data, i.e. we need to select via two columns and get the 3rd column using that. Can any-one please help me out!!!
Dummy data, is shared below:

Sample_input.xlsx (8.8 KB)
Sample_Mapping.xlsx (8.6 KB)
Sample_output.xlsx (8.8 KB)

1 Like
#2

@Lahiru.Fernando @balkishan @chenderson can you please help him :slight_smile:

#3

Hi @Achal_Sharma

Check this

from p in Product
join c in Catalog on c.Column Name equals p.Column Name
join m in Manufacturer on m.Id equals p.ManufacturerId
where p.Active == 1
select new { Name = p.Name, CatalogId = p.CatalogId, ManufacturerId = p.ManufacturerId, CatalogName = c.Name, ManufacturerName = m.Name };

Change the column names according to ur use

Thanks
Ashwin S
#5

hi @AshwinS2 @Lahiru.Fernando @ovi @chenderson @loginerror

thank you for the reply but I am unable to use this SQL query in UiIath as I am using DataTable as database. could you help me out in this situation, it would be a great help if you share an example workflow.

Thanks
Achal Sharma

#6

Hi @Achal_Sharma

Check this

  1. SELECT Column A,Column B,Column C,Column D
  2. FROM [tablenmame1] JOIN tablename2
  3. ON [tablename1].ColumnA= ColumnA
    use execute non query activity and paste the query

Thanks
Ashwin S

#7

hi @AshwinS2

can you share a small example on this. it would be a great help as never used the SQL query in uipath before.

Thanks
Achal Sharma

#8

@Achal_Sharma
Keep in mind, that a LINQ syntax is similar to SQL but not the same (looked on details)
@AshwinS2 did a fantastic job on his suggestions
Now the statement maybe needs some adoptions:
e.g. to get it running: if Product is a Datatable then you have to change to Product.AsEnumerable
same for the others e.g. Catalog … etc

The fastest way would be you are naming us the datatypes and e.g. some sample values (in short size), so the statement can be adopted

I will crosscheck on the data from your xlsx

#9

@Achal_Sharma
Based on your Excel Samples from above following statement is returning an array of objects:
Input, mapping excels are readin and are available as DataTables

(From i In dtInput.AsEnumerable
Join m In dtMapping.AsEnumerable
On i(0).toString Equals m(0).toString And i(2).toString Equals m(1).toString
Select New Object() { i(0), i(2), m(2) }).toArray

In your samples ( sample_ output) column C is unclear where the info comes from. However you can incorporate by changing: Select New Object() { i(0), i(2), IncorporateHereResultColumnCInfo ,m(2) }).

Currently the approach is not a oneliner. So you have to do following:

  • Create a result Datatable with the structure as needed
  • iterate over the returned Object Array and use Add Row activity for adding this to the result table

Make sure DatasetExtensions is referenced

Please give us feedback on the result, once You have implemented. Thanks

#10

@Achal_Sharma is it working?