Need to fetch filter data from excel

Help Studio
, ,
1

I have excel and I need to group by row data and fetch group data and store into data table.

For reference:-

Excel data:-

image
image411×507 10 KB

Group by Product col:-
image

I need group by rows data and store into data table

2

Can anyone suggest for the same?

3

Hi @ashukla7836
You can filter the input datatable by using Filter DataTable Activity to filter based on Product Column and then store the resultant in another datatable

4

But I don’t know what data will store into excel excel column name.

5

Hi @ashukla7836
i am not cleared what u had said, can u clarify it properly?

6

Sure,
I am getting data from sql and store into excel sheet. So I don’t know what data is in excel sheet.
But I know only column name. So in “Product” column I need to grouped rows data like:-("Arrowroot,Potato Chips) in Product column. So I need to grouped Arrowroot data. And after grouping I need to fetch filtered data into Data Table.

7

So by not sure you mean that whether the data u need group / filter is available in excel or not , is it right ?

8

How to group row data.?

10

Hi @ashukla7836

Please Use datatable.select method
DataTable.select(“Product = ‘Arrowroot’ AND Product= ‘Potato Chips’”).CopyToDataTable
Hope this helps you!
Regards

11

Hi Pravin,

I can’t use select method because I don’t know row values

I am trying with dt.AsEnumerable().GroupBy(Function(x) x(“Product”)).Select(Of DataTable)(Function(s) s.CopyToDataTable). to groupby rows values.

12

Okay, try to explain again my scenario.

This is the excel data and I have read this and stored into Datatable.

10001 1-May Arrowroot 51
10002 2-May Bran 93
10003 3-May Carrot 46
10004 4-May Chocolate Chip 62
10005 5-May Oatmeal Raisin 49
10006 6-May Potato Chips 53
10007 7-May Bran 47
10008 8-May Potato Chips 76
10009 9-May Carrot 114
10010 10-May Carrot 57
10011 11-May Oatmeal Raisin 34
10012 12-May Bran 35
10013 13-May Oatmeal Raisin 65
10014 14-May Arrowroot 76
10015 15-May Arrowroot 87
10016 16-May Potato Chips 76
10017 17-May Potato Chips 65

I need output like -

10001 1-May Arrowroot 51
10014 14-May Arrowroot 76
10015 15-May Arrowroot 87
10002 2-May Bran 93
10007 7-May Bran 47
10012 12-May Bran 35

10003 3-May Carrot 46

10004 4-May Chocolate Chip 62.

out put

13

dt.AsEnumerable().GroupBy(Function(x) x(“Product”)).Select(Of DataTable)(Function(s) s.CopyToDataTable) and storing in ienumerable type variable.
and try to use for each loop.
But unable to do that