Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Excel only places quotes around certain fields, how do I force excel to save a CSV file with quotes around every column?

share|improve this question

9 Answers 9

up vote 6 down vote accepted

Seems that even now, Excel has problems saving a proper CSV file. An old ExcelForum.com post lists a number of macros that do the trick.

share|improve this answer

This page also has the solution which comes straight from the horse's mouth:

http://support.microsoft.com/kb/291296/en-us

If the link degrades, the topic to search for is:

"Procedure to export a text file with both comma and quote delimiters in Excel" and/or "Q291296"

tl;dr: use their macro

share|improve this answer
6  
Something that may be worth noting is that macro is using Integer for row and column values. You'll get hit with an error if trying to export greater than 32768 rows. Changing the datatypes to Long, for example, will fix that problem. –  Dan May 21 '13 at 18:06
    
Good spot (+1). It looks like they haven't updated the code since it was first written for versions of Excel with only 32768 rows max. –  sahmeepee Oct 13 '13 at 13:57
4  
I took the liberty to create a gist with this macro, and a couple of fixes: gist.github.com/fabriceleal/7803969 –  fableal Dec 5 '13 at 11:43
    
@fableal Great snippet! It deserves to be in an answer. –  xiankai Dec 18 '13 at 1:35

If you open the XLS file in LibreOffice or OpenOffice, then Save As....and choose Text CSV, it alows generating a CSV file that also includes quotes as delimiters. E.g.: "Smith","Pete","Canada" "Jones","Mary","England"

Just check the "Quote all text cells" box: Export to CSV with LibreOffice

share|improve this answer
2  
Although not an answer to the specific question it is a simpler workaround than trying to get my clients to use a macro! –  Duncanmoo Nov 1 '13 at 8:35
    
I'm tired of using this method every day, that's why I'm here –  vladkras Mar 7 at 6:33

This was the easiest for me: I imported the spreadsheet into Access 2010 and exported it from there as a delimited text file. Gave me the quote marks around my fields. Takes less than a minute for 42k rows now that I have the steps down.

share|improve this answer

I have found another work around which doesn't involve the VBA Macro, but which does require Notepad++ or a similar macro-based text editor.

Export your file as Tab-seperated text, then open the exported file within Notepad++. Replace all instances of the 'tab' character with the text "," (ie literally double-quote, comma, double-quote) and then use another macro to prefix and suffix each line of text with a double-quote.

A bit hacky but I found it quicker than getting a VBA macro working.

share|improve this answer

For Windows users

  1. "Save as" Excel file as CSV
  2. Open saved file with Mircrosoft Works Spreadsheet
  3. "Save as" the spreadsheet as CSV
  4. All non-numeric field now have " around them

Note this is not the same keyboard quote which has a forward & backward variety.
So if using CSV to LOAD into Mysql table, cut and paste into ENCLOSED parameter otherwise you will wonder why you get message #1083 - Field separator argument is not what is expected

share|improve this answer
2  
Mircrosoft Works <-? Are you sure about that? –  nixda Oct 8 '13 at 17:39

I found this easy solution:

  1. High­light the cells you want to add the quotes.
  2. Right click and go to: For­mat Cells → Tab: Number → Category: Custom
  3. Paste the fol­low­ing into the Type field: "''"@"''" (see details below)
  4. Click “okay”

The string you are pasting is "''"@"''" which is double quote, single quote, single quote, double quote, @ symbol, double quote, single quote, single quote, double quote.

Edited for Excel 2010 from the information found here.

share|improve this answer

Michael's answer of Aug 21 2014 is really helpful. However, I had to perform an additional step or two:

  1. Open the .csv file with Notepad (or equivalent)
  2. Replace all '' (repeated single quotes) with " (double quote)
  3. Replace all ; (used as delimiter in German version of Excel) with ,
  4. Save revised .csv file
share|improve this answer

This is my recipe. I hate having to do this but I had no better way. In my case, I'm not getting quotes around any field. I also needed to use UTF-8 encoding and ; instead of tabs, so this is what I ended doing.

NOTE: due to the ilegibility of putting double quotes enclosed by single quotes and so on, I've used purposely "keyboard formatting" to indicate both keystrokes and literal characters and strings.

  1. Insert a new column before any other in your Excel spreadsheet (click on the title of the 1st column, the entire column gets selected, right click, select "insert").
  2. Select the entire range of empty cells from the new column (they are selected by default). On the formula field, insert whatever you want: for instance, xxx. Press CTRL+enter to fill the entire column with the same value.
  3. Save the file as Unicode Text (*.txt).
  4. Open file in Notepad++.
  5. Press CTRL+f to open Search/Replace.
  6. Go to Replace tab.
  7. In "Search mode" select "Extended (\n, \r, \t, \0, \x...)".
  8. In the "Search" field, enter xxx plus a tab keystroke.
  9. In the "Replace" field, enter " (a double quote).
  10. Press Replace All.
  11. In the "Search" field, put a tab keystroke.
  12. In the "Replace" one, enter ";" (double quote, semicolon, double quote).
  13. Press Replace All.
  14. You are almost done. For the last double quote, enter \r in the "Search" field and "\r" (double quote, backslash, double quote) in the "Replace" one. Press Replace All.
  15. As long as you are not using chinese character, etc., you can change encoding to UTF-8 and save the file safely.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.