Filter a dataTable

datatable
uiautomation

#1

First of all Hi everyone and you have mantained here a good forum that helped me a lot in the past.

My queation is: I have a excel file and I want to apply a filter to a column. The column name is “New Order” and I want it to only show the rows that have a “New Order > 0”… I’ve tryed many thing but none seems to work. Can somebody help? Thank you in advance!


#2

Hi @Renato_Quintal,

Try this,
dt.Select("[New Order]>0").CopyToDataTable

To store the Above code in another DataTable


#3

Hello @Poovarasan2! Thank you for your time and help. Where do I integrate that? What I want its to use the same DataTable that I read from the excel and apply that filter in that column. Is that possible?


#4

Hi there @Renato_Quintal
To utilise the above example, you will need another DT variable.

You can then assign the new DT to the filtered rows from the first.

Assign - dtMyNewDT = dtMyOriginalDT.Select("[New Order]>0").CopyToDataTable

The Select function allows you to return an array of DataRows and CopyToDataTable allows you to convert these back into a DT format.

You could then write these values into another spreadsheet.


#5

Hi @Mr_JDavey. Greatfull for you help. Till now I have this apagar

Now what is suppose to do to re-write the excel column?


#6

Hi @Renato_Quintal
Well, if you want to re-write the filtered rows into excel, you can simply use Excel Write Range.

This will, however, overwrite any existing data on the sheet, based on the starting Cell Reference.

It may be better to write the data into another sheet, so you can preserve the original.

Can I ask what is the end goal?

As in, what do you need to achieve by filtering the rows and writing them back?


#7

The end goal is to know whose CC has made new orders with a value higher than 0. SAP stuff… I’ve already filtered the table and re-write the filtered data below! Now maybe I’ll go by your advice and write a new file with the filtered data. One more question, can I make the headers of the new file equal to the headers of the original file? @Mr_JDavey


#8

@Renato_Quintal,
Why can’t you use the same DataTable after filtering the data…??


#9

Hi there @Renato_Quintal,
Certainly, when you perform Excel Read/Write activities, there is an option for Add Headers.


#10

Yes but I’m talking about the same cell format. Eg: If the headers of the original file are GREY brackgrounded so the headers of the new file must be equal. Is there a way to do that? @Mr_JDavey


#11

Hello Poo,

Can you pls tell me what wrong i am doing .I am not able to figure it out .
Your query worked for int32 columns.

But when i try to put the filter for firstname = Albert i am getting the following errors
rd_rnge.Select("[firstname]"=Albert].CopyToDataTable - i get syntax error for this
rd_rnge.Select("[firstname]=Albert").CopyToDataTable - I get invalid column name error for this

Thanks
Vishnu


#12

@vishnu_vardhan rd_rnge.Select(“firstname=‘Albert’”) Try this once