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
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
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:
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:
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:
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:
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:
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
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:
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:
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:
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:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
