How to Create CSV File from Excel (6 Easy Ways)

CSV, short for Comma Separated Values, is a widely used file format used to store delimited records in plain text. It is often used to transfer data between different applications due it’s small file size and simplicity, especially for large datasets. Let’s examine some easy ways to create CSV files from Excel.

Watch Video – 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, and contains data about the Salesman, Item, and Qty in columns B, C, and D respectively. Here, we have used Microsoft Excel 365 version, however you may use any other version of Excel instead.

Method 1 – Using Save As Command

• Go to the File tab.

• Click on Save As from the menu.
• In the Save As section, select the Browse option.

The Save As dialog box appears.

• Select CSV (Comma delimited) format in the Save as type box.
• Click on the Save button.

Note: After pressing the Save button, you’ll get a warning dialog box reminding you that only the active worksheet will get converted to a CSV file. Perform the above steps for each additional worksheet you want to save.

A CSV file with the same name will be created in the specified location on your PC.

• Navigate to the file and right-click on it.
• From the context menu, tap on Open with.
• Select Notepad from the sub-menu.

The file opens in the Notepad app.

You can also open the same file with Excel. But it won’t look like before. It’ll show the data without any type of formatting.

Method 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). However it is possible to create a CSV file without destroying special characters.

In the following dataset, we have two item names written in Japanese.

Steps:

• Open the Save As dialog box like in Method 1.
• Select CSV UTF-8 (Comma delimited) format in the Save as type box.
• Click on Save.

A CSV file of the current sheet will be created. If you open the file in Excel, you can see that the special characters, which were displayed as . (question marks) in Method 1, now display correctly in the CSV file too.

• Open the file in Notepad and observe the same result.

Method 3 – Applying UTF-16 Encoding

Here’s a different method to create a CSV file with special characters from Excel.

Steps:

A text file will be created with the .txt extension.

• Click on the File tab.
• Select Save As from the options (Press CTRL + SHIFT + S as the keyboard shortcut).

The Save As dialog box of the Notepad app will open.

• In the File name box, add a .csv extension after the file name.
• Select All Files in the Save as type box.
• Select UTF-16 LE in the Encoding box.
• Click the Save button.

A CSV file that contains the special characters correctly is created.

Method 4 – Employing Google Sheets

We can use Google Sheets for the creation of a CSV file from Excel.

Steps:

• Open a blank spreadsheet in Google Sheets.
• Jump to the File tab.
• Click on Import from the available options.

• Select the preferred Excel workbook.
• Click on Import data.

The file will open in the spreadsheet.

• Proceed again to the File tab.
• Click on Download.
• Select Comma Separated Values (.csv) from the options.

• Open the downloaded file.

You’ll get a new CSV file as shown in the following picture:

Method 5 – Implementing VBA Code

You can automate the prior method entirely with the help of VBA.

Steps:

The Microsoft Visual Basic for Applications window appears.

• 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

It’ll create a separate CSV file.

Method 6 – Using an Online Converter

We can also create a CSV file without even opening our Excel file, by means of an online file converter.

Steps:

• Go to the link https://convertio.co/xls-csv/.
• Click on Choose Files.

• Select the Excel workbook you want to convert.
• Select CSV as the conversion option.
• Click on the Convert button.

• Click on the Download button to download the converted CSV file to your PC.

How to Make a CSV File for Contacts

Here, we have a Contact List as an Excel workbook. Let’s make a CSV file from this dataset.

Steps:

• Open the Save As dialog box like before.
• Select the location to save your new CSV file.
• Select CSV UTF-8 (Comma delimited) format in the Save as type box.
• Click on Save.

Download Practice Workbook