Excel: How to add double quotes to strings only in CSV file

Question

I want to create a CSV file from Excel in which string values should be in double quotes and date values should be in MM/dd/yyyy format. All the numeric and Boolean values should be without quotes.

How would I go about this?

Answer 1

The default csv file format is to use commas between each value, quotes to contain each cell (In case your cell contains a comma) and line breaks for the next row e.g.

"A1","B1"
"A2","B2"

This is only from me toying with open office, which may vary a little from excel. But try it and see.

Answer 2

It's easier to use VBA to do this. The SaveAs method of the Workbook object only lets you choose pre-defined formats and the xlCSV one does not delimit strings with double-quotes.

To do this in VBA:

Dim fileOut As Integer

fileOut = FreeFile
Open "C:\foo.csv" For Output As #fileOut

Write #fileOut, 14, "Stack Overflow", Date, True

Close #fileOut

(NB Date is a VBA statement that returns the current system date as a Variant of sub-type Date)

If you then examine the file in Notepad:

14,"Stack Overflow",#2009-05-12#,#TRUE#

The string has been delimited as required, the date converted to universal format and both the date and boolean are delimited with # signs.

To read the data back in use the Input # statement which will interpret all of the values appropriately.

If you want to write part of a line and then finish writing it later then:

Write #fileOut, 14, "Stack Overflow";
Write #fileOut, Date, True

produces the same result as the original program. The semi-colon at the end of the first statement prevents a new line being started

Strings with embedded double-quotes will cause problems so you'll need to remove or replace those characters

Answer 3

It's kind of scary that Excel doesn't let you specify formats. Here's a MrExcel link that might prove useful to you as well.

http://www.mrexcel.com/forum/showthread.php?t=320531