Filter Data Table Activity

[ecarles (관)]

Hello. I am using the filter data table activity, to filter a DataTable extracted from a DataScraping done in a website. The thing is that I need to filter the rows depending on some values. What I found, is that when it comes to putting a condition of an Integer, it won’t do the filtering, whereas if I only put the String conditions, it works.

Question:

Is it because the DTScraping activity only gets the Integers as Strings? If so, how can I solve that? I have tried doing an Assign and converting it to integer but nothing worked.

Any help would be really appreciated. Thanks!

[Manjuts90]

@ecarles Check the below link, it might be useful in ur case.

Deleting rows from a sheet after filtering

Thanks @MAHESH1 ! Got 2 queries regarding filtering a data table and writing filtered data into new sheet as you mentioned before. Suppose if the filtered data table contains no rows, I don’t want the new sheet to be created then. How do I ensure that the bot moves to the next item and does not stop ? How do I use a “less than ( < )” in a filter ? Say for e.g. in the code - dt1=(From p in dta.Select() where Convert.ToStringf(p.Item(“Column C”)).Equals(“Value”) Select p).ToArray.Copy…

[ecarles (관)]

It’s not exactly what I’m looking for, but thanks!

[PrankurJoshi]

Hi @ecarles

See these, might help you

Issue with filter datatable

Hi guys, I want to filter a data table based on a value of a column, I wrote: “ColumntName”<“10” but id didn^t work. Any Idea how to deal with Integers in this filter? Thanks in advnace

SELECT with string to integer/decimal PARSE is not working

This issue is somewhat covered in some earlier topics, but I still fail to make this work. I have extracted numerical data from web in string format (“123,45”) into a data table. I am using select and parse to filter rows based on the numerical value of the column “c2”, but fail to get it right. tableName.Select(“int16.Parse([c2], NumberStyles.AllowDecimalPoint) <200”).CopyToDataTable() is not working. I have tried different version of using ’ and " in SELECT but w/o success. Can anyone he…

Thanks,
Prankur

[ecarles (관)]

Hey, many thanks!

As per the first post you’ve linked, where do I write the following statement?

dataTable1.select(“Convert([WIID],system.int32)<10 AND [Status]=‘Open’”)

It’s actually what I need. Do I write in an assign inside a for each row?

[Manjuts90]

@ecarles That statement has to be written outside for each row

[ecarles (관)]

alright! Tried it with an assign activity just before the filter data table activity

aDataTable = aDataTable.Select(“Convert([WIID],system.Int32)”).CopyToDataTable

and it gives the following error

Any idea of what to do? Should I type the filtering condition there too?

Edit:

Wrote the condition too aDataTable.Select(“Convert([WIID],system.Int32)<200000”).CopyToDataTable

and it gives the same error as before

[PrankurJoshi]

Try this

DataTable.Select(“(cint[WIID])<200000”).CopyToDataTable

[ecarles (관)]

@PrankurJoshi

(“(cint[WIID])<200000”).CopyToDataTable

Tried this, gives me the following error

[Silviu (Silviu Predan)]

Hi @ecarles,

In the Filter Data Table activity, try to use a float value as 200000.0 instead on the integer value (200000) for the filtering value. It’s a known issue and a fix will be released soon.

For the DataTable.Select method, to convert to a different data type you can use something like “Convert(WIID, ‘System.Int32’)”, check this article for more details.

Best regards
Silviu

[Manjuts90]

@ecarles

@ecarles

aDataTable.Select(“Convert([WIID],system.Int32)<200000”).CopyToDataTable

aDataTable.Select(“Convert([WIID],System.Int32)<200000”).CopyToDataTable

Instead System.Int32 you used system.Int32 that’s y u getting that error (you Used lowercase letter for System instead of UpperCase)

[ecarles (관)]

Hello @Silviu

I have tried the float value thing and still nothing happens to my excel.

I know it’s this the issue because if I take the WIID condition then the filtering works properly and pastes it to my Excel file.

Here is a capture of what I’m talking about

Any idea? Many thanks for your response!

[ecarles (관)]

Tried it and it doesn’t give me an error but then the Output is not pasted in the excel

Thanks for your help though

[shah330 (Fenzil Shah)]

Hi all,

Im having difficulty trying to use “Filter Data Table” to filter a datatable without headers. The source is TXT so using Read TXT to generate a string variable then using “Generate Data Table” from the string var. The output is always blank. How does one filter using column index?
Sample of 1 line is below :
64,140131,0000048900,00000001209807,0001,0000,0000,0000,26,0,0,1049,1

Im not able to attach xaml as im new user.

Regards,
Shah

[RPANovice]

Hi, I have been studying the same topic lately.

I just tried the following with Convert with ’ -marks around System.Int32 and it worked.

aDataTable.Select(“Convert([WIID],‘System.Int32’)<200000”).CopyToDataTable

More on this topic at https://docs.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=netframework-4.7.2 (See Convert- examples on this page).

Also “SELECT” does not support CInt() -function, but it supports Convert and some aggregate functions. See DataView RowFilter Syntax [C#] for more information.

[ecarles (관)]

This worked!!! Many thanks dude! Forgot to mention that after putting this “condition” Assign, then you don’t have to put it again in the filter data table activity