Convert Excel To CSV

Converting Excel To CSV File

Comma Separated Values or CSV is one of the file formats used to save an Excel workbook. It stores the dataset containing numerical values, text, and special characters in a plain text format. This format enables users to handle large data and quickly detect issues. In Excel, the data is distributed and stored in cells, whereas when we Convert Excel to CSV format, the tabular data changes to the text format, and each line of the data is stored in separate lines.

For example, the below Excel table contains rank details of 8^th-grade students.

When we Convert Excel to CSV, we save it as a CSV file. Then, the above data is converted to a comma-separated text file in a Notepad, as shown below.

[Note: Once we Convert Excel to a CSV file, the columns get separated by commas, making the data easier to interpret].

The four Excel-supported CSV file formats for different Operating Systems that allows us to save and Convert Excel to CSV format without errors are:

• CSV (Comma delimited) – It saves Excel data in a CSV file. We can use the saved CSV file in other Windows applications.
• CSV (Macintosh) – It saves the Excel file as a CSV file, compatible with the Mac OS.
• CSV (MS-DOS) – It saves the Excel data in a CSV file, compatible with MS-DOS OS.
• CSV UTF-8 (Comma delimited) – UTF-8 or the Unicode Transformation Format 8-bit is a Unicode encoding form that supports special characters and comes with backward compatibility with ASCII. While this format works well for data containing ASCII characters, it is also suitable for files with non-ASCII data, as it will keep them undistorted.

These formats are available while choosing the file type for saving our Excel, as shown below. In addition, the saved file will have the .CSV extension.

Apart from these four CSV file formats, we have another file format, i.e., the Unicode Text (highlighted in the above image). While all operating systems support this file type, it can manage the most modern language symbols and ensure they remain undamaged in the converted file.

How To Convert Excel To CSV?

A few ways to Convert Excel to CSV are as follows:

1. Converting Of Excel Files Into CSV.
2. Converting Of Excel Files Into CSV Using UTF-8.
3. Exporting Excel Files Into CSV Using UTF-16.

#1 – Converting Of Excel Files Into CSV

We will Convert Excel to CSV in the standard or basic method.

In the following table, the data is,

• Columns A and B contain Value 1 and Value 2.
• Column C contains the Mathematical Operations or Formulas.
• Column D contains the Comments in the form of bullets.

The steps to Convert Excel to CSV are as follows:

Step 1: Select the “File” tab > click the “Save As” option.

Step 2: Choose the location to save the converted file > enter the file name in the “File_name:” field > choose the “Save as type:” as “CSV (Comma delimited)” from the drop-down list.

Step 3: Click the “Save” button. Immediately, we will get the below warning message if our Excel has multiple worksheets. Click “OK” to complete the action.

[Special Note: In some scenarios, if our active worksheet has formulas, charts, specific formats, or objects, we may get another warning message, as shown below.]

• Click Yes, to Convert Excel to CSV without the unsupported features.
• Click No and ensure the data format is CSV-supported before we Convert Excel to CSV file].

After Converting Excel to CSV File, double-click to open the CSV file, then the output will be as shown in the image below, without any formatting or formulas, and the bullets appear as ‘?’.

To view the output in the Notepad, right-click on the CSV file > choose Open with > Notepad.

The output of the converted CSV file is shown in the image below. [The commas separate the columns and the column values, and the bullets are replaced with the ‘?’ symbol].

#2 – Converting Of Excel Files Into CSV Using UTF-8

If our worksheet contains special symbols or foreign text, the method to convert Excel using CSV (Comma delimited), might not work. The reason is that the CSV format alters non-ASCII characters. In such cases, we Convert Excel to CSV online and save the file in a format that utilizes a Unicode character encoding.

One of the Unicode encoding forms is UTF-8. It involves encoding a character with one to four bytes. We can use it when our spreadsheet predominantly has ASCII characters, as each symbol requires only a single byte for storage.

In the following table, the data is,

• Column A contains the ASCII Characters.
• Column B contains the ASCII Codes.

The steps to Convert Excel to CSV using UTF-8 are as follows:

Step 1: Select the “File” tab > click the “Save As” option.

Step 2: Choose the location to save the converted file > enter the file name in the “File_name:” field > choose the “Save as type:” as “CSV UTF-8 (Comma delimited)” from the drop-down list > click the “Save” button.

After we Convert Excel to CSV online, the CSV file in Excel will appear as below:

Step 3: To view the output in the Notepad, right-click on the CSV_UTF-8 file > choose Open with > Notepad.

The output of the converted CSV file is shown in the above image. [The columns and their values are separated by commas. However, all the symbols remain unchanged].

[Note: As all the symbols are ASCII characters, the encoding is the same as an ASCII file when using the CSV UTF-8 (Comma delimited) file type to save our spreadsheet].

#3 – Exporting Excel Files Into CSV Using UTF-16

UTF-16 is another Unicode encoding form that takes two to four bytes to encode each character. We can use this file type when our worksheet has data containing symbols requiring two to four bytes for encoding and we want to Convert Excel to CSV.

In the following table, the data is,

• Column A contains the Countries.
• Column B contains the Currency.
• Column C contains the Symbols of the currencies.

The steps to Convert to CSV using UTF-16 are as follows:

Step 1: Select the “File” tab > click the “Save As” option.

Step 2: Choose the location to save the converted file à enter the file name in the “File_name:” field > choose the “Save as type:” as “Unicode Text” from the drop-down list > click the “Save” button.

It will be a text document.

Step 3: To view the output in the Notepad, right-click on the CSV_UTF-16 file > choose Open with > Notepad.

Next, select the “Edit” menu > click the “Replace…” option.

Step 4: The “Replace” window pops up. Copy the tab space from the Notepad data and paste it into the “Find what:” field, as shown below > enter the ‘,’ symbol in the “Replace with:” field. Finally, click “Replace All” to complete the action.

Step 5: Save the file with the extension .CSV, with the Encoding set to UTF-16 LE.

We will now see a CSV file.

Once we save the file, the output in Excel and Notepad is shown in the images below.

The UTF-16 Unicode encoding form ensures the foreign characters remain undistorted in the converted file.

Important Things To Note

• When we Convert Excel to CSV, we get a file supported by different programs as an alternative import or export format, containing easy-to-review content.
• In an Excel workbook, only the active or current worksheet gets converted to CSV at a time.
• When using the UTF-16 Unicode encoding file format, save the Excel as Unicode Text. Then replace tab spaces with commas and save it with a .CSV extension and Encoding set as UTF-16 LE.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand Convert Excel to CSV, with its formula and examples. We can download the template here to use it instantly.

Recommended Articles

This has been a guide to Convert Excel to CSV. We explain top 3 methods of converting it using comma delimited, UTF-8, UTF-16, with examples & a downloadable template. You can learn more from the following articles –

• Add-ins In Excel
• XML in Excel
• Split Cells in Excel