The full form of CSV is ‘Comma Separated Values’. It’s a format where we can see the numbers and texts in plain text. Nowadays, this format is quite popular due to its simplicity. One can easily analyze the data through this format and make required changes. In this article, we’ll show you the effective ways to Convert an Excel File to CSV Format.
Download Practice Workbook
Download the following workbook to practice by yourself.
5 Easy Ways to Convert Excel File to CSV Format
To illustrate, we’ll use the following Excel file as our source. For instance, the file contains the data about Salesman, Product, and Sales of a company. We’ll transform respective Excel worksheets to separate CSV files.
1. Convert Excel to CSV Format Through Save As Command
The easiest method for changing an Excel file is through the Excel File Save As command. Therefore, follow the below steps to Convert an Excel File to CSV Format.
- First, open the Excel workbook and the desired sheet.
- Then, click File.
- As a result, the File window will appear. In the left-most pane, select Save As.
- In the Save As window, click the drop-down icon as shown below and choose the CSV (Comma delimited) option.
- Subsequently, press Save.
- At last, it’ll create a CSV file which is shown in the following picture.
NOTE: After pressing Save, you’ll get a warning dialog box. It reminds you that only the active worksheet will get converted to a CSV file. And, to get all the sheets in CSV format, you’ve to perform the above steps for each worksheet.
2. Transform Excel to CSV UTF-8 without Destroying Special Characters
The above method is simple but it has a drawback. It can’t transform the special characters (Non-ASCII characters). So, learn the steps below to Transform Excel to CSV UTF-8 without Destroying Special Characters.
- In the following dataset, we have a Salesman’s name in Korean.
- Firstly, go to File.
- Afterward, select Save As.
- In the Save As window, select CSV UTF-8 from the drop-down options.
- Next, press Save.
- Consequently, it’ll create a new CSV file for the desired sheet and you’ll see the special character in that CSV file.
3. Excel File to CSV UTF-16 Conversion
Moreover, we can follow a different process to convert Excel files with special characters. Hence, learn the following process to carry out the operation.
- First of all, open the Excel worksheet.
- Press Save As in the File window.
- After that, select Unicode Text from the drop-down list.
- Then, press Save. Hence, you’ll get a .txt file.
- Now, open the text file and click Save As.
- Consequently, a dialog box will pop out.
- Next, type .csv at the end of the file name and choose All Files in the Save as type.
- Select UTF-16 LE in the Encoding field and press Save.
- As a result, it’ll return a CSV file that contains the special characters correctly.
4. Use Google Spreadsheets for Turning Excel Files into CSV
Additionally, we can use Google Spreadsheets for the conversion of Excel files. Now, follow the process below to perform the task.
- Open a blank Google Spreadsheet at first.
- Select Import from the File option.
- Then, select the desired Excel workbook and press Import data.
- Consequently, it’ll open the file in the spreadsheet.
- Now, select File ➤ Download ➤ Comma Separated Values (.csv).
- Next, open the downloaded file.
- Lastly, you’ll get a new CSV file like it’s shown in the following picture.
5. Apply VBA to Change Multiple Excel Sheets to CSV Format
So far, we’ve covered the transformation of a single worksheet to CSV format. But, we can also convert all the worksheets present in an Excel workbook. For that purpose, we have to apply Excel VBA. in our last method, we’ll show you the steps to carry out the operation. So, see the following process.
- In the beginning, select any sheet and right-click on the mouse.
- Then, select View Code.
- As a result, the VBA window will emerge and a dialog box will appear.
- Now, copy the below code and paste it into the dialog box.
Sub MultipleSheetsCSV() Dim st As Worksheet Dim path As String Application.ScreenUpdating = False path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) For Each st In Worksheets st.Copy ActiveWorkbook.SaveAs Filename:=path & "_" & st.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False ActiveWorkbook.Close False Next Application.ScreenUpdating = True End Sub
- Next, press F5 after saving the file.
- In the end, it’ll create separate CSV files for each worksheet in that workbook. In this example, we have 5 Hence, it returns 5 CSV files.
Henceforth, you will be able to Convert an Excel File to CSV Format following the above-described methods. Keep using them and let us know if you have any more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.