CSV Files into Excel (Table of Contents)
Introduction to CSV Files into Excel
MS Excel is an interesting tool when it comes to handling data. Excel data can be imported in many software and even Excel can import data from different sources. Most of the time, we have unstructured data or data in different formats. For that, the solution is Excel. It converts data into a tabular structure. CSV or Comma Separated Values file is a commonly used format to store important data related to statistics, contacts, etc. Importing data makes it easier to read CSV files but sometimes there are unexpected data changes with change in format and data looks scrambled. CSV files can be imported further to many software like SAS, Tableau, etc. for Data analysis and visualization.
There are different techniques for CSV data to look better and organized in Excel. Below are some examples and methods to import and read data in Comma Separated Values format.
Example
Let us suppose we have a text file that has data separated by commas.
- This file is saved as CSV file with .csv extension.
- Now, the CSV file is ready for use. Next, we’ll go to Excel > File > Open > Browse. Then select the saved CSV file as shown below.
- We can see that the CSV file is imported now.
- But the data is not clear. We can apply formatting on the data and see the change as shown below.
Finally, the CSV file is converted into an Excel file and is organized now.
Explanation
CSV files have data separated by commas. These files can be created in Notepad, Excel, etc. CSV files are used in organizations for transmitting and storing data. There are multiple ways for converting CSV files into Excel as given below:
- Directly open CSV files with Excel.
- Opening CSV files through windows explorer option.
- Importing CSV file data into Excel.
CSV files are converted into Excel for supportability as it is hard for Data analysts to draw insights from the data. After conversion, each value separated by commas is placed into each cell separately which makes the data clear. There are multiple advantages of converting CSV files into Excel including
- Better storing of data and calculations as per need.
- Improved data security as Excel files cannot be opened by any other software.
- Saving CSV file in tabular manner with .xlsx extension.
- Data display in a perfect manner with each value in each cell.
- The formatting of data becomes easier and convenient.
- Analysis, Graph plotting, and Visualization on numerical data after conversion of CSV file into Excel.
- Programming and macros using VBA for data development.
Methods to Open CSV Files in Excel
There are various methods of opening CSV files in Excel as mentioned above. We’ll see in detail how these methods work.
4.9 (2,356 ratings)
View Course
1. Opening CSV file in Excel
- CSV files can be opened in Excel and it becomes a new file in Excel later. Suppose we have a CSV file with data separated with commas as shown below. This file is a text file saved with .csv extension.
- Here the first row is the header and below are the data about employees of the company. Each value is separated by a comma. We’ll import the file in Excel by going to Excel now.
- We can Go to File at the top left corner and then Open > Browse as shown below.
- Then we’ll select the file from the location. If we can’t see the file, then at file type “All Excel files” can be changed to “All files” and then select the CSV file as shown below.
- Now, our CSV file is ready in Excel.
- To make the data clear and organized, we can apply some formatting to differentiate the headers from other fields and finally, the file is ready for further use.
2. Using Windows Explorer if we’re using Windows OS
- We’ll have to go to the directory where the CSV file is located and right-click on it. Here our file is located on the desktop. Then go to Open with > Excel and the file will be opened in Excel.
- After selecting Excel, it becomes the default program from then onwards when we click on it, it will be opened in Excel by default. Here is the CSV file open in Excel.
After a bit of formatting, the file is ready to use.
3. Importing CSV File by Importing Data Externally
- This method helps in importing data in the current worksheet but is a bit longer process than the above two processes. First, we need to open MS Excel and select a blank workbook from it. A new workbook is now ready. we’ll place the cursor where we want to import the data.
- Next, we’ll have to go to the “Data” tab then go to “Get External Data” as shown below.
- Then we’ll select “From Text” from the option as the CSV file was created in “Notepad”.
- We have to browse to the location where the file is stored. As we have imported the file earlier, it is showing as a CSV file. If we are using it for the first time also, the process is the same.
- Now, we can see a window which has some options as shown below.
- Then, we have to click on “My data has headers” and choose the file type as “Delimited” and click “Next” as shown below.
- A new window comes as shown below.
Now, untick the “Tab” and select “Comma” and click “Next”.
Then we’ll click on the “Finish” button and a new window is displayed where we need to select the “Existing worksheet”.
- Finally, the CSV file will be imported and the data is displayed below.
- With little formatting, the data can be organized and used further for analysis.
Things to Remember
- Opening a CSV file is easy and it doesn’t change the format of the file to .xlsx or any other file.
- By default, the file is not organized. After importing it we need to apply formatting to make the data look better.
Recommended Articles
This is a guide to CSV Files into Excel. Here we discuss How to open CSV Files in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
