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

[mannyghee]

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:

[TABLE="width: 636"]
<tbody>[TR]
[TD="width: 72, bgcolor: transparent"]H
[/TD]
[TD="width: 235, bgcolor: transparent"]UNASSIGNED
[/TD]
[TD="width: 72, bgcolor: transparent"]Direct
[/TD]
[TD="width: 72, bgcolor: transparent"]0
[/TD]
[TD="width: 72, bgcolor: transparent"]BOTH
[/TD]
[TD="width: 72, bgcolor: transparent"]BOTH
[/TD]
[TD="width: 109, bgcolor: transparent"]DISPATCH #2
[/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]H
[/TD]
[TD="bgcolor: transparent"]NO READ
[/TD]
[TD="bgcolor: transparent"]Direct
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]BOTH
[/TD]
[TD="bgcolor: transparent"]BOTH
[/TD]
[TD="bgcolor: transparent"]DISPATCH #2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]H
[/TD]
[TD="bgcolor: transparent"]STD Super Sort Letters to LCTS 1
[/TD]
[TD="bgcolor: transparent"]Direct
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]TRAY
[/TD]
[TD="bgcolor: transparent"]BOTH
[/TD]
[TD="bgcolor: transparent"]LCTS #1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]780
[/TD]
[TD="bgcolor: transparent"]780
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]9
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]780
[/TD]
[TD="bgcolor: transparent"]780
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]9
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent"]18900
[/TD]
[TD="bgcolor: transparent"]18900
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]9
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent"]18900
[/TD]
[TD="bgcolor: transparent"]18900
[/TD]
[TD="bgcolor: transparent"]171
[/TD]
[TD="bgcolor: transparent"]171
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]9
[/TD]
[TD="bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"]9
[/TD]
[/TR]
</tbody>[/TABLE]




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

 

[Paul Ked]

See here https://www.mrexcel.com/forum/excel...sv-output-remove-xtra-commas.html#post2031840

 

[joeu2004]

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]

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.

 

[joeu2004]

Excel will always add the same amount of commas to all rows to match the largest row

You're right. My bad!