How to Read CSV File in Excel (4 Fastest Ways)

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.

Dataset1

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.

Dataset in 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.

How to Read CSV File in Excel Opening CSV File Directly

Alternatively, you can open the file after opening a blank workbook in Excel.

  • Just, go to File > Open > This PC.

Opening an Excel Workbook

  • 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.

How to Read CSV File in Excel Opening CSV File Directly

After using the above two methods (opening the File Explorer or Excel workbook), you’ll get the following output.

How to Read CSV File in Excel Opening CSV File Directly

Finally, the output will look as follows after applying the necessary formatting.

How to Read CSV File in Excel Opening CSV File Directly

Read More: How to Open CSV File with Columns in Excel (3 Easy Ways)


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.

How to Read CSV File in Excel Opening with Excel Application

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.

Choosing the Default App

Read More: How to Open Notepad or Text File in Excel with Columns (3 Easy Methods)


Similar Readings


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.

Opening the From Text/CSV Feature

  • Next, click over the Sales Report.csv file and then pick the Import button.

Import the CSV File

  • 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.

How to Read CSV File in Excel Using From Text or CSV Feature (Power Query)

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.

Specify the Location

Finally, you’ll get the following output.

Read More: How to Import Text File to Excel Using VBA (3 Easy Ways)


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.

How to Read CSV File in Excel Using Utilizing Text to Columns

  • Next, just go to the B2 cell and press CTRL + V to paste the texts.

Paste the Texts

  • Now, go to the Data tab >  Data Tools ribbon > choose the Text to Columns feature.

How to Read CSV File in Excel Using Utilizing Text to Columns

  • In step 1 of 3, you have to choose the Delimited data type.

Text to Columns Wizard

  • Then, (in step 2 of 3), pick the Comma as Delimiters.

How to Read CSV File in Excel Using Utilizing Text to Columns

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.

Text to Columns Wizard

Eventually, you’ll get the following output.

How to Read CSV File in Excel Using Utilizing Text to Columns

Read More: How to Convert CSV to Excel with Columns (5 Methods)


Conclusion

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.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo