How to Read CSV File in Excel (5 Quick Tricks)

Get FREE Advanced Excel Exercises with Solutions!

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. So, knowing how to read CSV files in Excel is crucial for us.
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, we’ll walk through 5 easy methods for reading CSV files in Excel and provide some tips for working with them. Whether you’re a data analyst, researcher, or business user, learning how to read CSV files in Excel can help you gain valuable insights and make data-driven decisions.

how to read csv file in excel


What Is CSV File?

CSV, simply referred to as 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, the comma is the delimiter in the CSV file. However, you can use other space characters e.g. tab, semicolon, and so on.
There are four categories of CSV files:

  • CSV UTF-8 (Eight-bit Unicode Transformation Format) (Comma delimited)
  • CSV (Comma delimited)
  • CSV (Macintosh)
  • CSV (MS-DOS)

Each format has a specific purpose, although Microsoft Excel is compatible with all four. The ability to simply import data from any of the formats and export copies to any of the four CSV formats for use in other apps as needed is crucial since it allows you greater flexibility in handling your data.


How to Read CSV File in Excel: 5 Quick Tricks

For better understanding, let’s see an example of a CSV file stored in D:\Exceldemy.

csv file saved in local pc

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, we get the Sales Report of some Items given along with Product ID, Sales Rep, and Sales. And, the comma is the space delimiter. Surely, this is the structure of a CSV file.

csv file containing dataset

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

Now, we’ll utilize this dataset to read CSV file in Excel using multiple methods. So, let’s explore them one by one.
Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


1. Open CSV File Directly to Read

If you already have installed the Excel application, you can open the CSV file directly. This is the most convenient and straightforward way to access and work with the data in the CSV file.

  • For doing this, just open the Windows File Explorer and go to the directory of the existing CSV file.
  • Then, select the file and double-click over it.

double-click on csv file to open in Excel

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

  • Just, go to File >> Open >> Browse.

clicking on Browse option in Excel

  • 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 (*.prn;*.txt;*.csv).
  • Immediately, you’ll see the Sales Report.csv file. Now, just click on the Open button.

Working on Open dialog box to select csv file

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

csv file opened in excel worksheet

Note: Upon opening the CSV file directly in Excel, the software will automatically generate a worksheet with the same name as the CSV file, where the data will be displayed.
  • Just add a suitable title. Finally, the output will look as follows after applying the necessary formatting.

applied formatting to read csv file in excel

Read More: How to Open CSV File with Columns in Excel


2. CSV File Opening with Excel Application

If you don’t have the Excel application installed or Excel is not the default app for opening (.csv) files, you will benefit from using this method.

  • Install the Excel application.
  • After installing the application, select the CSV file and right-click on it.
  • Then, go to Open with (pick this option from the context menu) >> Choose another app.

right clicking on csv file in directory

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

making Excel default apps to open csv file to read

The output is just similar to the previous method.

Read More: Open CSV File in Excel Without Formatting


3. Read CSV File Using From Text/CSV Feature (Power Query)

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.

  • Go to the Data tab > From Text/CSV.

using From Text or CSV feature

  • Next, select the Sales Report.csv file and then tap the Import button.

working on Import Data dialog box in Excel

  • Then, you’ll see a preview of the text file where the Comma is fixed as the Delimiter automatically.
  • If you want to load the converted data into a working sheet, select the Load To option.

Comma as delimiter in csv file and preview of the file to read

  • Next, check the radio button before the New worksheet option if you want to load the data in a new worksheet. Otherwise, specify the location under the Existing worksheet.
  • Click OK.

selecting New worksheet to put the data from csv file to Excel

  • In the workbook, it opens in a new sheet with the name same as the CSV file name and is formatted as a Table.

data formatted as query table

You can convert it to a normal range.

  • Place the cursor on any cell inside the table.
  • Then, navigate to the Table Design tab >> Tools group drop-down >> Convert to Range option.

converting data to normal range

  • In the warning dialog box, click OK.

warning dialog box to give alert about removing query definition from sheet

After applying to format, this is the final result.

formatted data to read from csv file in Excel

Read More: How to Open CSV with Delimiter in Excel


4. Utilize Text to Columns Feature 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 on the keyboard.

selecting and copying data from csv file

  • Next, just go to the B4 cell (you can choose according to yours) in a new worksheet and press CTRL + V to paste the texts.

pasting data in output cell

  • Select the texts in the B4:B14 range and click the Data tab >> Text to Columns feature on the Data Tools group.

using text to columns feature to read csv file in Excel

  • In Convert Text to Columns Wizard – Step 1 of 3 wizard, you have to choose the Delimited data type.
  • Then, click on Next.

step 1 of 3 of Convert Text to Columns wizard

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

step 2 of 3 of Convert Text to Columns wizard

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.
  • Give the range of the Destination cell (in this case, we selected cell B4).
  • Click on Finish.

step 3 of 3 of Convert Text to Columns wizard

Eventually, you’ll get the following output.

raw data after using text to column feature in Excel

And, after formatting, it looks like that.

final result of csv file after formatting in Excel to read

Read More: How to Open CSV File in Excel with Columns Automatically


5. Apply VBA Code to Read CSV File

In this method, we’ll show you the simple yet effective examples to apply VBA to read CSV file in Excel. Hence, learn the steps to perform the task.

  • Move to the Developer tab, then click on the Visual Basic button in the Code group.

navigating on Developer tab in Excel

Note: By default, the Developer tab remains hidden. You can learn to enable the Developer tab by following this linked article.

It launches the Microsoft Visual Basic for Applications window.

  • Now, click the Insert tab and choose Module from the list. We get a small Module window to insert our VBA code.

inserting new code module

Here’s the working code to do the task.

  • Paste this code into the module.
Sub Read_CSV()

'Declaring the worksheet variable and file_picker variable
Dim wsheet As Worksheet, file_picker As String

'Setting the worksheet to "VBA"
Set wsheet = ActiveWorkbook.Sheets("VBA")

'Using Application.GetOpenFilename to display the file picker dialog box to select a CSV file
file_picker = Application.GetOpenFilename _
("Text Files (.csv),.csv", , "Provide Text or CSV File:")

'Adding a QueryTable to the worksheet with the selected file
With wsheet.QueryTables.Add(Connection:="TEXT;" & file_picker, _
Destination:=wsheet.Range("B4"))

'Setting the text file parse type to delimited
.TextFileParseType = xlDelimited

'Setting the text file delimiter to comma
.TextFileCommaDelimiter = True

'Refreshing the data in the QueryTable
.Refresh
End With

End Sub

VBA code to read csv file in Excel

  • After completing the code script, click the green-colored play button to Run the code. You can press F5 on the keyboard to do the same task.
  • Stick to the following video to understand it clearly.
  • Apply formatting to improve aesthetics.

result of applying vba code to open csv in Excel

Read More: How to Open Large CSV Files in Excel


Importance of Knowing How to Read CSV File in Excel

Effective data handling and analysis skills are essential in today’s data-driven environment. One of the most popular data formats for storing structured data is the Comma Separated Value (CSV) file format. Excel has the ability to read and comprehend CSV files and we get the following advantages from that.

  • Excel users who work with enormous datasets will find it extremely important to know how to read CSV files. Users may effectively examine, sort, filter, and alter the data by opening a CSV file directly in Excel and using its many features and capabilities.
  • It can organize the data in a more readable way, calculate and analyze values, and present the data visually by making charts and graphs.
  • Additionally, CSV files may contain sensitive information and can originate from a wide range of sources, including databases, applications, and websites. Users may access and analyze data securely and effectively by using Excel to read these files, ensuring that important insights are not lost.

How to Save CSV File in Excel

By understanding how to save a CSV file in Excel, users can ensure that their data is stored accurately and securely for future use. Saving a CSV file in Excel is a straightforward process that can be done by following a few simple steps.

  • First, advance to the File tab.

opening File tab

  • Then, click on Save As >> Browse.

clicking on Browse option

  • Select the file path where you want to save your desired file.
  • Make sure to select CSV (Comma delimited) (*.csv) as Save as type.
  • Then, click on Save.

saving as CSV file in Excel

As a result, it will store the Excel file in (.csv) format.

new file saved in csv format


How to Open Several CSV Files Simultaneously in Excel

Manually opening each file one by one can be time-consuming and tedious. Here, we will explore how to open several CSV files simultaneously in Excel, saving you time and effort.

  • Bring the Open dialog box just like Method 1.

To select multiple files,

  • Click on the first file, then hold down the Shift key and click on the last file to select adjacent files. Selecting adjacent files through this action will include the first and last files along with all the files that fall between them.
  • You can choose multiple non-adjacent files by holding down the CTRL key and actively clicking on each individual file that you wish to open.
  • After selecting the files, click on Open.

opening multiple csv file at once in Excel

Note: Although the method is simple and efficient, it has a minor drawback of opening each CSV file in a separate workbook. Having to switch between several Excel files can be inconvenient and burdensome in practice. To address this issue, an alternative approach is to import all the files into a single workbook, which can provide a more seamless experience.

Things to Remember

  • Check File Encoding: CSV files can be saved in different encoding formats such as UTF-8, UTF-16, and ANSI. If the CSV file contains non-English characters, it’s important to check the encoding format and select the appropriate option while importing the file into Excel.
  • Use Text to Columns Feature: If the CSV file contains data that is separated by a character other than a comma, such as a tab or a semicolon, the Text to Columns feature in Excel can be used to split the data into separate columns.
  • Check Preview: Before importing the CSV file into Excel, use the preview feature to make sure that the data is properly formatted and all the columns are aligned properly.
  • Remove Any Unnecessary Characters: Sometimes CSV files can contain unnecessary characters such as quotes or spaces. It’s important to remove these characters before importing the CSV file into Excel to avoid issues with data formatting.

Frequently Asked Questions

1. Can I use a different delimiter besides a comma?

Yes, you can use a different delimiter besides a comma. When importing the CSV file into Excel, you can select the appropriate delimiter from the list of options.

2. What should I do if the CSV file contains non-English characters?

If the CSV file contains non-English characters, you should check the file encoding and select the appropriate option while importing the file into Excel. You can also use the Unicode (UTF-8) encoding format for international characters.

3. How do I import large CSV files into Excel?

If you have a large CSV file, you can import it into Excel using the Power Query feature. This allows you to work with the data more efficiently by filtering, sorting, and transforming the data before importing it into Excel.


Download Practice Workbook

Download the following practice workbook and CSV file. It will help you to realize the topic more clearly.


Conclusion

That’s the end of today’s session. Certainly, I believe that you can utilize the above methods if you want to know how to read the CSV file in Excel. By following the steps outlined above, you can easily import CSV files into Excel and start analyzing the data using Excel’s powerful tools and features.
Also, we discussed some tips and tricks for reading CSV file in Excel. By keeping these tips in mind, you can make the most of your CSV files in Excel and streamline your data analysis workflow.
Anyway, don’t forget to share your thoughts in the comments section below. Happy Excelling.


Related Articles


<< Go Back to Import CSV to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo