Filter data in excel file

Hello,

How can i filter data from excel document and store each group of that data in a new excel document?

I though about regex… but don’t think it’s right way… !

Any suggestions?

Thanks

There are two activities which allow filtering of data. If your excel data is formatted as a table, you can use FilterTable. If not, read the data into a datatable and use the FilterDataTable activitiy:

image

@mz3bel

newDT = yourDataTable.Select("[Column1] = ‘value’ AND [Column2] = ‘value’ …").CopyToDataTable

Where yourDataTable is original input data. Like this apply select query on input data and will give you output as dataTable. Then use Write Range Activity to write into Excel file.

1 Like

Hi Buddy @mz3bel

We can handle this with two methods
–using the read range activity and getting the datatable as output and using SELECT method to get a group of data after filtration, where the datatable with filtered datatable can be entered to a excel with write range activity…The reason why i m mentioning this as a first choice is if your filter condition and its value involves any numerical value or datetime value, it cannot be handled with filter datatable activity, unless the datatable is created with a build datatable activity with that columns in datatype INTEGER, as the datatable obtained from read range will have all the columns with type STRING
may be like this
outdt = outdt.Select("[yourcolumnname1] = ‘value to be compared’ AND [yourcolumnname2] = ‘value to be compared’ ").CopyToDatatable()

Then second method is
–as said before we can use FILTER DATATABLE activity with the input datatable been passed from the read range activity and output would be of type datatable
here we can apply any condition with string values to the columns in the datatable.

Kindly try this and let know buddy
Hope this would help you
Cheers @mz3bel

1 Like

So were you able to proceed now buddy
Cheer @mz3bel

1 Like

@Palaniyappan @lakshman

Thank you guys

1 Like

No Worries
Cheers @mz3bel

1 Like

@Palaniyappan

Not yet, still figuring out how… i did the select method but doesn’t work…

Here’s an example of my code and excel file :

extractedAlarms.xlsx (9.7 KB)

a.xaml (10.8 KB)

The thing is, that i’m trying seperate code1 from code 2 and date from time and store them in new columns as column code1, column code2, column date.

Also have to filtre the rows… as alerte1, alerte2 and alerte3 store them in a new document as document 1 contains alerte1, document 2 contains alerte 2 and document 3 contains alerte 3.

I used variables, because i need them for an other part of the code… so i used them as arguments

That’s the whole issue i have now.

For those, who does have the same issue as me or close to my issue. Here’s the solution :

Thanks to @Lahiru.Fernando

ExtractedAlarmSolution.xaml (16.2 KB)

2 Likes

Glad to hear that my support helped you to get the issue resolved… Great work!! :slight_smile:

2 Likes

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