While working in Excel, you may often need to deal with a CSV file. This might be opening a CSV file in Excel with keeping columns, converting a CSV file to an Excel file, creating connections, or something else. However, reading or opening the CSV file in Excel is essential to utilize the Excel features over the text-based data within such a type of file. In this article, I’ll discuss 4 ways on how to read a CSV file in Excel with proper explanation. So, you can adjust the ways for your file.
Download Practice Workbook
What Is CSV File?
CSV, simply refers to Comma Separated Values, is a text-based file that stores data. It is one of the common formats that is used to exchange data from one application to another application. For example, .shp file (extension of a geographical application namely ArcGIS) is not supported in Excel. But you can easily convert the .shp to .csv file (.csv is the extension of a CSV file). Then, open the CSV file in Excel and you can work with the converted data.
By default, comma is the delimiter in the CSV file. However, you can use other space characters e.g. tab, semicolon, and so on.
4 Ways to Read CSV File in Excel
For better understanding, let’s see an example of a CSV file stored in E:\Exceldemy.
If you open the Sales Report.csv with Notepad ( a dedicated text editor application owned by Microsoft) , you’ll get the following data in text format. Here, Sales Report (title of the dataset) of some Product Items is given along with Product ID, Sates, and Sales. And, comma is the space delimiter. Surely, this is the structure of a CSV file.
Now, you have to read the CSV file in Excel using the following ways.
1. Open CSV File Directly
If you already have installed the Excel application, you can open the CSV file directly. Please follow the below steps.
- For doing this, just open the Windows File Explorer and go to the existing CSV file.
- Then, select the file and double-click over the file.
Alternatively, you can open the file after opening a blank workbook in Excel.
- Just, go to File > Open > This PC.
- Then, go to the path (file location) of the existing CSV file and make sure that you have selected the File Format as Text Files.
- Immediately, you’ll see the Sales Report.csv file. Now, just click over the Open button.
After using the above two methods (opening the File Explorer or Excel workbook), you’ll get the following output.
Finally, the output will look as follows after applying the necessary formatting.
2. Open with Excel Application
If you don’t have the Excel application installed or Excel is not the default app for opening .csv files, this method will be beneficial for you.
- Firstly, install the Excel application (also, you can use the web version of Excel for free).
- After installing the application, select the CSV file and right-click.
- Then, go to Open with (pick this option from the Context Menu) > Choose another app.
Shortly, you’ll see the following options and choose the Excel application. Also, check the box before Always use this app to open .csv files for opening the file directly as shown in the first way.
- How to Import CSV into Existing Sheet in Excel (5 Methods)
- Excel VBA: Read Text File into String (4 Effective Cases)
- Excel VBA to Read CSV File Line by Line (3 Ideal Examples)
- How to Convert CSV to XLSX Command Line (with Easy Steps)
- Excel VBA to Read CSV File into Array (4 Ideal Examples)
3. Using From Text/CSV Feature (Power Query) to Read CSV File
If you have a larger dataset (in .csv format) and you want to read the CSV file safely, you can utilize the From Text/CSV option, one of the features of Power Query, a data transformation and preparation engine in Excel.
- Firstly, go to the Data tab > From Text/CSV.
- Next, click over the Sales Report.csv file and then pick the Import button.
- Then, you’ll see a preview of the text file where the Comma is fixed as the Delimiter automatically.
- Furthermore, if you want to load the converted data into a working sheet, select the Load to option.
Lastly, check the circle before the New worksheet option if you want to load the data in a new worksheet. Otherwise, specify the location under the Existing worksheet.
Finally, you’ll get the following output.
4. Utilizing Text to Columns to Read CSV File
If you have other space delimiters (e.g. tab or semicolons) inside your CSV file or if you’re a user of the Excel web version, this method might be fruitful.
- Primarily, you have to select and copy the texts by pressing CTRL + C.
- Next, just go to the B2 cell and press CTRL + V to paste the texts.
- Now, go to the Data tab > Data Tools ribbon > choose the Text to Columns feature.
- In step 1 of 3, you have to choose the Delimited data type.
- Then, (in step 2 of 3), pick the Comma as Delimiters.
Note: If you have the other space delimiters, choose that instead of the Comma.
- In the last step, you have to keep the General data format checked.
Eventually, you’ll get the following output.
That’s the end of today’s session. Certainly, I believe that you can utilize the above methods to read the CSV file in Excel. Anyway, don’t forget to share your thoughts in the comments section below.