Bdtyktl

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.

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

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.

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.