124

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

20 Answers 20

50

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

5
  • 10
    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, 2013 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, 2013 at 13:57
  • 13
    I took the liberty to create a gist with this macro, and a couple of fixes: gist.github.com/fabriceleal/7803969
    – fableal
    Dec 5, 2013 at 11:43
  • Office 2010, still overflows on high number of selected rows, just as @sahmeepee mentioned. Aug 9, 2017 at 1:00
  • IF you use this macro be careful to highlight JUST what you want exported - mark an entire empty or sparse sheet you get a HUGE file with a lot of empty quotes. Like many thousands of times larger. Exports what you SEE so numbers in narrow columns get the 4,5e09format look Dec 7, 2023 at 18:33
49

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

In order to also quote numeric fields, highlight your cell range and change the cell formatting to "text" prior to saving.

7
  • 12
    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, 2013 at 8:35
  • I think this is the easiest and most convenient solution since OpenOffice is a portable software. You don't have to install anything or write any codes.
    – Scott Chu
    Apr 16, 2015 at 3:12
  • 43
    Not an answer at all. The question is explicitly about MS Excel
    – vladkras
    Jul 5, 2016 at 10:26
  • 1
    The question has nothing to do with Excel. It just so happens Excel isn't the only program which can interpret CSV Vladkras. However this answer is still invalid because it ONLY CONVERTS TEXT CELLS. If the cell is numeric, it won't be quoted. The question is about forcing quotes on EVERY column - not just those which resemble text to LibreOffice.
    – 1owk3y
    Mar 9, 2017 at 23:53
  • 2
    @1owk3y Just select all, Format > Cell > and select Text in Category box. This makes all values become text = everything will be exported with quotes.
    – Lluser
    Aug 5, 2021 at 11:11
31

Powershell appears to dump correctly. so something like

search for powershell.exe on windows machine if you dont know powershell.

import-csv C:\Temp\Myfile.csv | export-csv C:\Temp\Myfile_New.csv -NoTypeInformation -Encoding UTF8

Hope it helps someone.

3
  • 2
    For convenience, Shift + right-click in the folder where your CSV is saved and choose Open PowerShell window here, which simplifies the command to import-csv Myfile.csv | export-csv Myfile_quoted.csv -NoTypeInformation -Encoding UTF8. remember to use single-quotes around filenames with spaces or other special characters.
    – Walf
    Nov 1, 2018 at 23:45
  • Won't that fail in the case of a cell containing a comma?
    – mavhc
    Dec 7, 2021 at 15:22
  • @mavhc, if a cell contains a comma, it will already be quoted (at least from excel exports). If it's not quoted, it's likely not a valid CSV file. This process will put quotes around cells that don't have them and leave the cells that already have them. Jul 18, 2022 at 19:49
23

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.

5
  • Hmmm.. that gave me ''ITEM'' (two single quotes) rather than " (one double quote). I just then did a search and replace to fix it. Mar 18, 2016 at 22:28
  • 8
    The format should have been \”@\”
    – manat
    Nov 19, 2016 at 15:31
  • This answer was actually a perfect answer if you're using LibreOffice - it totally lacks a "text" option in the Format > Number Format menu, despite their documentation insisting the option is there. If you right-click "Format Cells" in LibreOffice, you are given a FULL list of cell interpretation options - rather than the somewhat useless 6 options presented in the dropdown.
    – 1owk3y
    Mar 9, 2017 at 23:56
  • @manat's answer, but without the curly quotes: \"@\"
    – MSC
    Feb 7, 2019 at 0:59
  • 1
    Not able to do a double quote in number field. Dec 27, 2019 at 0:28
7

Highlight the cells you want to add the quotes. Go to Format –> Cells –> Custom Copy/Paste the following into the Type field: \”@\” Click “okay” Be happy you didn’t do it all by hand.

From: http://www.lenashore.com/2012/04/how-to-add-quotes-to-your-cells-in-excel-automatically/

3
  • 3
    This work well for alpha - you will need \”#\” for columns with numeric values.
    – DropHit
    Sep 25, 2017 at 2:07
  • Between the \"@\" and \"#\" this works. But I still have to edit the headers in Notepad++ for the numeric columns, and then replace all """ with ". What a pain.
    – Gary
    Dec 8, 2017 at 15:51
  • Have not tried it.. but if you are creating a csv couldn't you just change the columns to text and use \"@\" Sep 26, 2019 at 18:00
5

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.

1
  • The only drawback would be if the field itself contains a double-quote. Aug 9, 2017 at 3:01
3

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.

1
  • 1
    If you wanted to do that in Notepad++, you would be best using regular expression search and replace and then you could do all 3 replace operations (start of line, tab and end of line) in one place
    – sahmeepee
    Mar 27, 2015 at 19:56
3
  1. Highlight the cells you want to add the quotes.
  2. Right click and go to: Format Cells → Tab: Number → Category: Custom
  3. Paste the following into the Type field: "''"@"''" (see details below)
  4. Click “okay”
  5. Open the .csv file with Notepad (or equivalent)
  6. Replace all ' ' (repeated single quotes) with " (double quote)
  7. Replace all ;
  8. Save revised .csv file
1

If you use Notepad++ to add quotes to the beginning of each line, just open the exported csv file, put your cursor at 1st line, 1st column, then click menu Edit/Column Editor..., in field 'Text to insert', enter ", then the beginning of each line will have a quote, then you can use regular expression to search/replace all the tabs.

1
  • Be careful. You may get bad results when there are multi-line text values!
    – Lluser
    Aug 5, 2021 at 11:16
0

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

1
0

I used the approach below to take three columns in Excel and build the string in the fourth column.

=CHAR(34)&A2&CHAR(34)&","&CHAR(34)&B2&CHAR(34)&","&CHAR(34)&C2&CHAR(34)&""

My issue with the "''"@"''" approach is the second column of my data was a number the "''"@"''" approach did nothing with the numbers. Sometimes the second column is blank but I needed to make sure it was represented in the final text file.

0

Another way if you have MS access (I have ver 2007) import the file then export it as a text file. Then change the .txt to .csv. Note all number fields will not have double quotes around it so if you need double quotes around the numbers too then while in Access change the field from a number field to a text field.

0

I have run into this issue many times. Here is the simplest solution I have come up with.

  1. Save the file as Excel
  2. Import the Excel into an Access table
  3. Export the table from Access to a text file.

Access will allow you to specify exporting delimiter as a comma and qualifying text fields with quotes. In the export wizard, you can specify whatever delimiter or character around strings you want.

You may need to create an Access query to arrange the fields in a particular order or hide the auto-ID field added by Access.

Also... once exported to a CSV, do NOT open it in Excel again. If you want to look at the results, open it in notepad. Excel has a tendency to mess with the data of a csv.... I think it was fields padded with leading zeros if I remeber correctly.

1
  • This appears to duplicate the information in user326114's answer.
    – fixer1234
    Apr 6, 2016 at 19:08
0

Exporting comma separated and quoted strings can be done with only Excel 2016 and Notepad, using a copy of the data, a formula, an export, a file properties change, a replacement in Notepad, saving the exported file and cleanup. Each is a simple step. In detail:

  1. Copy the columns to be exported into a new intermediate sheet to preserve the original and as backup, with the new sheet to be deleted later to leave the spreadsheet as it was.

  2. Put otherwise not occurring character(s), say '#' or ';-)' at each end of a string in the column say A with the formula =concat("#",trim(A1),"#"), putting the formula on all the rows of another column.

    • Adjacent columns can be done at the same time but don't quote numbers to avoid having them read in as strings.
    • The trim avoids any trailing spaces which can trigger unskillful export behavior.
    • Strings should not contain a ' " ' which might perturb import.
  3. Copy the new column(s) back over A..., using the '123' method so as to not carry the formula.

  4. Export the sheet as a CSV file, to put in the commas between fields including numbers.

  5. Change the file.csv properties so that it can be opened with Notepad.

  6. Use Notepad to replace the arbitrary character(s) with ' " '.

  7. While it would seem sensible to use " as the arbitrary character, it has to be put in a different cell, say '$A$50', and then what appears on export is ' " " " ', apparently another inconvenient trigger.

  8. In notepad the file should be saved, becoming a *.txt file to be imported and the intermediate *.csv deleted.

  9. With cleanup of the extra spreadsheet sheet mission accomplished.

Perhaps the Access export tools can one day be embedded in Excel. A more generic approach among those programs that use cut and paste, would be to have the paste options include a choice of ways to interpret the output structure and provide delimiters.

0

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
0
0

Here's how to use the OpenOffice app to take an Excel spreadsheet and create a text (csv) file with quotation marks around every text field.

Open the Excel file with OpenOffice, then follow these steps:

File > Save As

Provide a new name for the file you're about to create

For Filetype, choose "Test CSV (.csv)"

turn on checkbox "Edit Filter Settings"

click "Save"

choose "Keep current format"

Field delimiter should be a comma: ,

Text delimiter should a quotation mark: "

check the box "Quote all text cells"

click "OK"

This worked for me!

0

I made my own VBA macro to create my CSV in no time.

It just put the result in a "output" sheet that you need to create.
The result double quote all column and add ";" as a delimiter.

If you guys ask, I can add some parameters to the code to add flexibility.

Hope you guys enjoy!

Public Sub CreateCSV()

    Dim i, j, nc, nr As Integer
    
    nr = Range("A65000").End(xlUp).Row
    nc = Range("ZZ1").End(xlToLeft).Column
    
    With Sheets("output")
        .Cells.Clear
    
        For i = 1 To nr
        
            For j = 1 To nc
                
                    If j = 1 Then
                        .Cells(i, 1) = Chr(34) & Cells(i, j) & Chr(34) & ";"
                    ElseIf j = nc Then
                        .Cells(i, 1) = .Cells(i, 1) & Chr(34) & Cells(i, j) & Chr(34)
                    Else
                        .Cells(i, 1) = .Cells(i, 1) & Chr(34) & Cells(i, j) & Chr(34) & ";"
                    End If
                
            Next j
            
        Next i

    End With
    
End Sub
0

This can also be done by using Microsoft SQL Server management Studio. You basically just use the Import or Export feature to import or export your CSV file to a new CSV file with text qualifiers around each field.

  1. Create a database if one doesn't exist
  2. Right-click the database (any database)
  3. Click Tasks
  4. Click Import Data (or Export data...either should work)
  5. For Data source, select Flat File Source
  6. Browse and select your existing CSV file
  7. Click Next and verify that everything looks right in the preview
  8. Click Next again
  9. For Destination, choose Flat File Destination
  10. Browse and specify the name and location of the new file that will be created
  11. For Text qualifier, enter " (one double quote)
  12. Click Next and then Finish

Your new file should now have double quotes around each field.

0

The top answer (from Microsoft) is good, but it is a little hard to use since you need to provide a filename and you must preselect the cell range that you want to export. I found it easier to prepopulate the filename with <path-to-my-excel-file>/output.csv and also have the export just take everything from the current worksheet via ActiveSheet.UsedRange. Attach this VBA module to a button on your worksheet, and you have a 1-click solution to export everything as-is.

Sub QuoteCommaExport()
   ' Dimension all variables.
   Dim DestFile As String
   Dim FileNum As Integer
   Dim ColumnCount As Long
   Dim RowCount As Long

   ' Prompt user for destination file name.
   DestFile = InputBox("Enter the destination filename" _
      & Chr(10) & "(with complete path):", "Quote-Comma Exporter", Application.ActiveWorkbook.Path & Application.PathSeparator & "output.csv")

   ' Obtain next free file handle number.
   FileNum = FreeFile()

  ' Turn error checking off.
   On Error Resume Next

   ' Attempt to open destination file for output.
   Open DestFile For Output As #FileNum

   ' If an error occurs report it and end.
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
   End If

   ' Turn error checking on.
   On Error GoTo 0

   ' Loop for each row in selection.
   For RowCount = 1 To ActiveSheet.UsedRange.Rows.Count
   If ActiveSheet.UsedRange.Cells(RowCount, 1) <> "" Then
   ' Loop for each column in selection.
      For ColumnCount = 1 To ActiveSheet.UsedRange.Columns.Count

        ' Write current cell's text to file with quotation marks.
         Print #FileNum, """" & ActiveSheet.UsedRange.Cells(RowCount, _
            ColumnCount).Text & """";

         ' Check if cell is in last column.
         If ColumnCount = ActiveSheet.UsedRange.Columns.Count Then
            ' If so, then write a blank line.
            Print #FileNum,
         Else
            ' Otherwise, write a comma.
            Print #FileNum, ",";
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
   End If
   ' Start next iteration of RowCount loop.
   Next RowCount

   ' Close destination file.
   Close #FileNum
End Sub
-1

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.
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .