How can I make Excel save a .csv using commas and quotes?

Question

I am trying to save file as a .csv, however, Excel is not using the standard comma separator and quotes. Here is an example of what I want:

"0","70","0","4/29/2012 12:00","13311250""1","70","0","4/30/2012 12:00","13311250""2","70","0","5/1/2012 12:00","13311250"

This is what Excel is actually giving me:

0   70  0   4/29/2012 12:00 13311250
1   70  0   4/30/2012 12:00 13311250
2   70  0   5/1/2012 12:00  13311250

So what is going on, why am I not even getting any quotation marks? The process I followed was to import the file from .csv (shown in snippet 1) using data from text file option, I modified it, then saved it again as a .csv, but I am getting a file that is formatted the second way.

Answer 1

The following site shows the VB macro code to perform the export https://support.chartio.com/knowledgebase/exporting-csv-files-with-double-quotes-from-excel

1. Open your CSV file in Excel > Find and replace all instances of double quotes (").

2. Follow the instructions provided in this Microsoft KB article. However, instead of using the macro provided in the Microsoft KB article, use the one below in its place.

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


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

   ' 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

   MaxRow = ActiveSheet.UsedRange.Rows.Count
   MaxCol = Selection.Columns.Count

   MsgBox "Processing this many rows: " & MaxRow 
   MsgBox "Processing this many columns: " & MaxCol

   ' Loop for each row in selection.
   For RowCount = 1 To MaxRow

      ' Loop for each column in selection.
      For ColumnCount = 1 To MaxCol

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

          ' Check if cell is in last column.
          If ColumnCount = MaxCol 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
  ' Start next iteration of RowCount loop.
  Next RowCount

' Close destination file.
Close #FileNum
End Sub

Answer 2

I created a text file with your .csv contents. I then:

1. imported the .txt into Excel and selected Delimited
2. I checkboxed tab NOT comma
3. I selected General not text

Here is my output:

0,"70","0","4/29/2012 12:00","13311250""1","70","0","4/30/2012 12:00","13311250""2","70","0","5/1/2012 12:00","13311250"

Each program/application has its own interpretation of what comma delimited really is. In my Excel example, I technically did not use comma delimited but used tab delimited. You could also use text delimited, depending on what you are trying to accomplish.

Looking through RFC4180, embedded double quotes should be doubled, and the field must be delimited with double-quotes.

Answer 3

Use this script.

Source: Export Excel CSVs with Double Quotes

Excel Macros Microsoft provide access to Visual Basic in the form of Macros from within Excel that allow us to do things Excel can’t manage by itself. To create a VB Macro open the Visual Basic Editor (Alt+F11) then from the menu Insert > Module. This should open a new module code window that you should copy and paste in the following script:

Sub CSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
ListSep = Application.International(xlListSeparator)
  If Selection.Cells.Count > 1 Then
    Set SrcRg = Selection
  Else
    Set SrcRg = ActiveSheet.UsedRange
  End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
  CurrTextStr = ìî
For Each CurrCell In CurrRow.Cells
  CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
  CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End Sub

It fixed the same problem for me. I had exported a CSV from an application into Excel and when editing it, I was saving CSV files. After checking them, they were without quotes around comma limited values, but this script saves CSV files with quotes, so the saved file can be used in other applications.