1

I have an Excel file with blank lines at the start. When I save as a CSV file from within Excel, the blank lines are being removed from the saved CSV file, but the Excel view of the file, i.e. filename.csv, still showed the blank lines present. I only discovered this by re-opening the saved csv file in Excel or a text editor.

Why does Excel remove blank rows when saving as CSV? And is there a way to avoid this?

Improve this question
3
  • Different versions of Excel behave differently; please include the version you're using. Also, how do you know the lines are being removed? That is, what are you using to open the CSV file after export? You seem to say that if you open the exported CSV with Excel or a text editor, you do see the blank lines. So what are you using that leads you to nonetheless state that Excel is removing the blank rows?
    – Debra
    Commented Jan 14, 2019 at 18:56
  • I am using Microsoft Office Home & Business 2010. However, it's a more general question as I am writing a website that is instructing users to create a csv file for me to import. I need to understand the various issues that might arise when they convert an excel file (for example) to csv and this was a surprise. Re: seeing the blank lines: when I've excel open and I save as csv, the filename renames to <name>.csv however from within excel it looks the same. Only if I close Excel & re-open the .csv (in Excel) I see the blank lines. I then confirmed this using a text editor (e.g. Sublime Text).
    – DeeBee
    Commented Jan 15, 2019 at 21:49
  • When I export as CSV with blank lines, if the lines are at the start of the file, they are not saved. If they are anywhere else (even just the 2nd line, right below the field defs) they are created in the CSV, with a series of commas. I think you might be re-saving the file after you export to CSV, and that's why you see the top-blank lines in Excel and not in a text editor. Try the export (save) to CSV and then close the file without saving again in Excel. Now open it & the top-blank lines should not be present. At least, that's what happens in Excel 2016 (I don't have 2010 to test.)
    – Debra
    Commented Jan 15, 2019 at 22:11

1 Answer 1

Reset to default
1

CSV

Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.

Depending on your data an empty row would have different formats (depending on the number of columns). In addition an empty row would be an empty record which is useless as it contains no information. As such it gets removed from the CSV.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .