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.

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.

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.

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

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.

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.

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

Read More: How to Create CSV File from Excel

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.

Now, save the workbook as a macro-enabled workbook otherwise you will lose the code.

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.

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(\).

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.

Download Practice Workbook

Convert Excel to CSV.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!

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

1. Convert Excel Worksheet to Comma Delimited CSV File

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

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

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply