How to filter data from excel and store in different datatable

Hi all, I have one excel in that in column “A” I have multiple chq no so according to second letter I need to filter for ex. Chq no second alphabet start with B nd Z ex LB,RB,QZ,RZ then it should me “HL” and if second letter start with rather than bz ex LA,LV then it should be “CL”
so I want store all HL chq no in one dt1 and CL chq no in dt2
Later I need to login browser and in type into need to put dt1 for all HL one by one logout
and login,typeinto dt2 fr all cL one by one nd logout.
Basically want to make 2 datatable one for HL nd one for Cl and then use values in portal.

In img first two are cl nd second two are hl

Initialize your new datatables dtCL and dtHL
Use readrange to make your excel into a datatable, lets call it dtExcel.
Use for each row in datatable dtExcel.
If CurrentRow(0).ToString.Substring(1,1).ToUpperInvariant = “B” OR CurrentRow(0).ToString.Substring(1,1).ToUpperInvariant = “Z” Then add a datarow to dtHL equal to CurrentRow(0).ToString Else add datarow to dtCL equal to CurrentRow(0).ToString (Assuming there are no other values on the 2nd letter aside from B,Z,A and V or that any other letter aside from B and Z are considered CL then this will do)

Hi sayali_rokade,

Use readrange to store your excel into a datatable, say dtMain
Then use filter datatable activity and configure the filter using startsWith or Contains based on your requirement and store the results in different Datatable.

Do you have any sample to store in datatable

Do you have any sample files how to get sorted data table values

Can you pls provide sample cz I didn’t understand how we can use datatable values after login one by one

You can use your excel file for input, Check the below workflow.
Hope this will be helpful

FilterDT.xaml (8.8 KB)

Thanks @Dharunya_Devi will check in my flow (73.0 KB)
@sayali_rokade , Please unzip and check the flow. I attached input file as a check number, sheet 1 is input, CL and HL shaeets are output

1 Like

Hi @sayali_rokade

You can try the following approach

  1. Read the excel and store in dtMaster

  2. use 2 build datatable activity to intialise two tables dt1 and dt2 , dt1 for CL chq and dt2 for HL chq

  3. use the linq query get the CL chq values to dt1

dt1= dtMaster.AsEnumerable().Where(Function(row) row(“Chq no”).ToString.Trim(1).ToLower.Equals(“b”) or row(“Chq no”).ToString.Trim(1).ToLower.Equals(“z”)).CopyToDataTable

dt2= dtMaster.AsEnumerable().Except(dt1.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

Hope this helps u

Thanks & Regards,
Nived N

@Dharunya_Devi thanks for ua efforts ,as we know after this i want to login and one by one i want to print data from for 1 value nd then second then third like wise how we can take.
now it is categorized HL and CL right after that for HL i want to get one value and write in type into then second upto last then logout then same for CL

@sayali_rokade , You can use For each row activity to achieve it.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.