Delete rows from Excel then write to same sheet

Help Studio
, ,
1

I have a massive excel file that I need to delete certain rows from. all rows with Fiscal year=x I need deleted.

Currently I am reading the sheet, filtering with remove fiscal year=x then writing back to the same sheet. however, since the original dt is bigger than the filtered dt some of the original dt is not overwritten and remains in the sheet. How do I get around this? I need ONLY the filtered data written to the same sheet that I am reading the OG data from.

I preferably need to do this as a background process.

1 Like
2

Hey @kasey.betts

Just try deleting the range & then perform write in it.

Thanks
#nK

3

Hi,
Please check the steps. Hope this will help you.

step1: use read range and store them in dt1
step2: create a dt2 by just cloning dt1
step3: use a linq query to filter values
dt2=dt1.AsEnumerable().Where(Function(x) Not Convert.ToString(x(“FiscalYear”)).Equals(“x”)).CopyToDataTable
Step4: use write range for writing data from dt2 in same sheet

4

How do I delete the sheet with UiPath?

1 Like
5

That KEEPS one Fiscal Year. I do not which data will need to be kept. I just know what needs to be deleted. all other data that isn’t on the list be deleted needs to be kept

6

hi,
then you can modify the query as shown below

dt1.AsEnumerable().Where(Function(x) Not Convert.ToString(x(“FiscalYear”)).Equals(“x”)).CopyToDataTable

7

Hey @kasey.betts

Sorry its not sheet, but the range.

nmnithinkrishna_DelRangeFromXL.zip (8.2 KB)

Above is the sample demo.

Thanks
#nK

8

Thanks. That filters the data correctly but still the same issue with writing. I think I need to clear the sheet before writing like Nithin suggested

9

@kasey.betts Deleting data is not required, but use write range it will print the data from dt2 only…
if you use append range, it adds to the existing data in the sheet
if you use write range, it will replace the data in the sheet

10

Thank you! That works! I wasn’t sure how to get it to delete the entire sheet and not just a certain cell range. Thanks for your help!

1 Like
11

How would I do it for an array of values? Like if I had an array of fiscal years {2021,2020} and wanted to filter out all the data from years included in the array?

1 Like
12

Here you go…

dt1.AsEnumerable().Where(Function(x) YearArray.Contains(Convert.ToString(x(“FiscalYear”)))).CopyToDataTable

Hope this helps

Thanks
#nK

13

@kasey.betts the one which provided by @Nithinkrishna works pretty well. since you don’t want the values matches fiscal year, place a “not” in the query.

dt1.AsEnumerable().Where(Function(x) Not YearArray.Contains(Convert.ToString(x(“FiscalYear”)))).CopyToDataTable

Hope this helps

1 Like
14

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