Dynamically Filter Excel Data and Store in DT

Help Activities
, ,
1

image
intial stage of excel / input

image
after filter / expected output

this is my scenatio, now I can track/find out the unique id(column B) by using dt.DefaultView.ToTable(true, “yourColumnName”) this code

but now I want to get this exact output in a datatable and the datatable will be updated with per transaction(here I am considering each unique id as a transaction, e.g 123,456,789)

each time I am getting an unique id I will keep the branch and desg in a datatable and want to write it to a corresponding system.

How may I easily solve the scenario?

2

Hi @Tanzill_Ahsan ,

It looks like you have to filter the Id column with the value needed.
image

Have you tried using the Filter Datatable Activity ? Configure the Id column value as "789"

Let us know if this is not what was expected.

3

@supermanPunch yes but the value is dynamic and filter datatable activity is not solving my scenario in this way you had shown

4

@Tanzill_Ahsan ,

If the value 789, is a dynamic, then it would need to be stored in a variable. You could use that variable instead of hard coding the value.

5

@supermanPunch 789 is a dummy value and not fixed, they excel will update everyday

6

@Tanzill_Ahsan ,

Do you have to create multiple datatables based on the Id value ? i.e filtering based on each id value and preserve the datatable filtered ?

7

@Tanzill_Ahsan

Follow the steps below

Uniquedt - datatable containing your unique ids
Dt - original data with all rows

  1. Use a for each row in datatable…on unique datatable you got(uniquedt)
  2. Then inside it use a filter datatable on the original table dt and assign output to filtereddt…and in the condition provide the "ID" on left side and in between use equals and on right side give currentrow("id").ToString
  3. After that in the filtereddt you have your data only for one id

So for each interation you will get new filtered data

Hope this helps

Cheers

8

@Anil_G

unique_test.xaml (11.2 KB)
dummydata.xlsx (10.3 KB)

did the design with the following steps you’d mentioned but not getting the desired result under DT(filtered_dt)

do you have a few minutes to have a look?

Workflow SS:
image
image
image

9

@Tanzill_Ahsan

I see the id column is of type integer so as we did tostring it is not filtering

Please use currentrow(“id”) and remove .ToString.

And to write data into separate sheets you can change the sheetname to Currentrow(“id”).ToString

image

image
image1117×637 43.5 KB

Hope this helps

cheers

1 Like
10

@Anil_G thank you this solution is solving my scenario

but can you please mention one more thing, how may I keep the OUTPUT in a datatable instead of writing in separate excel sheet

image

Like, after getting the first result of 123 in a DT, I will write it in the system and then the database will be cleared and then I will keep doing the same procedure for the rest of the data.

Thanks

11

@Tanzill_Ahsan

You already have it in filtered datatable…so you can include your database activities inside your for loop and use the filtereddt to populate the data for eqch iteration it repeats the database steps as wel with different set of data

Cheers

1 Like
12

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