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.

Solution

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

Comments

[Raystafarian]

Is the data in the csv surrounded by quotation marks, or do you want to insert quotation marks as part of the delimiter?

[Excellll]

Welcome to Super User! Please include the relevant code/information from the linked page in your answer. This will make sure your answer remains useful should the linked page ever change or disappear.