How To Convert Excel To CSV
The term CSV stands for Comma Separated Values that is well known and widely used file format. The CSV stores and keep the tabular data containing number and text in the form of plain text. Several applications and programs support the CSV file format because it is an alternative import and export format, and that is the reason behind its popularity and use. But one should know that a CSV file will work on one spreadsheet and cannot work on a cell, row, or column. Thus if you try to save your excel row or column data in CSV format, it is not supportable. The CSV format is the comma separated values file that keeps data in a comma-separated manner simply. Although MS Excel versions support conversion of an excel spreadsheet to a CSV file, there are other online software and applications available that offer the users to convert an excel document to a CSV file by just uploading the file, and the resultant output can be downloaded in just a few seconds of time.
Here, in this section, we will discuss different CSV formats because not only one, but there are a few more CSV different formats. Also, we should know why there is a need to convert an excel document to a CSV file and why it is so popular. With this, we will see out the practical implementation of each step where we will take an excel spreadsheet and convert its data to CSV. We will also look at some online modes of converting an excel file to a CSV file format.
Types of Formats
Usually, a user can convert an excel spreadsheet into two different formats:
1) Delimited Text Files or (.txt): In such type of file format, an excel sheet data is converted and separated by a TAB character. It is supportable on all types of operating systems and can support every modern language.
2) Comma Separated Values Text Files (.csv): In such type of file format, an excel sheet data is converted and separated by a comma character.
In CSV text files, it has further categorized formats which are:
CSV (comma delimited) Format: It saves the excel file as a comma-separated text and can be used in another text editor.
CSV (Macintosh): Its changes can be seen in the MAC OS because it saves the excel file as a comma-separated file to be used in Mac systems.
CSV (MS-DOS): Its changes can be seen in the MS-DOS because it saves the excel file as a comma-separated file to be used in MS-DOS systems.
We can change as per our requirement, and the conversion from excel to a text file is known as Export Data.
Why we need to convert excel to CSV
Because of the following reasons, there is a need to convert an excel spreadsheet to CSV file format:
- CSV format is simple and supportable by all spreadsheets and database systems.
- CSV format allows easy transfer of data across different software and applications.
- CSV files are the plain-text files that make it easy for web developers to create, develop, organize a large amount of data, and import data to an excel sheet or database.
Converting Excel to CSV
One needs to follow the below steps for converting an excel worksheet to a CSV file format:
Step 1: Open MS Excel on your computer system either by directly searching on the search tab or using the MS Excel icon, if present on the desktop. A snippet is shown below:
Step 2: If you have an existing excel file, open it on your system by using file> Open and browse for the file and click Open (or direct press Ctrl + O) or create a new excel sheet. We have opened an existing excel file, as you can see in the below snapshot:
Step 3: If you have created a new file, first save it using Ctrl + S or Go to File > Save and give it an appropriate name and save it at your preferred location on your system. If you have opened an existing file, skip this step.
Step 4: Now, Go to File > Save as, and the Save As dialog box will open as shown below:
Step 5: In the File name, give a new name for the file (if needed), and in Save as type, click on the drop-down arrow (facing downward), and a list of options can be seen from which select CSV (comma delimited) as shown in the below snapshot:
Step 6: Click Save or press Enter and choose the location where you want to save the file and click OK, and the file will be saved as shown below:
Step 7: The file will be converted to a CSV file.
How to Check for the conversion
In order to check whether the excel file is successfully converted to CSV file format, proceed with the following described steps:
1) Move to the location where the file you saved and right-click on it, and a list of options will open. Select Properties and click on it. A snapshot is shown below:
2) Within type of file, you can see the file type as Microsoft Excel Comma Separated Values File (.csv) and can see in the below snapshot also:
3) It means our excel sheet was successfully converted to CSV file format.
4) Now, to open the CSV converted file, right-click on the file and open it with Notepad or any other text editor and you can see that each cell data is separated via comma in between them, and you will get the below-shown snapshot:
Importing CSV To Excel
When a user wants to open the converted CSV file in the excel worksheet, the user needs to import the CSV file in MS Excel, and the process is called Import. In order to import a CSV file in excel, there are two methods:
Method 1: Direct Open
1) Open MS Excel on your computer system either by directly searching on the search tab or using the MS Excel icon, if present on the desktop. A snippet is shown below:
2) Click on file> Open or press Ctrl + O, and the Open dialog box will appear, from which move to the location and select the converted CSV file. Click on Open or hit Enter. The excel will load the file, as you can see in the below snapshot:
Point To be noted:
It may happen the converted CSV file may not be found at its location when using Ctrl + O in MS Excel. It is because the Open box filters the file type as per the application. Thus, it displays only Excel files by default. In the File Type (next to the File name), change it to All Files instead of All Excel Files as shown below:
The converted CSV file is now visible, as you can see in the above snippet.
Method 2: Using Import Text Wizard
The import text wizard method allows the user to more flexibly convert columns to different data formats because directly opening the CSV file makes use of the current default data format settings in order to know the way to import each column of data. To use the Import text wizard, follow the below steps:
1) Open MS Excel and go to Data which is next to ‘Formulas’ as you can see in the below snapshot:
2) Under Get External Data clipboard, click on From text, as you can see in the below snapshot:
3) The Import Text File dialog box will open, move to the location and select the CSV file and hit Import, as you can see in the below snapshot:
4) The text import wizard will open as shown in the below snapshot with a three-step cycle:
- In Step 1 of 3, you can choose the file type you want and click on Next.
- In Step 2 of 3, you can set the delimiters of the data and click on Next, as you can see in the below snapshot:
- In Step 3 of 3, you can select each column and set the Data Format you want, and click Finish, as you can see in the below snapshot:
We hadn’t made any changes to our file.
5) Next, it will ask, “Where do you want to put the data?” e., either to the Existing worksheet or to the New worksheet and hit OK. A snapshot for the same is shown below:
We have chosen the New worksheet for our file.
6) The CSV file opens up in the excel worksheet in the type of format you selected, as you can see in the below snapshot:
Following all these above steps, we can easily convert an Excel worksheet to a CSV file.
Online Excel To CSV
Other than the methods that are described above, we can also convert an excel worksheet to a CSV file online. There are several online converting software available through which we can convert an Excel worksheet to a CSV file. Some of the online Excel to CSV converters are:
- ZAMZAR
- com
- co
- com, etc.
To use these online modes of conversion, one needs to follow the below-described steps:
Step 1: Connect your computer with an internet connection as we need to convert the worksheet online.
Step 2: Open a web browser on your system such as Google Chrome, and on the search bar, type ‘online excel to csv convertor’ as you can see in the below snapshot:
Step 3: Several links will open. Select any of them. A snapshot is shown above.
Step 4: Next, the selected website will open as you can see in the below snapshot that we have opened cloudconvert.com on our system:
Note: The theme and appearance of different websites may vary, but the process will be the same almost for every website or software.
Step 5: Click on Select File and select an Excel file that you want to convert to CSV file format, and click Open. A snapshot for the same is shown below:
Step 6: It will ask to select a format in which you want to convert the selected file like here, we have the following shown formats:
As we need to convert the selected excel file to CSV, no need to make any change and click on ‘Convert’ as you can see in the below snapshot:
Step 7: As soon you click on the Convert button, the file will begin to upload and will get converted to CSV format, and after the successful conversion, either it will begin automatic download or will let you download the converted file by clicking on the Download button as we got in the below snapshot:
Step 8: The converted file will get downloaded to your default Downloads folder, and you can find your file there.
These are the ways to convert an excel worksheet to a CSV file format.