Remove empty rows from CSV

Recently I had to remove empty rows from a Magento ImportExport product export CSV file (see The format of these files is really complex.
The problem was, that I did a cleanup of the product data inside the file. As a result of this there where empty rows.
So I had to eliminate them. I had to find a way to detect empty rows and to delete them afterwards.

These where the steps I’ve taken to remove empty rows from the CSV file:

  1. Open the file in LibreOffice (the hardest step! :-D)
  2. Add an additional column to the end of file called DELETE
  3. Add the formula “=COUNTIF(A3:Q3, “”)” to the first cell in the new column.
    This formula checks all cells before the DELETE cell of the row and counts the empty ones.
    I had 17 used rows so in empty lines the DELETE cell will have the value 17.
    (if you have also line with “-” f.e. you can use an OR comparison as well => “=OR( COUNTIF(A3:Q3,”-“), COUNTIF(A3:Q3,””))“)
  4. Copy the formula to all cells of the DELETE column
  5. Mark the whole sheet (Ctrl+A)
  6. Go to Data->More Filters->Standard Filter
  7. Add a >= condition for DELETE column like that :
    Remove empty rows
    Make sure that “Range contains column labels” is checked unter Options. Then hit OK. Afterwards only the empty rows are visible and selected.
  8. Now deselect the first row, that contains the labels (Click on the row number it with Ctrl key pressed)
  9. Deleted the other still selected rows (Remove empty rows button button)
  10. Click Data->More Filters->Reset Filter

That’s how to remove empty rows from a Magento ImportExport product export CSV file.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.