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

CSV is a widely used format generally required for different data analysis software. In addition to that, it has high adaptability and is easy to organize and edit. Because of this widely popular nature, you may run into situations where you need to convert your datasets from Excel into CSV format for usage in different scenarios. In this tutorial, I am going to show you how to convert Excel files to CSV automatically.

What Is CSV?

CSV or comma-separated values is a file format where, as the name suggests, values are stored as strings with a comma as a delimiter to separate them. Although this is what it really is, it can be more complicated than that. Appending a lot of data, the file can become quite a big mess. But some software or programming languages like Pandas library in Python prefer using these files as inputs.

Some benefits of using a CSV file include-

• It is widely adopted by users from different platforms
• Easy to organize and edit
• Can be used with almost all of the major spreadsheet programs like Microsoft Excel or Google Spreadsheet
• It is also made use of by different business software

On the other hand, a major downside of using such a file is that it is limited to containing one spreadsheet only. In contrast, a .xlsx file from Microsoft Excel can contain different spreadsheets containing many different datasets at a time.

How to Convert Excel Files to CSV Automatically: 3 Ways

Excel provides some methods to convert a spreadsheet to a CSV file. Although the major and widely used method is to use the Save As command, you may need to use other methods depending on the dataset characters or whether you want to convert multiple spreadsheets at a time or not.

For the purpose of this, I am dividing different methods into their own sub-sections. Additionally, I will be going over the pros and cons of each one in its own sub-section. Follow along to see how every method works or find the one you need from the table.

1. Convert Excel Files to CSV Automatically Using Save As Command

This is the most suitable and widely used method to convert Excel Files to CSV automatically. If your spreadsheet doesn’t have any Unicode characters, by all means, go for it. Granted we can use Microsoft’s Save As feature to convert any files to other formats, we can also use it to convert Excel files to CSV automatically.

For a more detailed guide follow these steps. For the purpose of demonstration, I have used the following dataset.

Steps:

• First, select the spreadsheet you want to convert if there are multiple spreadsheets in your Excel file.
• In your ribbon, click on the File tab.

• Then, click on Save As. You can skip the last two steps and just press F12 on your keyboard to open the Save As window.

• Now, in the Save As window, select the location you want to save your file and select CSV (Comma delimited) in Save as type. You can also put CSV UTF-8 and get the same result.

• After that click on Save.
• If a warning window appears warning it will only convert one spreadsheet (appears if you have more than one spreadsheet in your Excel file), click on OK.

It will convert Excel files to CSV automatically.

You can also, open it in Excel. It will show something like this removing all the formats.

Read More: How to Convert Excel to CSV without Opening

2. Convert Excel Files to CSV with Special Characters

If you follow the previous method with a spreadsheet containing Unicode characters you may run into problems importing the data back again. Saving the file as CSV (comma delimited) will distort any character that is not ASCII. So if you have special characters in your dataset follow one of these two steps to convert Excel files to CSV automatically, without distorting the characters.

For both methods, I have used the following dataset with Chinese figures in their respective names in Mandarin.

While converting Excel files to CSV automatically, you can convert them to both Unicode forms: UTF-8 and UTF-16. The main difference between the two being UTF-8 is a more compact encoding and encoding of common characters including English and numbers using 8 bits. UTF-16 encodes at least 16 bits for every character. Be that as it may, you can convert into either one and get a similar CSV file. Although for Asian characters like these, UTF-16 is recommended.

2.1 Save as CSV UTF-8

UTF-8 with an ASCII character has the same encoding as an ASCII file. So if you don’t have that many Unicode characters to be converted into a CSV file, you should convert them to UTF-8.

To convert Excel files to CSV UTF–8 automatically, follow these steps.

Steps:

• First, select the spreadsheet you want to convert to a CSV file if you have more than one spreadsheet in your Excel file.
• Then click on the File tab from your ribbon.

• After that, click on Save As. You can skip the two methods and press F12 on your keyboard and achieve the same outcome up to this point.

• Then, in the Save As window, select the location where you want to save your file. And select CSV UTF- (Comma Delimited) in Save as type field.

• Click on Save.
• If you have more than one spreadsheet in your Excel file a warning window may appear telling you that only one spreadsheet will be converted. Click OK on that.

This way you can convert Excel files to CSV automatically with special characters without messing up or distorting the characters.

In the end, the CSV file will look something like this, preserving all the data.

If you open it with Excel it will look something like this.

2.2 Save as CSV UTF-16

For Asian characters like the one used in the dataset, it is recommended to use UTF-16. The downside of it is it takes up more bytes than UTF-8 and is not fully compatible with ASCII files.

Nonetheless, if you want to convert Excel files automatically to CSV UTF–16 follow the steps below.

Steps:

• First, select the spreadsheet you want to convert if you have more than one in your Excel file.
• Then go to the File tab on your ribbon.

• And select Save As after that. Or ignore the last two steps and press F12 on your keyboard.

• In the Save As window, select the location and select Save as type as Unicode Text.

• After that, Click on Save.
• It will save as a text file. Now open it up with a text editor like Notepad. In the event, you will find it saved with Tabs as the delimiter in between data.

• Now copy the tab space from the notepad.

• After that, open up the Find and Replace box in the notepad by pressing Ctrl+H on your keyboard.
• In the Find what box, paste the tab space.
• Then in the Replace with box, put a comma(,) inside.
• Finally, click on Replace All.

• After that close the box. Consequently, it will replace all the tab space with commas and you will have a text file with comma-separated values.

• Finally, change the extension manually to .csv.

In this way, you can convert Excel files to CSV UTF-16 automatically.

Read More: How to Convert Multiple Excel Files to CSV

3. Convert Excel Files to CSV with Multiple Sheets

The previous methods described can all be used to convert Excel files to CSV automatically. But all of them convert a single spreadsheet at a time to one CSV file. But sometimes there are scenarios where you have a large number of spreadsheets on your Excel file and have to convert every one of them. Thankfully, Microsoft Visual Basic for Applications (VBA) provides a solution for that.

To use VBA codes, first, you need to have the Developer tab on your ribbon. Enable the Developer tab, if you don’t have it already.

For demonstration, I am using a file with above mentioned two datasets in it in different spreadsheets (If you download the one from the start of the article you will have this one).

Follow the steps if you want to batch convert Excel files to CSV automatically.

Steps:

• First, go to the Developer tab on your ribbon and select Visual Basic from the Code group.

• Then in the VBA window, select Insert. After that, select Module from the drop-down menu.

• Inside the module, write down the following code.

Sub Batch_Convert_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

• Once you have written it in your module, save the file and press F5 on your keyboard to run the code.

You will have all the spreadsheets converted into different CSV files in the directory of your main Excel file.

This will be the converted CSV file of the first spreadsheet.

And this will be the one for the second spreadsheet.

Note that, the batch conversion, too, distorted the Unicode characters as mentioned in the second method. If you have special characters in your dataset follow the second method.

Read More:  How to Save Excel File as CSV with Commas

Download Practice Workbook

Conclusion

These were different methods for different scenarios to convert Excel files to CSV automatically. Hope you have found this article helpful and informative. If you have any questions or suggestions let us know below. For more detailed guides like this, visit Exceldemy.

Related Articles

• Set Comma Instead of Semicolon as Delimiter in Excel CSV
• How to Make a CSV File in Excel for Contacts
• Save Excel as CSV with Double Quotes
• [Fixed!] Excel Not Saving CSV with Commas
• 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

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