Excel saving file as CSV with extra 2 commas at the end of the rows

mannyghee

New Member
Joined
Jun 16, 2011
Messages
11
I am working on a file with about 30K rows and has to be saved as a CSV file. When saved and opened up in NOTEPAD, the file has rows (starting with H) that have double commas (,,) at the end of the line while the others (start with D) are fine. The H rows are 2 columns less than the D rows. The added commas renders the file useless when imported into the application that needs it.

Below are screenshots of the Excel file and the CSV file opened in Notepad:

H
UNASSIGNED
Direct
0
BOTH
BOTH
DISPATCH #2
H
NO READ
Direct
0
BOTH
BOTH
DISPATCH #2
H
STD Super Sort Letters to LCTS 1
Direct
0
TRAY
BOTH
LCTS #1
D
1
1
780
780
0
9
0
9
D
2
2
780
780
0
9
0
9
D
18900
18900
0
0
0
9
0
9
D
18900
18900
171
171
0
9
0
9

<tbody>
</tbody>




H,UNASSIGNED,Direct,0,BOTH,BOTH,DISPATCH #2 ,,
H,NO READ,Direct,0,BOTH,BOTH,DISPATCH #2 ,,
H,STD Super Sort Letters to LCTS 1,Direct,0,TRAY,BOTH,LCTS #1 ,,
D,1,1,780,780,0,9,0,9
D,2,2,780,780,0,9,0,9
D,18900,18900,0,0,0,9,0,9
D,18900,18900,171,171,0,9,0,9
D,18900,18900,504,504,0,9,0,9
 
Last edited:

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
I am working on a file with about 30K rows and has to be saved as a CSV file. When saved and opened up in NOTEPAD, the file has rows (starting with H) that have double commas (,,) at the end of the line
I suspect that Excel is doing nothing wrong. The two commas probably represent cells that contain some version of the null string. You might not even see them if the cell values had been created by someone doing copy-and-paste-value.

To confirm, verify that =ISTEXT(H1) returns TRUE, and =LEN(H1) returns zero.

Interesting workaround: save as CSV; close the window (or exit Excel); open the CSV file; then save as CSV again.
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
Excel will always add the same amount of commas to all rows to match the largest row, I think that is the idea of a CSV file!

The workaround doesn't work around anything, there are still redundant commas stored in the CSV file.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,824
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top