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


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
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.
    Bárbara · 1 months ago
    Sério eu te amo rsrsrsr
    Me ajudou demais, muito obrigada!
  • To post as a guest, your comment is unpublished.
    Rodrigo · 1 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.