How to filter data from excel and store in different datatable

Help Studio
1

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.

IMG20220705145523
IMG202207051455231872×4160 1.09 MB

In img first two are cl nd second two are hl

2

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)

4

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.

5

Do you have any sample files.how to store in datatable

6

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

7

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

8

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

FilterDT.xaml (8.8 KB)

9

Thanks @Dharunya_Devi will check in my flow

10

Sample.zip (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
11

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

12

@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 dtCL.so 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

13

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

14

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