Shahriar Abrar Rafid
CSV, also known as Comma Separated Values, is a widely used format generally required for different data analysis software. It’s a format where we can see the numbers and texts in plain text. In addition to that, it is easy to organize and edit for high adaptability. Nowadays, this format is quite popular because of its simplicity. There is a significant possibility that you may run into situations where you need to create a CSV file from Excel. In this article, we’ll show you 6 easy and quick ways to create a CSV file from Excel.
Download Practice Workbook
You may download the following Excel workbooks for better understanding and practice yourself.
6 Methods to Create CSV File from Excel
To illustrate, we’ll use the following source file as our dataset. It’s a daily Sales Report of a particular fast food store. For instance, the dataset contains data about the Salesman, Item, and Qty in columns B, C, and D correspondingly.
Now, we’ll create a CSV file from the respective dataset in numerous ways. So, let’s explore them one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
1. Using Save As Command
The easiest method for creating a CSV file is using the Save As command in Excel. Therefore, follow the below steps to create a CSV file from Excel.
- At the very beginning, go to the File tab.
- Then, click on Save As on the menu.
- In the Save As section, select the Browse option.
Immediately, the Save As dialog box appears before us.
- Here, choose CSV (Comma delimited) format in the Save as type box.
- After that, click on the Save button.
Note: After pressing the Save button, 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.
Accordingly, it’ll create a CSV file with the same name in the desired location on your PC.
- Currently, right-click on the file.
- From the context menu, tap on Open with.
- Then, select Notepad from the sub-menu.
Instantly, it’ll open the CSV file in the Notepad app.
Also, you can open the same file with Excel. But it’ll not look like before. It’ll show the data without any type of formatting.
Read More: How to Save Excel File as CSV with Commas (3 Suitable Methods)
2. Utilizing CSV UTF-8 to Create CSV File Keeping 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 create a CSV file without destroying special characters.
📌 Steps:
In the following dataset, we’ve two item names written in Japanese.
- First of all, open the Save As dialog box like Method 1.
- Then, choose CSV UTF-8 (Comma delimited) format in the Save as type box.
- After that, click on Save.
Consequently, it‘ll create a CSV file for the desired sheet. And you can see the special character in the CSV file also, which was shown as ??? (question mark) in Method 1.
- Following this, open the file in Notepad and see the same result here.
Read More: Save Excel as CSV with Double Quotes (3 Simplest Methods)
3. Applying UTF-16 Encoding
Moreover, we can follow a different process to create a CSV file with special characters from Excel. Hence, learn the following process to carry out the operation.
📌 Steps:
- Like before, get the Save As dialog box.
- Here, choose Unicode Text format in Save as type.
- Therefore, tap the Save button.
Thus, it’ll create a text file with the .txt extension.
- Then, open it in Notepad.
- Next, move to the File tab.
- Following this, select Save As from the options (Press CTRL + SHIFT + S as the keyboard shortcut).
Instantly, it’ll open the Save As dialog box of the Notepad app.
- In the File name box, add a .csv extension after the file name.
- Then, select All Files in the Save as type box.
- After that, choose UTF-16 LE in the Encoding box.
- As usual, press the Save button.
As a result, it’ll return a CSV file that contains the special characters correctly.
Read More: Convert Excel to Comma Delimited CSV File (2 Easy Ways)
Similar Readings
- How to Keep Leading Zeros in Excel CSV Programmatically
- [Fixed!] Excel Not Saving CSV with Commas (7 Possible Solutions)
4. Employing Google Sheets
Additionally, we can use Google Sheets for the creation of a CSV file from Excel. It’s simple and easy, just follow along.
📌 Steps:
- At first, open a blank spreadsheet in Google Sheets.
- Secondly, jump to the File tab.
- Thirdly, click on Import from the available options.
- Then, select the preferred Excel workbook.
- After that, click on Import data.
Consequently, it’ll open the file in the spreadsheet.
- Again, proceed to the File tab.
- At this time, click on Download.
- Then, select Comma Separated Values (.csv) from the options.
- Next, open the downloaded file.
- Lastly, you’ll get a new CSV file like it’s shown in the following picture.
Read More: How to Convert Excel Files to CSV Automatically (3 Easy Methods)
5. Implementing VBA Code
Have you ever thought of automating the same boring and repetitive steps in Excel? Think no more, because VBA has you covered. In fact, you can automate the prior method entirely with the help of VBA. Let’s see it in action.
📌 Steps:
- Firstly, go to the Developer tab.
- Then, click on Visual Basic on the Code group.
- Alternatively, press ALT + F11 to replicate the task.
Immediately, the Microsoft Visual Basic for Applications window appears.
- Here, move to the Insert tab.
- Then, select Module.
- Therefore, paste the following code into the module.
Sub create_csv_from_excel()
Dim wst As Worksheet
Dim path_x As String
Application.ScreenUpdating = False
path_x = ActiveWorkbook.Path & " " & Left(ActiveWorkbook.Name, _
InStr(ActiveWorkbook.Name, ".") - 1)
For Each wst In Worksheets
wst.Copy
ActiveWorkbook.SaveAs Filename:=path_x & "" & wst.Name & ".csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
Next
Application.ScreenUpdating = True
End Sub
- Now, Run the code.
In the end, it’ll create a separate CSV file.
- Thus, open the file.
Read More: How to Write to a Text File Using Excel VBA
6. Handling Online Converter Without Opening File
Here, we will use another method to create a CSV file from Excel by using an online website converter. Let’s walk through the steps to learn it.
📌 Steps:
- Initially, go to the link https://convertio.co/xls-csv/.
- Then, click on Choose Files.
- After that, select the right Excel workbook.
- Additionally, select CSV as the conversion option.
- Lastly, click on the Convert button.
- Finally, click on the Download button to download the converted CSV file to your PC.
- Similarly, open can open it in either Notepad or Excel.
Read More: How to Convert Excel to CSV without Opening (4 Easy Methods)
How to Make a CSV File in Excel for Contacts
In this section, we’ll get to learn how to make a CSV file for contacts in Excel. So, without further delay, let’s dive in!
📌 Steps:
Here, we’ve got a Contact List in our hands as an Excel workbook.
To make a CSV file from this dataset, follow the steps below.
- Primarily, open the Save As dialog box like before.
- Secondarily, choose the right file location where you want to save your new CSV file.
- Then, choose CSV UTF-8 (Comma delimited) format in the Save as type box.
- Finally, click on Save.
- Hence, open the new file in Notepad.
Conclusion
That’s all from me today. This article explains how to create a CSV file from Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more. Happy Excelling ☕.
