Cells with commas have quotes when exporting as CSV

[rcarmichael]

Good afternoon all,
I currently have a sheet set up which I am trying to export as a CSV. It is currently delimited using semicolons ( and appears formatted exactly as is required.
Some of my cells have a comma (,) in them. These lines become enclosed in quotation marks (") when exported to any type of CSV or any type of TXT Output.
Is there anyway to stop this from occurring?
Any assistance is greatly appreciated.
Sincere regards,
Ryan

 

[rlv01]

Enclosing text in quotation marks is Excel's way of differentiating between commas used as field separators, and commas that are part of the cell text. It is a good thing. Without the quotes, the csv file cannot be correctly reopened by Excel.

 

[rcarmichael]

Enclosing text in quotation marks is Excel's way of differentiating between commas used as field separators, and commas that are part of the cell text. It is a good thing. Without the quotes, the csv file cannot be correctly reopened by Excel.

Thanks for your reply @rlv01 . I know the reason why this is done, and agree in most cases it is beneficial. But as this particular CSV is semicolon separated (and changing the locale is not an option) I was hoping there was another way.
Currently I am copying the data manually from the completed excel sheet into a blank CSV. Any ideas on how to do this better?
Sincere regards,
Ryan

 

[rlv01]

Any ideas on how to do this better?

A number of ways:
1. Choose a unique replacement character for the comma, one not used elsewhere in your file and globally replace all commas. Export the file, use Notepad or Notepad++ to open the file and use search/replace to restore all the commas. Save the file.

2. Code your own VBA routine to create your own semicolon delimited csv file export function that does not add the quote chars.

3. See if the software below will meet your needs. Free or paid version.

Home - Rons Data Edit

Rons Data Edit is a new Modern CSV editor designed to edit CSV files (or any tabular text format) quickly and easily with numerous editing options, a clean and user-friendly interface, and the power and speed making it easy to handle large files.

www.ronsplace.eu

If I were doing it, my choice would be #1 if I was only doing it once. If I expected to have to do it all the time, then #2 - or #3 if I was not comfortable with the VBA coding.

 

[Alex Blakenburg]

I also think a macro is your best option.
It sounds to me like you have already concatenated the string you want to output into a single column.
If that is the case there are a couple of other option but they probably both finish up with a file with a prn extension.
If your target system doesn't like that then you still have to manually change the extension or still need a macro to do that.

The additional options if you have a single column output are:
• Export or Save As > Formatted Text Space Delimited (*.prn)
this won't but quotes around it.
• Ask your IT department to set up a Generic / Text Printer queue
I am not sure if they can change file extension default from prn to txt or csv