CSV Files into Excel (Table of Contents)
Introduction to CSV Files in Excel
MS Excel is an interesting tool when it comes to handling data. Many software applications can import Excel data, and Excel can import data from various 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. People commonly use the CSV or Comma Separated Values file format to store important data such as statistics, contacts, etc. Importing data makes it easier to read CSV files. Sometimes unexpected data changes can lead to format changes which can scramble the data. Users can import CSV files into software like SAS, Tableau, and others for data analysis and visualization.
There are different techniques for CSV data to look better and be organized in Excel. Below are examples and methods to import and read data in Comma Separated Values formats.
Example
Suppose we have a text file with data separated by commas.
- This file is saved as a CSV file with a .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 to the data and see the change below.
Finally, we convert and organize the CSV file into an Excel file.
Explanation
CSV files are used in organizations for transmitting and storing data. CSV files have data separated by commas. These files can be created in Notepad, Excel, etc. There are multiple ways to convert CSV files into Excel, as given below:
- Directly open CSV files with Excel.
- Opening CSV files through the 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.
- Any other software that cannot open improved data security as Excel files.
- Saving CSV files in a tabular manner with .xlsx extension.
- Data is display in a perfect manner with each value in each cell.
- The formatting of data becomes easier and more convenient.
- Analysis, Graph plotting, and Visualization of numerical data after conversion of CSV file into Excel.
- Programming and macros using VBA for data development.
Methods to Open CSV Files in Excel
As mentioned above, various methods exist for opening CSV files in Excel. We’ll see in detail how these methods work.
1. Opening CSV file in Excel
- CSV files can open in Excel, and it becomes 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 a .csv extension.
- We’ll import the file in Excel by going to Excel now. The first row is the header, and the data about the company’s employees are below. A comma separates each value.
- 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. Then go to Open with > Excel, and the file will be opened in Excel. Here our file is located on the desktop.
- After selecting Excel, it becomes the default program; 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 import data in the current worksheet but is longer than the above two processes. First, we need to open MS Excel and select a blank workbook. 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 and 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. The process is the same if we use it for the first time.
- We can see a window with some options, as shown below.
- Then, we have to click on “My data has headers,” 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 will be displayed where we must select the “Existing worksheet”.
- Finally, the CSV file will be imported, and the data will be displayed below.
- The data can be organized and used further for analysis with little formatting.
Things to Remember
- Opening a CSV file is easy; it doesn’t change the file format 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 in Excel. Here we discuss How to open CSV Files in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
