Ask Your Question

how to loop through csv file and delete rows that contain X

asked 2016-08-24 18:06:35 +0200

AliZ gravatar image

Hello. I am new to working with spreadsheets and looking for a way to clean up some data. I have a large spreadsheet of over 5000 rows and I would like to delete all rows that begin with the letter X in column N. I have beginner experience with JavaScript, so I am thinking about writing an if statement that would loop through column N and then delete based on the criteria. In LibreOffice, would this best be approached through creating a macro, AutoFilter, or is there a better option? Thanks for any advice!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-08-24 19:39:31 +0200

Lupp gravatar image

Actual deletion of rows requires one of the following proceedings.

(1. ) User interaction: Filtering for the rows to delete, deletion, removing of the filter
(2.) Write and rund user code for the purpose.

Concerning (1.) Select all the rows.
Use the 'satandard' filter with Column N BeginsWith X.
Delete the still visible rows.
Show all rows.
See also attached demo.

Concerning (2.)
Not so advisable

The original data should be preserved in an extra sheet.
Doing things based on formulae automatically working with freshly entered data is (in principle) also feasible.ask75752DeleteRowsByFiltering001.ods

edit flag offensive delete link more


Thank you so much! I will not waste my time on writing the code then! I think I was overthinking it.

AliZ gravatar imageAliZ ( 2016-08-24 20:41:47 +0200 )edit

Your solution worked great when I tried it yesterday, but is not working today on a different spreadsheet. I'm able to filter out the rows that begin with X, then I delete them. At this point I then choose "Reset Filter" to show the remaining entries that begin with something other than X , but only a very small subset appear. Do you have any ideas? I'm pulling my hair out over here!

AliZ gravatar imageAliZ ( 2016-08-26 22:09:19 +0200 )edit

I do not get the option 'Reset filter' after the deletion of the X-rows. As I described, I use 'Show' for all rows to get access again to the rows hidden by the filter..I do not actually understand your situation.
However, there is the way described by @w_whalley. It is preferrable anyway with some respect.

Lupp gravatar imageLupp ( 2016-08-26 22:57:16 +0200 )edit

Figured out what the problem was. The spreadsheet had to be selected both when applying filters and resetting filters. Wow. I feel silly.

AliZ gravatar imageAliZ ( 2016-08-26 23:05:16 +0200 )edit

answered 2016-08-24 22:25:49 +0200

w_whalley gravatar image

Sort the table on column N. This will group all the cells where N starts with X. You can then select and delete them. If you want to preserve the order of the other cells, insert a column of sequential numbers before doing the first sort. After the deletion, sort on the sequential number column to restore the original order.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-08-24 18:06:35 +0200

Seen: 767 times

Last updated: Aug 24 '16