Excel Macro: Proper CSV Export

A proper CSV file wraps every entry in double quotes, and separates the entries using a comma character.

i.e.: "c1","c2","c3","entry, with a comma"

Now this is not a 100% requirement because the only purpose to wrapping an entry in double quotes, is whenever there is a comma within the entry itself. The purpose of the double quotes is to state where the entry starts and ends, and to say "this comma belongs in the entry, not as a splitting point".

More interpreters do not require a 100% properly formatted CSV, because when there is no comma in the entry, there is typically no need to wrap the entry in double quotes. So this is perfectly fine for newer systems:

c1,c2,c3,"entry, with a comma"

Unfortunately, older systems still require every entry to be wrapped in double quotes. A proper CSV wraps all entries, regardless of commas, in double quotes to signify that "yes, this entry is this long and contains this data"

Excel fails at this, it will export the CSV using the second example above, which then fails to load properly in some older software systems.

The script below, provided by Microsoft, will format the data as a proper CSV.

  1. Turn on the Developer Tab
  2. Go to the Developer Tab, click on Macros.
  3. Macro Name: QuoteCommaExport -> Create
  4. In the new window that appears you should see two lines:
Sub QuoteCommaExport()

End Sub
  1. Copy and paste the script below, overwriting those two lines.
  2. Save and close the current window.
  3. Back within the excel document, highlight the entire range of data you wish to export
  4. Go to the Developer Tab -> Macros -> Select: QuoteCommaExport -> Run
  5. Enter in a location to save the document and OK

Check the location you choose to save it as, the document should now be correctly formatted and will work in older systems.

 

Print