Convert Excel to Comma Delimited CSV File (2 Easy Ways)

This article illustrates how to convert excel files to CSV (comma-separated values) or comma-delimited format. CSV files contain data without any formatting, unlike excel. It makes the data more easily accessible and usable by various software. CSV formatted data are also easier to handle while using it in coding. The following picture highlights the purpose of this article. Have a quick look through the article to learn how to do that.

Convert Excel to CSV Comma Delimited


Download Practice Workbook

You can download the practice workbook from the download button below.


2 Simple Ways to Convert Excel to Comma Delimited CSV File

1. Convert Excel Worksheet to Comma Delimited CSV File

Assume you have a workbook with two worksheets named Names and Sales. Then, you can convert each worksheet to a CSV file one at a time. Follow the steps below to be able to do that.

πŸ“Œ Steps

  • First, go to the worksheet that you want to convert. Then, press F12 to open the Save As window. Alternatively, you can select File >> Save As to do that.
  • Now, browse to the preferred location where you want to save the converted file. Then, you can change the file name if you want.
  • Next, click on the dropdown box for the Save as type option.

Convert Excel to Comma Delimited CSV File

  • After that, you will see a list of file formats to which you can convert the file. Now, selecting CSV (Comma delimited) (*.csv) will give the desired result. But, you need to select CSV UTF-8 (Comma delimited) (*.csv) if you have foreign characters in your dataset. The CSV (Machintosh) (*.csv) and CSV (MS-DOS) file types are for Machintosh and MS-DOS operating systems respectively.

Convert Excel Worksheet to Comma Delimited CSV File

  • Now, choose the correct file type and then select Save.

Convert Excel Worksheet to Comma Delimited CSV File

  • Then, excel will show the following warning only if you have multiple worksheets in your workbook. Now, click OK to convert the active worksheet only.

Convert Excel to Comma Delimited CSV File

  • After that, the active worksheet will be converted to a CSV file. But, the worksheet name in the original workbook will change to the file name used while saving the CSV file. Furthermore, you will see a warning for possible data loss. Now, you can ignore all of this and close the workbook without saving it.

  • After that, go to the specified file path. Then you will see the converted CSV file there.

  • Now, open the file with Notepad. Then you will see the following result.

  • Next, go to the other worksheet and repeat the earlier procedure. This time use the worksheet name for the file name.

Convert Excel Worksheet to Comma Delimited CSV File

  • After that, you will see a similar result as earlier.

Read More: How to Convert Excel Files to CSV Automatically (3 Easy Methods)


2. Convert Excel Workbook to CSV Format (Comma Delimited)

You can use Excel VBA to convert all of the worksheets at once. 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 picture below. This will take you to a new blank module window.

  • After that, copy the following code using the copy button.
Sub ExcelToCSV()
Dim sh As Worksheet
Dim file_path As String
Application.ScreenUpdating = False
file_path = ActiveWorkbook.path & "\" & _
Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
For Each sh In Worksheets
sh.Copy
ActiveWorkbook.SaveAs Filename:=file_path & "-" & sh.Name & ".csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
Next
Application.ScreenUpdating = True
End Sub
  • Next, paste the copied code onto the module window.

  • Then, press F5 to run the code. Now, go to the location of your workbook. After that, you will see all of the worksheets are converted to CSV files.

Explanation of the VBA Code:

Sub ExcelToCSV()
We will write the code inside this subject procedure.

Dim sh As Worksheet
Dim file_path As String
Declaring necessary variables.

Application.ScreenUpdating = False
This makes VBA work in the background. As a result, the code works faster.

file_path = ActiveWorkbook.path & β€œ\” & _
Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, β€œ.”) – 1)
Defining the value of the variable. This stores only the name of the file excluding the file extension.

For Each sh In Worksheets
This For Loop goes through each worksheet.

sh.Copy
This code line copies the worksheet.

ActiveWorkbook.SaveAs Filename:=file_path & β€œ-” & sh.Name & β€œ.csv”, _
FileFormat:=xlCSV, CreateBackup:=False
Saves the workbook as a CSV file in the same path as the source workbook.

ActiveWorkbook.Close False
Stops VBA from closing the source workbook.

Application.ScreenUpdating = True
Sets screen updating to its default.

Read More: How to Apply Macro to Convert Multiple Excel Files to CSV Files


Convert Excel to CSV FilesΒ  with a Different Delimiter (Pipe, Semicolon, Slash, etc.)

So far we have learned to convert Excel to CSV (Comma Separated Values) format with a comma delimiter. However, you can use pipe, semicolon, or any other delimiter by changing the default Windows settings. In this section, we will discuss this in simple steps.

Follow the steps below if you want to use a different delimiter (/ ; | etc) to separate the values in the CSV file.

πŸ“Œ Steps

  • First, press Win+R to open the Run Type control and click OK. This will take you to the Control Panel.

  • Then, select Change date, time, or number formats from the Clock and Region settings.

  • Next, select the Additional settings from the Formats tab in the Region window. This will take you to the Customize Format dialog box.

  • After that, change the List separator to a desired one. Now, every time you save your worksheet as a CSV file, the values will be separated by that delimiter.

Note:

To insert a pipe delimiter, press SHIFT+Backslash(\).

Use a Different Delimiter While Converting from Excel to CSV

Read More: Macro to Convert Excel to Pipe Delimited Text File (3 Methods)


Things to Remember

  • Save your workbook before converting the worksheets to CSV files. Close your workbook without saving it after the conversion.
  • VBA code will always separate values in the CSV file by the delimiter specified by the List separator.

Conclusion

Now you know how to convert excel files to CSV formatted files. Hopefully, this has helped you to solve your problem. Please use the comment section below for further queries or suggestions. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo