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:
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.
A few ways to Convert Excel to CSV are as follows:
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
We will Convert Excel to CSV in the standard or basic method.
In the following table, the data is,
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.]
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].
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,
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].
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,
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.
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.
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).
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.
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.
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 –
