Recently I had to remove empty rows from a Magento ImportExport product export CSV file (see https://www.webguys.de/magento-1/turchen-19-produktimport-mit-der-importexport-schnittstelle). 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:
- Open the file in LibreOffice (the hardest step! :-D)
- Add an additional column to the end of file called DELETE
- 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,””))“)
- Copy the formula to all cells of the DELETE column
- Mark the whole sheet (Ctrl+A)
- Go to Data->More Filters->Standard Filter
- Add a >= condition for DELETE column like that :
Make sure that “Range contains column labels” is checked unter Options. Then hit OK. Afterwards only the empty rows are visible and selected.
- Now deselect the first row, that contains the labels (Click on the row number it with Ctrl key pressed)
- Deleted the other still selected rows ( button)
- Click Data->More Filters->Reset Filter
That’s how to remove empty rows from a Magento ImportExport product export CSV file.