In this article, you’ll learn how to export an Excel file to a CSV file using 4 methods. The methods include exporting only the active sheet using the Save As command and multiple sheets using VBA.
You’ll also learn how to import a CSV file to an Excel file using 3 methods. Here, we’ll use the Open command, the From Text command and the Power Query tool.
An Excel CSV (Comma-Separated Values) file is a simple and widely used file format that stores tabular data in plain text, where each row represents a record, and each column is separated by a comma. CSV files are easily readable and can be opened by various applications including Excel.
Below, you’ll see an overview image showing converting an Excel file to a CSV file using the Save As command.
Download Practice Workbook
You can download the Excel files from here for practice.
CSV File Formats Supported in Excel
The names, extensions, and details of the CSV file formats supported in Excel are given below.
Name | Extension | Details |
---|---|---|
CSV (Comma delimited) | .csv | Enables the usage of a workbook by saving it as a comma-delimited text file. Doesn’t allow special characters. |
CSV UTF-8 (Comma delimited) | .csv | Same as CSV (Comma delimited) but allows special characters. Available in Excel 2016 and later versions. |
CSV (Macintosh) | .csv | Same as CSV (Comma delimited) but works on the Macintosh operating system. |
CSV (MS-DOS) | .csv | Same as CSV (Comma delimited) but works on the MS-DOS operating system. |
How to Export an Excel File to a CSV File
1. Exporting Only Active Sheet Using Save As Command
- To export only the active sheet of your Excel file, go to the File tab.
- Select This PC as the location under the Save As menu.
- Give the file a name. Like we put “Excel CSV” as the file name.
- Choose CSV (Comma delimited) (*.csv) as the file type and click on the Save button to save the file.
- You’ll get a warning that only this single sheet can be saved in CSV format. You can’t save multiple sheets.
- Press OK to proceed.
- The Excel sheet will be saved as a CSV formatted file.
- We had some Japanese Kanji characters in column C of our original Excel file. Here, we are seeing question marks instead of them because the CSV format can’t recognize any special characters.
2. Exporting in CSV UTF-8 Format to Preserve Special Characters
We have to convert our Excel file to CSV UTF-8 format if we want to preserve special characters like Japanese Kanji characters in column C of our original Excel file.
- So, open the File tab.
- Under the Save As menu, choose This PC as the destination.
- Assign a name to the file, similar to “Excel CSV UTF”.
- To save the file, select CSV UTF-8 (Comma delimited) (*.csv) from the list of available file types and click Save.
- You’ll be informed that this particular sheet is the only one that can be saved in CSV format. Not more than one sheet can be saved. So, press OK.
- A CSV formatted file will be created from the Excel sheet.
- Now, the Japanese Kanji characters in column C are visible.
3. Exporting Multiple Sheets Using VBA
The above methods allow you to only convert a single sheet into CSV format. If you want to convert multiple sheets from the same Excel file into CSV format, you can use VBA Macro.
- To insert a VBA code, go to Developer > Visual Basic.
- Or use the keyboard shortcut Alt + F11 to directly open the Visual Basic window.
- Select Module from the Insert menu in the Visual Basic window to open a new Module.
- Enter the following code in the Module and click the Run button or press F5 from the keyboard to run the code.
Sub Exporting_Multiple_Sheets()
Application.ScreenUpdating = False
path = ActiveWorkbook.path & "\" & _
Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
For Each ws In Worksheets
ws.Copy
ActiveWorkbook.SaveAs Filename:=path _
& "_" & ws.Name & ".csv", FileFormat:=xlCSVUTF8, _
CreateBackup:=False
ActiveWorkbook.Close False
Next
Application.ScreenUpdating = True
End Sub
- This VBA code automates the process of exporting each worksheet in the active workbook to separate CSV files with names based on the original workbook’s name and the individual worksheet names.
- The newly created CSV formatted files will be placed at the original Excel file’s location.
- Each sheet from the Excel file will be converted to a separate CSV formatted file.
4. Saving in XLS Format Then Converting to CSV Format
- To save your Excel file in XLS format, open the File tab.
- Select This PC as the location from the Save As menu.
- Give the file a name like “Excel CSV”.
- To save the file in XLS format, select Excel 97-2003 Workbook (*.xls) from the list of available file types and click Save.
- As our original workbook was saved in the latest version of Excel, you’ll see a Compatibility Checker window with a warning.
- Click Continue to proceed.
- The file is saved in XLS format.
- Now, we convert it to CSV format. So, again open the File tab.
- Give This PC as the location under the Save As menu.
- Put a name for the file. Like we put “Excel CSV 2” as the file name.
- Choose CSV (Comma delimited) (*.csv) as the file type and click on the Save button to save the file.
- Only this one sheet can be saved in CSV format, thus you’ll receive a warning. Multiple sheets cannot be saved.
- Press OK to continue.
- The active sheet of the Excel document will be saved in CSV format.
- Column C displays question marks in place of special characters since the CSV format can’t identify any special characters.
- You have to save the file in CSV UTF-8 format instead of CSV format to recognize special characters.
Things to Remember
- Generally, the CSV formatted files do not support special characters. If you want to preserve special characters, you have to export your Excel file into CSV UTF-8 format.
- You can immediately save a file in the CSV format with UTF-8 encoding in Excel 2016 and later versions. For previous versions, you have to save it in the Unicode Text format first before converting it to UTF-8 format.
- Using the Save As command you can only convert the active sheet into CSV formatted file. For converting multiple sheets together into separate CSV files use VBA Macro.
- When you use VBA, remember to save your file in XLSM format.
- The keyboard shortcut for opening the Visual Basic window is Alt + F11.
- The keyboard shortcut for running a VBA code is F5.
Conclusion
In conclusion, you have learned several useful methods to convert an Excel file to a CSV file and vice versa through this article. You have also learned about CSV file formats supported in Excel. Please let us know in the comment section if there is any query or suggestions related to this topic.
Export Excel to CSV: Knowledge Hub
- Convert Excel File to CSV Format
- Create CSV File from Excel
- Convert Excel File to Text File with Comma Delimited
- Convert Excel to Comma Delimited CSV File
- Make a CSV File in Excel for Contacts
- Convert Excel Files to CSV Automatically
- Convert Excel to CSV without Opening
- Convert Multiple Excel Files to CSV
- Save Excel File as CSV with Commas
- Set Comma Instead of Semicolon as Delimiter in Excel CSV
- Save Excel as CSV with Double Quotes
- [Fixed!] Excel Not Saving CSV with Commas
<< Go Back to Export Data from Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!