Dealing with double quotes in a CSV format

[markshnier]

I have data that uses the inch abbreviation, such a ceramic tile that is 6" by 8" in dimension. I have a native .xls speadsheet and need to create a CSV file for exaport to another system.

When I do a "Save As" in CSV format form a .xls file containing

6"

it gets saved in the CSV file as

"6"""

Is there a way to keep the data in the original format without Excel adding in extra double quotes.

It would need to NOT involve VBA scripts as that is way over my head. I am looking for a solution using just Excel forulas or some kind of find and replace.

thx in advance. I have searched through this forum looking for previous posts, but I don't see an answer.

Mark

 

[GlennUK]

Are you sure the system you are exporting to will accept data with a single double-quote embedded in it?

 

[markshnier]

Yes, the data is going into an AS/400 type mainframe (OK, it is now called i-series), but everyone still calls it an AS/400. It has no problem with quotes.
Mark

 

[GlennUK]

The trouble is CSV files follow certain rules ... which are causing your problems. What about removing the double quotes altogether before saving?

 

[bandit_1981]

Use a holder character for them and then when you read it in put it back.
IE " = ^ then when you find a ^ when reading it in replace it with a "

 

[markshnier]

I think that I will just do a Find & Replace annd get rid of the quotes or change them to a single quote.
thx for your suggestions
Mark

 

[Fat Cat]

Have you looked at using SAVE AS and setting the file type to FORMATTED TEXT (Space delimited) (*.prn)

This will keep 6" looking like 6" without the double quotes

If you want comma separated values across a single line in the file, you would need to have a cell containing a comma between each cell containing data on the spreadsheet.