How to Save Excel File as CSV with Commas (3 Suitable Methods)

A Comma-Separated Values(CSV) file is a delimited text file with commas. It comes in handy during different sorts of data analysis. Sometimes you may be required to save an Excel file as a CSV file for further use. In this article 3 suitable methods to save Excel files as CSV with commas.

What Is CSV?

CSV or Comma-Separated Values is a popular file format that stores the values as text strings and each value is separated by commas. Various software and programming languages like the Pandas library in Python take CSV files as input.

Excel offers several methods to save the file as CSV. The most popular method is applying the Save As command but you may need to follow different methods for datasets having different characters or you may want to save multiple worksheets as CSV at once. Here we will discuss 3 suitable methods to save the Excel file as CSV with commas.

1. Saving the Excel File as CSV with Commas Using Save As Command

The most popular way of saving Excel files as CSV with commas is to use the Save as command. Thanks to Excel as it allows you to save the file in different formats. We can use the Save As feature to save the Excel file as CSV with commas. The method is discussed below with steps. We used a dataset having some great book names with their authors and publishing years for demonstrating the procedures.

Steps:

• First, open the worksheet which we want to save as a CSV file.
• Then, go to the File tab in the ribbon and select Save As from the options.

• Now, in the Save As window, fix the location to save the file and select the CSV (Comma delimited) (*.csv) as the type of the file.
• Afterward, press save.

• A notification will pop up if you have more than one sheet in the workbook. Just press OK.

• Finally, a CSV file is saved in the selected location which should look like this. The file excludes any format used in the worksheet.

• We can also open the file in notepad which will show the values separated by commas.

Read More: Set Comma Instead of Semicolon as Delimiter in Excel CSV

2. Converting File Having Special Characters to CSV with Commas

The above-mentioned method works just fine if our worksheet only contains ASCII characters. If our worksheet has Unicode characters in it, we have to follow slightly different methods. Two such methods are discussed below. We used a dataset containing Mandarin to demonstrate the methods.

Save as CSV UTF-8

we can save the worksheet having Unicode characters as CSV UTF-8 file type. This format doesn’t distort the Unicode characters. let’s follow the steps given below for this method.

Steps:

• First, open the worksheet having the Unicode characters and we want to save it as CSV with commas.

• Now, go to the File tab of the ribbon and select Save As from the list.
• In the Save As window, fix the location of the file to save and select CSV UTF-8 (Comma delimited) (*.csv) as the Save File Type.
• Subsequently, press Save.

• A notification will appear if we have more than one worksheet in the workbook. We have to press OK.
• In the end, a CSV file is saved. We can open it with a notepad.
• We will see the Mandarin words are being displayed just fine and the values are separated by commas.

• Here, if we open the file in Excel we will see the dataset without any previous format.

Save as CSV UTF-16

Instead of using CSV UTF-8 format, another format can also be used to save files containing Unicode. Especially for files with Asian characters, we can use CSV UTF-16 file type. This file also stores values separated by commas. The difference in this file type is it uses 16 bytes to represent each character. Let’s follow the steps below to save a file containing Mandarin to CSV UTF-16.

Steps:

• First, open the sheet which we want to save as CSV with commas.

• Then, go to the File tab of the ribbon and select Save As option.
• Afterward, the Save As window will open. Select the location to save the file and choose Unicode Text as the Save Format Type.
• Succeedingly, press Save.

• A notification may appear if we have more than one worksheet in the workbook.
• Just press OK.
• Finally, we can see the saved CSV file which contains the values from our selected worksheet.

• The values are not separated by commas yet.
• Now, Copy the space in between the values of the text file.
• Further, use the keyboard shortcut Ctrl + H to open the Find and Replace box.

• Next, Paste the copied space in the Find what section and put a comma (,) in Replace with section.
• Subsequently, select Replace All.

• We can see the values separated by commas. Let’s save the text file as CSV now.

• Go to the File option in the notepad and select Save As.
• Next, select All Files as Save as type and write (.csv) at the end of the file name.
• Finally, press Save.

• That’s it! We saved a CSV File. We can open it with a notepad to see values separated by commas.

Read More: Save Excel as CSV with Double Quotes

3. Inserting VBA Code for Saving Excel File as CSV with Commas

So far our shown methods saved one worksheet at a time. However it’s time-consuming to save each worksheet at a time for all sheets. There a quick solution for that using a simple VBA (Visual Basics for Applications) code. Let’s follow the steps given below for saving an Excel file as CSV with commas using VBA code.

Steps:

• First, open one of the worksheets of the workbook.
• Then, press Ctrl + F11.

• Or you can select Developer tab > Visual Basic to open the VBA code window.

• Next, in the VBA window select the active sheet and right click.
• Further, select Insert > Module.

• Now, a code window will open. Write the following code in that window.

Sub Batch_Save_CSV()

Dim wsht As Worksheet
Dim file_path As String

Application.ScreenUpdating = False
file_path = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)

For Each wsht In Worksheets
wsht.Copy
ActiveWorkbook.SaveAs Filename:=file_path & "_" & wsht.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
Next
Application.ScreenUpdating = True

End Sub

Sub Batch_Save_CSV()

Dim wsht As Worksheet
Dim file_path As String

Application.ScreenUpdating = False
file_path = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)

For Each wsht In Worksheets
wsht.Copy
ActiveWorkbook.SaveAs Filename:=file_path & "_" & wsht.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
Next
Application.ScreenUpdating = True

End Sub

• Now, click on the Run button from the VBA window.

• We can see each sheet in our workbook saved as  CSV file with commas. We can open the file in the notepad to see the change.
• Here, the CSV file for Sheet1.

• And the CSV file for Sheet4.

Read More: [Fixed!] Excel Not Saving CSV with Commas

Advantages of CSV File

• Users from several platforms use it.
• The file is easy to handle and organize.
• Different software and programming languages use the file as input.

Download Practice Workbook

Conclusion

CSV files with commas can be saved from Excel files following different methods. Here we have discussed 3 such suitable methods to save Excel files as CSV with commas. If you face any problem following the methods, please let us know by commenting. Please, visit our ExcelDemy site for more articles regarding Excel.

Related Articles

• How to Create CSV File from Excel
• How to Convert Excel File to Text File with Comma Delimited
• Convert Excel to Comma Delimited CSV File
• How to Make a CSV File in Excel for Contacts
• How to Convert Excel Files to CSV Automatically
• How to Convert Excel to CSV without Opening
• How to Convert Multiple Excel Files to CSV

<< Go Back to Export Excel to CSV | Export Data from Excel | Learn Excel