How to save worksheet data as csv file with / without double quotes?
When you need to export or save a range of data from Excel to a CSV file, you may notice that, by default, the CSV data is not surrounded by double quotes. However, if your cell values contain special characters such as commas or line breaks, Excel automatically surrounds those values with double quotes in the exported CSV. This behavior ensures data fields are preserved correctly when the file is opened in other applications. In practical work scenarios, you might want to control whether all data is quoted, or avoid quotes entirely. This article introduces several effective methods for saving worksheet data as a CSV file with or without double quotes, analyzing when each method is suitable and providing step-by-step instructions.
Save worksheet data as csv file with double quotes
Save worksheet data as csv file without double quotes
Other Built-in Excel Methods: Use Excel's "Save As" to export CSV
Save worksheet data as csv file with double quotes
If you need to ensure that every value in your worksheet is wrapped in double quotes when exporting to CSV—for example, for compatibility with specific data import systems or when your data contains commas, tabs, or line breaks—you can achieve this by using VBA code to customize the export process.
For most users, Excel's standard export does not offer an option to always quote all fields. Using VBA grants you granular control over the output format, especially in complex or large datasets. A possible drawback is that this approach requires basic familiarity with the VBA editor, but the flexibility often outweighs this minor inconvenience.
To perform this operation, follow the detailed steps below:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. If you are not familiar with the VBA environment, ensure you save your workbook before proceeding.
2. Click Insert > Module, and paste the following code into 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 Sub3. Press F5 to run the macro. A dialog prompts you to select the data range you want to save as a CSV file with double quotes. See screenshot:
4. After selection, click OK. A Save As window opens. Specify your target file name and save location, then confirm. See screenshot:
5. A prompt in Kutools for Excel confirms that the new CSV file has been saved at your specified location. See screenshot:
6. Click OK to close the dialog. When you open your new CSV file using a text editor or import it to another system, you will find that all data are surrounded by double quotes, as shown here:
Tip: Occasionally, regional settings (such as list separator symbols) might affect the format of the saved CSV. Double-check your exported file if you encounter delimiter issues, and consider adjusting xSep in the code for custom delimiters.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Save worksheet data as csv file without double quotes
Sometimes, you may want to export Excel data to CSV without including any double quotes, especially when the data does not contain fields with commas, line breaks, or other special characters that could confuse CSV parsers. By default, if data fields are separated by commas or line breaks in a cell, Excel surrounds them with double quotes when exporting to CSV to preserve data integrity, as illustrated below:
If you wish to save your data as a CSV file with no double quotes at all, you can use a custom VBA macro that outputs plain values separated by a chosen delimiter (tab character in this case). This is especially useful when you are certain your data does not contain problematic characters and strict quote compliance is required.
Note: Tab-separated values (.tsv) are technically not CSV, but many systems accept them and they provide a straightforward structure that avoids the need for double quoting.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. Save your workbook before proceeding to prevent potential data loss from unexpected code errors.
2. Click Insert > Module, then paste the code below into 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 SubThis script generates a CSV file where each field is separated by a tab instead of a comma, and no double quotes are applied to the cell values. Review your data to confirm that tab characters will not appear within individual cells, as this could disrupt the output structure. The code also prompts for a file name and location for the exported file.
3. Press F5 to run the macro. A prompt box appears for you to select the data range you want to export as a CSV file without double quotes:
4. After selecting the data, click OK. In the Save As dialog, specify the file name and location. Click Save to proceed:
5. Kutools for Excel displays a confirmation dialog to let you know the file has been saved. Click OK:
6. After this, open your new file in a text editor or import tool to confirm that the exported data is not surrounded by double quotes, as shown below:
If you discover that your data requires preservation of commas or line breaks within cells, it is advised not to use this method, as parsing errors may occur.
Other Built-in Excel Methods: Use Excel’s “Save As” to export CSV
Excel also provides a straightforward built-in method for exporting worksheet data as a CSV file through its “Save As” feature. This approach is suitable for quick exports and for users who do not require manual control over double-quoting or delimiter characters. When using this method, Excel automatically adds double quotes around fields containing special characters such as commas, line breaks, or double quotes themselves. For fields without such characters, no double quotes are added. This follows standard CSV conventions, ensuring compatibility with most third-party applications and data import systems.
Although you cannot force Excel to always quote fields or to suppress quoting entirely through this method, it’s effective for basic data exports where standard CSV formatting is sufficient. The main limitation is the lack of granular control—if your data must be quoted uniformly or you need to customize delimiters, VBA or other specialized tools are preferable. Additionally, this process always exports the entire active worksheet; exporting a specific range requires copying it to a separate sheet first.
To use Excel’s “Save As” for CSV export, follow these steps:
1. Click File > Save As. In older versions of Excel, choose Office Button > Save As.
2. In the Save As dialog, choose a file location and enter your desired file name.
3. In the Save as type drop-down menu, select CSV (Comma delimited) (*.csv).
4. Click Save. If the worksheet contains features unsupported by CSV format (such as formulas, formatting, or multiple sheets), Excel informs you that only the active sheet will be saved in CSV format. Click OK to continue.
After saving, open the resulting file with a text editor (such as Notepad). You will notice that:
- Cells containing commas, line breaks, or special characters are automatically surrounded with double quotes.
- Cells with plain text or numbers and no special characters are not quoted.
- No manual option exists in this method to force/omit quoting for all fields.
Tip: Before exporting, check for unwanted special characters in your data, as these will influence how Excel quotes the cells. For partial range exports, copy the desired range to a blank worksheet and use “Save As” from there. In international Excel versions, the field delimiter may follow your local settings.
Troubleshooting and suggestions:
- Always check your exported CSV file with a text editor to confirm quoting and delimiters are as desired.
- If you encounter encoding issues (e.g., non-English characters displaying incorrectly), consider saving as CSV UTF-8 in the “Save As” dialog when available.
- When using VBA, review your data for internal double quotes or delimiters that may cause formatting issues; preprocess the data if such cases exist.
- If you need to export only a part of your worksheet, either copy the range to a new worksheet and export, or use VBA to select a range specifically.
- Be mindful that CSV format does not preserve formulas, formatting, or multiple sheets—only raw values from a single sheet are saved.
Summary: Choosing between VBA and built-in Excel options depends on your requirements for quoting consistency, delimiter control, and data range selection. The VBA methods provided allow fine control for advanced needs, while Excel’s standard “Save As” is well-suited for fast, simple exports with standard quoting behavior. If neither method fits your specific requirements (such as quoting only some columns or applying custom escaping rules), consider additional scripting or dedicated CSV utilities to process your data efficiently before distribution.
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in