Convert Excel To CSV

Converting Excel To CSV File

Comma Separated Values or CSV is one of the file formats 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 8th-grade students.

Convert Excel to CSV Intro

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.

Convert Excel to CSV Intro Example

[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.

format

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.

Key Takeaways
  • If we Convert Excel to CSV Format, our Excel table dataset changes and gets stored as plain text in a comma-separated manner, thus making the data easy to interpret and fixing errors.
  • CSV (Comma delimited), CSV (Macintosh), CSV (MS-DOS), and CSV UTF-8 (Comma delimited) are the four CSV formats we can use to convert our Excel to CSV.
  • We must choose the right CSV file type to match the Excel data and format. If our Excel data contains special characters or foreign symbols, use Unicode encoding forms, UTF-8 or UTF-16.

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.
Example 1

The steps to Convert Excel to CSV are as follows:

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

File Tab
Example 1.1

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.

Convert Excel to CSV Example 1.2

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.

Example 1.3

[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.

Example 1.3 - 1
  • 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 ‘?’.

Example 1.4

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

Convert Excel to CSV Example 1.5

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].

Example 1.6

#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.
Convert Excel to CSV Example 2

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

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

File Tab
Example 1.1

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.

Example 2.2

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

Example 2.3

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

Convert Excel to CSV Example 2.4

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.
Convert Excel to CSV Example 3

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

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

File Tab
Example 1.1

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.

Convert Excel to CSV Example 3.1

It will be a text document.

Convert Excel to CSV Example 3.2

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.

Example 3.3

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.

Convert Excel to CSV Example 3.4

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

Convert Excel to CSV Example 3.5

We will now see a CSV file.

Convert Excel to CSV Example 3.6

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

 Example 3.7

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)

How to Convert Excel to CSV with delimiter?


We can Convert Excel to CSV with a delimiter, say a comma, using the below steps:

Step 1: In the Excel file, choose File > Save As.

File Tab
Example 1.1

Step 2: Decide the location where we want to store the CSV file, enter the required file name and choose the Save as type as CSV (Comma delimited).

Convert Excel to CSV Example 1.2

Once we click on Save, the Excel will change into a CSV file.

How do I convert an Excel file to CSV without formatting?


We can convert Excel to CSV without formatting by following the below steps:

1. In the Excel file, navigate through File à Save As and choose the Save as type as the required CSV file format.
2. We may get a warning message that we might lose some features or formatting of the Excel when the file gets converted into a CSV file.
3. Click Yes to change the Excel file to CSV without formatting.

How do I Convert Excel to CSV with commas?


We can Convert Excel to CSV with commas by first applying the CSV formatting rule of ensuring the fields containing commas begin and end with double quotes.

We can then use the required CSV file format to change the Excel to a CSV file.

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published.