Hi, I have an automation that extracts table data to Excel and I need to delete all rows that have a value of 0 in column named Balance. It half works but only every 2nd row meeting the criteria is deleted.
The At position field won’t accept anything other than 1.
What am I doing wrong?
When you delete a row, it moves all other rows up by one row. This means for each row deleted, your iteration will also end up skipping one row.
Rather than deleting rows during your iteration, could you simply make a list of the row numbers you want to delete then use one Delete Rows activity after your iteration, and provide the list of rows you want to delete in one go. (for example: “2, 4, 7, 12”)
Thanks @moosh, that makes sense about the iterations skipping a row.
The numbers of rows varies each time the data is extracted so it isn’t possible to delete specific rows in your example.
I have found a solution although it’s a bit sloppy. I added a Repeat activity and moved the existing For Each Excel Row inside it. Sloppy, but it works.
You wouldn’t hardcode the row numbers like my example, you would determine those row numbers during your iteration (exactly as you do now) but store them in a variable.
In the most basic form, you would replace your Delete Rows activity with an Assign activity where you can append the row number that needs to be deleted to the same variable.
Then after your loop, you would have a variable containing all the row numbers that you can then feed to a single Delete Rows activity.
Something like this
Hi,
There is simple technique in Studiox to achieve your requirement. Please follow the below steps.
Use excel file to choose your excel.
Inside Use excel activity. Use filter activity to select your column name in our case balance and assign basic filter like is equal to “0”
Now use the delete rows activity and select the option Delete all visible rows.
now it is have ability to delete all rows with zero in the balance column at single shot.
Use filter activity to again but this time only select clear any existing filter check box to see our final output in the excel.
please refer the below screenshot and also i am attaching the sample work flow. take a look and try and let us know. thanks.
DeleteRowsDemo.zip (59.6 KB)
Your solution worked perfectly, thank you very much.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu