Convert Excel to CSV

Convert Excel to CSV (Table of Contents)

• Convert Excel to CSV File
• How to Convert Excel to CSV?

Convert Excel to CSV File

While saving files we save in different formats like word, powerpoint, Excel, Text, and Macro enabled book, etc. CSV is one of the formats available for saving files in Excel. Before learning how to convert an Excel file to CSV first we will understand what is the full form of CSV and its importance.

Full form of CSV is Comma-Separated Value. Why it is called as Comma separated value is because each column in CSV is separated by a Comma. It stores the data in table format as plain text. As it stores data as plain text, various software programs and applications for import and export. It will organize better when working with huge data.

How to Convert Excel to CSV?

Let’s understand how to convert Excel to CSV and different kinds of Excel CSV formats by using some examples.

Example #1 – Conversion of Excel to CSV

Consider a file which has data with Names, Age and Salary as shown in the below screenshot.

Once data is inserted try to save the file with Save As option. Select the path where you want to save the file and select the Save as type as CSV (Comma delimited) which is highlighted.

Give file name as per your requirement and save it. Here I have given Name as Convert Excel to CSV.

Now we will see how it is saved. Observe the file which is saved in the format of CSV (Microsoft Excel Comma Separated Value File) is our CSV file. We can observe the icon is also different from Excel file.

Now we saved the file in CSV but if we observe there is “(Comma delimited)”, similarly we have some other formats of CSV files. We will see each of the Excel CSV file and what is the meaning and purpose of different formats.

There are 4 different formats of CSV file. They are Comma delimited, Macintosh, MS-DOS and UTF – 8.

We can observe different CSV formats in Excel. Now we will see an explanation for each of them.

1. CSV (Comma delimited) – This format converts the data to plain text separated by commas and this format files can be used by another Windows Operating System.
2. CSV (Macintosh) – This format files also save as same as comma-separated but can use this in Mac operation system.
3. CSV (MS-DOS) – This format file can be used in MS-DOS operating system.
4. CSV UTF-8(Comma Delimited) – UTF-8 uses 1 to 4 bytes for each symbol.

When we save a file in CSV format it will only save the currently active tab of the excel. If we want all the sheets in Excel into CSV format, we need to do it individually. Regarding this, we may get the message while saving the file in the form of warning pop up.

In addition to this we may get another pop-up message stating that our saved file has features that may not be compatible with CSV format, do you want to save it? Just click Yes to proceed further.

Hope you have understood how to convert Excel to CSV and what are the different CSV formats available.

If we want to see the CSV in text format in case if it opens in Excel, select the file and right click then choose the option Open with.

Choose Notepad and see how the data stores in the CSV separated by commas. Observe every column is separated by Comma.

Open a CSV file in Excel

We can access our CSV files from our Excel spreadsheets directly. We will see how we can do that.

Open an excel file and click on the File menu then the below screen will appear. From that, Choose Open option from the list of options. It will take to share drive path go to the path where our file is saved and select .CSV file.

Then the file opens as shown in the below screenshot.

If we observe the salary of A is showing as 1E+11 because as we directly opened CSV in excel it converts to exponential format in case if any number starting with Zeros, then it will convert to mathematical format, so it will remove the Zeros and will display only the other part of the number.

Example #2 – Conversion of Excel to CSV with UTF-8

First, we will understand why we need to use UTF-8. When excel spreadsheet has foreign characters and if we follow the same process of “save as” in the CSV format it may not work because that way is suitable for ASCII characters if the text is apart from the ASCII characters that are in a foreign language it may distract the data.

UTF-8 uses 1 to 4 bytes for each symbol. It is most useful when your file has ASCII characters as it stores in one byte each. To convert the file with foreign language characters, follow the below procedure. Suppose we have a file which has text in the Chinese language as shown in the below screenshot.

To convert to CSV first save the file in the Unicode Text format as shown in the below screenshot.

Open the file it will look like the below text file.

If we observe the above screenshot each column is separated by a tab space. In CSV file each column is separated by “,” character.

So, we need to replace the space with the “,”. Copy the space between two columns.

Press Control+H to find and replace the text. In “find what” give the control v to paste the copied space. In Replace with option input “,” and click on the option Replace All.

Once we click on Replace All, space will be replaced by “,”.

Now save the file with “.CSV” format and change the encoding option to UTF-8. Change the save as type as All files.

Things to Remember About Convert Excel to CSV

• Full form of CSV is Comma-Separated Value.
• To convert an Excel to CSV, use the Option save as while saving the file and select the format as CSV.
• There are 4 different CSV formats, they are CSV (Comma delimited), CSV (Macintosh), CSV(MS-DOS) and CSV UTF-8.
• We can convert all sheets of excel workbook at a time.

Recommended Articles

This is a guide to Convert Excel to CSV. Here we discuss how to Convert Excel File to CSV along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. How to Import Data in Excel?
2. Apply Table styles in Excel
3. How to use Excel Toolbar?
4. Guide to Excel Rows and Columns