Md. Shamim Reza
This article illustrates 3 simple ways to convert excel files to save as CSV with double-quotes. Saving CSV files with double quotes is necessary to avoid any misrepresentation of the data. For example, consider the row of data USD,25,000,Model1. Here, the comma delimiter is a little confusing. Instead, USD,“25,000”,Model1 is more befitting. The following picture highlights the purpose of this article. Have a quick look through it to learn how to do that.
Download Practice Workbook
You can download the practice workbook from the download button below.
3 Quickest Ways to Save Excel as CSV with Double Quotes
1. Use Custom Format to Save Excel as CSV with Double Quotes
Imagine you have the following dataset. You want to save it as a CSV file with cell values wrapped in double-quotes. Then follow the steps below.
📌 Steps
- First, select the entire range (A1:B6) of the dataset.
- Then, press CTRL+1 to open the Format Cells window.
- Next, select the Custom format from the Number tab.
- Then, enter \“#\”;\“@\” in the Type field.
- After that, press OK.
- Next, select File >> Save As. Then browse the desired location to save the file.
- After that, enter a file name. Now, choose CSV (Comma delimited) (*.csv) as the file type using the dropdown arrow.
- Then, click on the Save button.
- Next, go to that desired location. Then open the CSV file with a text editor (Notepad etc).
- After that, you will see the following result containing some extra double-quotes.
- Now, press CTRL+H to open the Replace window. Enter a double quote in the Find what field without any space in between them. Keep the Replace with field empty and select Replace All.
- Finally, you will get the desired result as follows.
Read More: How to Apply Macro to Convert Multiple Excel Files to CSV Files
2. Use Windows PowerShell
Alternatively, you can save the CSV file without double quotes first. Then, you can add double quotes using Windows Powershell. Follow the steps below to be able to do that.
📌 Steps
- First, go to the worksheet that you want to save as a CSV file.
- Next, select File >> Save As as in the earlier method.
- Then, browse the desired location for the file. Next, enter a name for the file.
- After that, choose CSV (Comma delimited) (*.csv) as the file type using the dropdown arrow.
- Then, click on the Save button.
- After that, you will get the following result.
- Now, hold the SHIFT key and right-click on any blank space in the location of the CSV file without double-quotes.
- Then, click on the Open PowerShell window here.
- Now, copy the following command. You need to change the file names based on your files.
import-csv Sales1.csv | export-csv Sales2.csv -NoTypeInformation -Encoding UTF8- Then, paste the copied code onto the PowerShell window and press Enter.
- After that, you will get the desired result as shown in the picture below.
Read More: How to Convert Excel File to Text File with Comma Delimited (3 Methods)
Similar Readings
- Macro to Convert Excel to Pipe Delimited Text File (3 Methods)
- How to Convert Multiple Excel Files to CSV (3 Suitable Ways)
3. Apply a VBA Code for CSV File with Double Quotes
You can also use Excel VBA to get the same result. Follow the steps below to do that.
📌 Steps
- First, press ALT+F11 to open the VBA window.
- Then, select Insert >> Module as shown in the following picture. This will create a new module.
- Now, copy the code below, using the copy button.
Sub ExcelToCSV()
Dim rng, row, cell As Range
Dim dqt, dlim, text As String
Dim file As Variant
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
text = ActiveWindow.RangeSelection.AddressLocal
Else
text = ActiveSheet.UsedRange.AddressLocal
End If
Set rng = Application.InputBox("Please select the data range:", "ExcelDemy.Com", text, , , , , 8)
If rng Is Nothing Then Exit Sub
file = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
dlim = Application.International(xlListSeparator)
Open file For Output As #1
For Each row In rng.rows
dqt = ""
For Each cell In row.Cells
dqt = dqt & """" & cell.Value & """" & dlim
Next
While Right(dqt, 1) = dlim
dqt = Left(dqt, Len(dqt) - 1)
Wend
Print #1, dqt
Next
Close #1
If Err = 0 Then MsgBox "The CSV file has been saved to: " & file, vbInformation, "ExcelDemy.Com"
End Sub- Then, paste the copied code onto the module as shown below.
- Next, press F5 to run the code. This will ask for your desired range. Now, you can toggle back to the worksheet and select the range. Then, press OK. This will take you directly to the Save As window.
- Now, browse the location for the file and enter a name. Then, click on Save.
- After that, excel will show the following message.
- Finally, you can check for the following results.
🔎 Explanation of the VBA Code
Sub ExcelToCSV()
We will write the code inside this subject procedure.
Dim rng, row, cell As Range
Dim dqt, dlim, text As String
Dim file As Variant
Declaring necessary variables.
On Error Resume Next
Neglects any errors.
If ActiveWindow.RangeSelection.Count > 1 Then
text = ActiveWindow.RangeSelection.AddressLocal
Else
text = ActiveSheet.UsedRange.AddressLocal
End If
Automatically detects the data to select.
Set rng = Application.InputBox(“Please select the data range:”, “ExcelDemy.Com”, text, , , , , 8)
Asks for the data range.
If rng Is Nothing Then Exit Sub
Exits operation for no input.
file = Application.GetSaveAsFilename(“”, “CSV File (*.csv), *.csv”)
Sets the format of the new file.
dlim = Application.International(xlListSeparator)
Specifies comma as the delimiter.
Open file For Output As #1
Creates a new file.
For Each row In rng.rows
dqt = “”
For Each cell In row.Cells
dqt = dqt & “””” & cell.Value & “””” & dlim
Next
While Right(dqt, 1) = dlim
dqt = Left(dqt, Len(dqt) – 1)
Wend
Print #1, dqt
Next
Close #1
Wraps the cell values with double quotes and populates the new file with them.
If Err = 0 Then MsgBox “The CSV file has been saved to: ” & file, vbInformation, “ExcelDemy.Com”
Confirms saving the CSV file.
Read More: How to Convert Excel Files to CSV Automatically (3 Easy Methods)
Things to Remember
- You need to give the proper file path in the PowerShell command in case you open it from other locations.
- You need to change the file names based on your files.
Conclusion
Now you know how to convert excel files to save as CSV files with double-quotes. Please let us know if this article has provided you with your desired solution. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to learn more about excel. Stay with us and keep learning.
