How To Reduce Commas When Exporting To .CSV

[Darkspire]

I have Excel 2007.

When I export a spreadsheet to a .CSV file I always end up with a large amount of unwanted commas at the end of each line.

Is there a way to mark a cell as null? Or mark a cell as a line end?

So that when exporting all the empty / unused spaces on any given line are not converted into commas?

for example:

Fred 1 2 3 4 5 6 7 8 9 10

Bert 1 2 3 4

When exported would look like:

Fred,1,2,3,4,5,6,7,8,9,10
Bert,1,2,3,4,,,,,,,

If I could signal / mark the fifth cell on the Bert line as line end then I would not end up with all the unwanted commas.

Thanks

Darkspire

[Ron Coderre]

Not to be glib...but...why do you care about the additional commas?
Any program reading the CSV file will simply see them as empty cells. Also, those commas indicate to whoever is reviewing the file that row data was not erroneously truncated.

[Darkspire]

I am writing data tables in Excel for a game that uses text files.

One of the exported .CSV files is 367 lines, some of the lines have less data on and it can take up to 15-20 minutes to just go through the file and delete the extra commas every time I export to test, if not deleted the program will read the extra commas as data and throw an exception.

Darkspire

[TxCPA]

Try selecting the first column without data in it - holding SHIFT, select END and the RIGHT arrow - that should select all the columns. Then right-click and delete the "empty" columns. I think you just have some ghost data in those trailing cells which gets created as NULL when saving to CSV. Worth a shot.

[Ron Coderre]

I faced that situation quite a while ago.
Back then, I created a calculated CSV "field" in a single column to the right of the data area and copied the values to a separate sheet in the workbook. The formula built each CSV row according to the spec required for the receiving program. Some rows had fewer fields than others. When done, I exported that calculated sheet to a CSV file.

That formula looked something like this:

Please Login or Register  to view this content.

Is that something you can work with?

[newdoverman]

Load the CSV file into NotePad then, use REPLACE and search for ,, and replace with nothing. This will get rid of all commas that are together without data between them.

This is only good if there are not empty cells within your data rows and the commas are at the end.

The good part is that all the commas that are together will be eliminated in one go for the entire document.

Save the edited document as a CSV file.