Remove rows from Excel sheet

Help
, , ,
1

I’ve looked at similar posts regarding this but I’m still struggling to get it to work. I ultimately want to remove all rows from my “RawData” Excel worksheet where Month != “Dec”. I’m using the following flow (Read Range, Filter Data Table, Write Range). When I execute it only opens my Excel file and then closes again without removing all rows except ones where month = Dec. Can someone please help!

image

2

Properties of Read Range:
image

3

@dumwalke

Try below select Query.

             newDT = inputDT.Select("Month NOT LIKE 'Dec%'").CopyToDataTable

And then use Write Range Activity and pass newDT to it.

1 Like
4

So I created a new variable like this:

image
image1271×33 2.46 KB

5

And my write range looks like this:
image

But it still didn’t work. What am I missing?

6

Hi
welcome to uipath community
if possible can i have a view on the FILTER DATATABLE WIZARD with a screenshot
make sure that you have used like this
COLUMN NAME MUST BE IN DOUBLE QUOTES

image

Cheers @dumwalke

1 Like
7

Thank you for helping! Here is a screen shot of my Filter Data Table Wizard:
image

8

And here is a screenshot of my Write Range properties:
image

1 Like
9

Fine
can i have a screenshot of your excel column with that value Dec
and one more thing
you want to keep the records or remove the records which are not having Dec in the column Month
image

Cheers @dumwalke

10

Sure. Here is a screenshot of the column. I want to Remove all rows where the Month is not equal to “Dec”.

image
image120×678 2.33 KB

11

If your ultimate goal is to have the excel file filter, it would be easier to do it with excel macro, but if you wil work with that data inside uipath, then you will have trouble to use a DateTime column as it was a string to filter…

12

Ah, so it’s getting hung up on the fact that it’s a DateTime column? So a Filter Data Table has no way of accepting a Date field as a valid value?

13

yep… there are know problems with stardard filtering when excel is datatime… it wont work using text like you see when your excel is opened, like using the month like “Dec” as if it was a text…

14

Is there a way to assign a variable a datetime.now.tostring value and then pass that as the value in the Filter Data Table condition? If I click on a cell with the Excel file for the Month column, the date is actually formatted as “12/1/2019”. so could I assign a variable that returns only the current month in this format? So in this case “12/1/2019” is what I would want to keep and all others removed?

15

if this fits your need test the standard filter with something like this:
“month” < new dattetime(DateTime.Now.Year,DateTime.Now.Month,1)

16

I updated the Filter Table Data as recommended here:

image
image717×472 9.76 KB

This only appeared to reorder the sheet putting December values at the top. It didn’t remove all other months. I am also still using the Read Range before and Write Range after.

Any other thoughts on how I can remove the other rows that aren’t December?

17

Actually, I have another update. Originally I was trying to write the results back to the same worksheet “RawData”. I tested by writing to a new sheet instead and it appears that it writes only the December values which is what I want. However, is there a way to write it back to the same Excel table within the same sheet “RawData”?

18

the way to do that is to wipe the original range before writing it back…

19

Great, do you know what value I should put in the “Range” value if I want to delete the entire range regardless of the size? I tried using “” but it wouldn’t accept that.

20

Start by using Get Table Range, use that out put to both Read Range and Delete Range.