Using Specific Rows from Sample excel and fetch entire row from Master excel?

Hi All,

I have two excels as Master and Sample.
In Sample Excel they are specific rows(like 20) in one column by using these rows, I want fetch that rows in Master Excel which contains More than 1 lakh records. How can I do this, Looking for quick help.

Hi @Pavan_Kalyan_Konda

Use read range acticiry to read both the excel files

Use for each activity to iterate sample excel
Inside that for each activity
Use another for each activity for master data
Inside that master data for each acivity

Use if activity to check the samplerow(“columnname”)=masterrow(“columnname”)

Thanks

Hi Pavan,

Please follow the below steps and see if it suits your requirement.

  1. Use workbook read range activity and input your master excel with 1 lakh data and store the total rows into a datatable MasterDT

  2. Follow the same steps for sample excel store the information into a datatable SampleDT.

  3. Now use for each row activity assign the sample DT. I assume your mentioned column name values should have identical or similar values in the master.

  4. In side for each loop use filter data table activity assign your masterDT and filter the columnname with the sample DT value like currentRow(“columnname”).tostring to match the records in masterDT. Use different datatable to store the output. Now we have filtered records in dt.

  5. Use merge datatable activity to merge datatable values to form a final datatable only with matched records with the sample.

With the use of for each loop we can iterate 20 times bcz of sample file data and get our desired output in final datatable.

  1. After the for each row loop you can use workbook write range activity to wrote the filtered data in different excel or different sheet in master excel.

Thanks.

thanks for your inputs Robin,
I didn’t get this Use if activity to check the samplerow(“columnname”)=masterrow(“columnname”). I need to assign variables as samplerow n masterrow? If i’m wrong correct me.

Hi

Did we try using simple JOIN DATATABLE activity with inner or left join methods

Have a view on this doc for an example

Cheers @Pavan_Kalyan_Konda

Hi @Pavan_Kalyan_Konda

Another alternate way is

Read range the master dt

Read range the sample dt

Use for each row in sample Dt

Assign
NewDt = masterDt.select(“[youmasterDtcolumnname] = '”+Currentrow(“yoursampledtcolumunname”).ToString+“'”).copytodatable()

Use path exists activity and create a dummy file path with file name.xlsx

Use if condition path exits

If path exists use
Then -->NewDt in append Range
Else–> New Dt append Range

Regards

1 Like

Good idea @pravin_calvin may be linq is the better solution for this requirement since master file having more than one lakh rows. Thanks.

2 Likes

Hi

Have a view on this thread as well for similar scenario

Cheers @Pavan_Kalyan_Konda

1 Like