Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to save worksheet data as csv file with / without double quotes?

When you export or save a range of data from Excel to csv file, normally, the csv data is not around with double quotes, but, if your cell values are separated by comma or line break, the exported csv data will be around with double quotes. And this article, I will talk about how to save worksheet data as csv file with or without double quotes as you need.

Save worksheet data as csv file with double quotes

Save worksheet data as csv file without double quotes


arrow blue right bubble Save worksheet data as csv file with double quotes


To save the normal data as csv file with double quotes, the following VBA code may help you, please do as follows:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Save worksheet data as csv file with double quotes:

Sub CSVFile()
'updateby Extendoffice 20160530
    Dim xRg As Range
    Dim xRow As Range
    Dim xCell As Range
    Dim xStr As String
    Dim xSep As String
    Dim xTxt As String
    Dim xName As Variant
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
    Set xRg = Application.InputBox("Please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    xName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
    xSep = Application.International(xlListSeparator)
    Open xName For Output As #1
    For Each xRow In xRg.Rows
        xStr = ""
        For Each xCell In xRow.Cells
            xStr = xStr & """" & xCell.Value & """" & xSep
        Next
        While Right(xStr, 1) = xSep
            xStr = Left(xStr, Len(xStr) - 1)
        Wend
        Print #1, xStr
    Next
    Close #1
    If Err = 0 Then MsgBox "The file has saved to: " & xName, vbInformation, "Kutools for Excel"
End Sub

3. And then press F5 key to run this code, and a prompt box is popped out to remind you select the data range that you want to save as csv file with double quotes, see screenshot:

doc save csv with quotes 1

4. Then click OK button, and a Save As window is appeared, please specify the file name and location, and then click Save button, see screenshot:

doc save csv with quotes 2

5. And a Kutools for Excel’s prompt box is popped out to remind you the new csv file has been saved into your specified location, see screenshot:

doc save csv with quotes 3

6. Click OK to close the dialog, and now, when you open your new csv file, all the data are surrounded by the double quotes as following screenshot shown:

doc save csv with quotes 4


arrow blue right bubble Save worksheet data as csv file without double quotes

If your data are separated by comma, line break in a cell, when you save the data as csv file, the data will be surrounded by the double quotes as following shown:

doc save csv with quotes 5

To save the data as csv file without the double quotes, please apply the following VBA code.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Save worksheet data as csv file without double quotes:

Sub Export()
'updateby Extendoffice 20160530
    Dim xRg As Range
    Dim xRow As Range
    Dim xCell As Range
    Dim xStr As String
    Dim xTxt As String
    Dim xName As Variant
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
    Set xRg = Application.InputBox("Please select data range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    xName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
    Open xName For Output As #1
    For Each xRow In xRg.Rows
        xStr = ""
        For Each xCell In xRow.Cells
            xStr = xStr & xCell.Value & Chr(9)
        Next
        While Right(xStr, 1) = Chr(9)
            xStr = Left(xStr, Len(xStr) - 1)
        Wend
        Print #1, xStr
    Next
    Close #1
    If Err = 0 Then MsgBox "The file has saved to: " & xName, vbInformation, "Kutools for Excel"
End Sub

3. Then press F5 key to run this code, and a prompt box is popped out to remind you select the data range that you want to save as csv without double quotes, see screenshot:

doc save csv with quotes 6

4. Click OK button, and a Save As window is displayed, please specify a file name and location for your new csv file, see screenshot:

doc save csv with quotes 7

5. And then click Save button, a Kutools for Excel’s prompt box is popped out to tell you the new csv file has been saved into your specified location, see screenshot:

doc save csv with quotes 8

6. Click OK to close the dialog, and, when you open your new csv file, the double quotes surrounded with data are removed as following screenshot shown:

doc save csv with quotes 9


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Magnus Levsonn · 2 months ago
    not unicode
  • To post as a guest, your comment is unpublished.
    Bárbara · 7 months ago
    Sério eu te amo rsrsrsr
    Me ajudou demais, muito obrigada!
  • To post as a guest, your comment is unpublished.
    Rodrigo · 7 months ago
    Olá!
    Muito obrigado! O conteúdo me ajudou muito. Precisava gerar um arquivo txt eliminando as aspas duplas no início e no final de cada linha e não estava conseguindo resolver. Agora consegui.