1

I’m no Excel guru so please bare with me.

My client works in the financial sector so he works with huge Excel files. On a daily bases my client needs to save a sheet to CSV that then gets imported to another application.

The sheet consists of numbers, 3 columns and about 500 lines. My client saves the sheet to csv, and yes we tested with various options provided, then opens the csv in notepad, scrolls to the bottom of the file and finds that there is an additional blank line after the last row of data. I’ve tested this on 3 different systems and was able to recreate the issue.

Is this an issue within Excel or is there a setting I’m missing? I should mention that my tests were all done on Windows 10 Pro systems with either Office 365 Pro or Office Home and Business 2013. Excel 2010 produced the same result.

Your assistance is appreciated.

7 Spice ups
2

Most likely Excel thinks that there is something in that row when it’s exported. To be sure open the CSV in Excel, hit, Ctrl+End to go to the last row. If this after the dataset the highlight all rows between the last row of the dataset and where CTRL+End lands you and delete the rows then re-save and try again.

1 Spice up
3

Does the import process ignore the last blank line in the CSV you are importing or does it import the blank line?

4

@SullyTech

I tried that, no matter what I do the addition line right at the end remains.

@ Rockn

Importing the csv back into Excel ignores the blank line, importing the csv file into the other application fails due to the blank line.

5

divanmohr just for sake of ruling out the original file, can you try creating a new workbook and pasting the information into it, then exporting as a CSV. If that doesn’t work, rule out Excel and try the same process using LibreOffice as a test?

@divanmohr

6

That was actually one of the first things we tried. Will give LibreOffice a try.

1 Spice up
7

What is the export process? Is is a macro or a simple save as?

8

Simple Save As.

LibreOffice gives the same result. Could it be a setting within Windows?

9

Are all of your rows the same length? Multiline?

10

Yes, so what I’ve done as a test is to create a spreadsheet, 100 lines, 3 columns, column A contains Monday - Sunday, column B is the date, column C is a figure around 6 characters long, though this can vary.

Results stay the same.

11

Also does the source Excel file contain a shed load of formatting? You could try stripping all that out seeing as its going to CSV anyway. that might cause it.

12

One of the tests we did was to select all the data and remove all the formatting, at this point the cells are all test to text and there is no formulas in any of the cells.

13

Sounds like you are going through all the same sorts of things I would already check? Any hidden formulas or sums at the bottom?

14

None at all, the sheet is a plain and boring as I could possibly get it. I’ve gone as far back as Office 2010 and the result is the same, maybe it’s an Excel thing, best way forward would be to just manual edit the csv file.

15

Last thing I can think of is creating a VB macro to save the data out deleting any blank rows, something similar to:

 DeleteHeadings
Rows("1:1").Select
Selection.DeleteShift:=xlUp

'Find thelast Row with data in a Column
DimLastRow As Long
DimFirstRow As Integer

With ActiveSheet
LastRow =.Cells(.Rows.Count, "A").End(xlUp).Row
End With

FirstRow= 1

With ActiveSheet
    DataLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    SheetLastRow = .Cells.SpecialCells(xlLastCell).Row
End With

Rows(DataLastRow + 1 & ":" & SheetLastRow).Delete

' SaveActive Workbook As CSV
Application.DisplayAlerts= False
ActiveWorkbook.SaveAsFilename:="C:\File_Path", FileFormat:=xlCSV,CreateBackup:=False
ActiveWorkbook.CloseSaveChanges:=True
Application.DisplayAlerts= True
16

Was hoping it wouldn’t come to that, will have to discuss with my client.

Thanks for all the input so far.

17

This really sounds like you save the data as the wrong type of CSV file. Open excel and look at your save as option types. You will notice there are multiple CSV file types listed. You most likely want the one labeled CSV MS-DOS. Using the others may be adding special characters, carriage returns etc that you would not see without a good editor like Ultraedit. Just remember even if something has the same extension it can be slightly different. I learned this lesson the hard way with files that were Unicode masquerading as .txt files. It’s reasons like this you never ever use wordpad to open a data file, only notepad.
Capture.png