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

In this article, we’ll walk through 5 easy methods for reading CSV files in Excel, and provide some tips for working with them.

CSV, the abbreviation of 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. For example, .shp file (extension of the geographical application ArcGIS) is not supported in Excel, but you can easily convert the .shp to a .csv file, then open the CSV file in Excel and work with the converted data.

By default, the comma is the delimiter in a CSV file, however you can use other space characters like tab and semicolon too.

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.

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

If you open the Sales Report.csv file with Notepad (a dedicated text editor application owned by Microsoft), you’ll get the following data in text format: the Sales Report of some Items along with Product ID, Sales Rep, and Sales, with the comma as the delimiter. This is the structure of a CSV file.

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

We’ll utilize this dataset to demonstrate reading a CSV file in Excel using multiple methods.

We have used the Microsoft Excel 365 version here, but you may use any other version at your convenience. Please leave a comment if any part of this article does not work in your version.

Method 1 – Opening a CSV File Directly

This is the most convenient and straightforward way to access and work with the data in the CSV file if Excel is already installed.

STEPS:

Open the Windows File Explorer and go to the directory of the existing CSV file.
Select the file and double-click it.

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

Go to File >> Open >> Browse.

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).
You’ll see the Sales Report.csv file. Click on the Open button.

You’ll get the following output:

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, and the output will look as follows after applying some formatting:

Method 2 – Opening CSV File with the Excel Application

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

STEPS:

Install the Excel application.
After installing the application, select the CSV file and right-click on it.
Select Open with from the context menu >> Choose another app.

From the following options, select the Excel application.
Check the box before Always use this app to open .csv files for opening the file directly (Method 1).

The output is similar to the previous method.

Method 3 – Using From Text/CSV Feature (Power Query)

If you have a larger dataset (in .csv format), you can utilize the From Text/CSV option, a feature of Power Query, a data transformation and preparation engine in Excel.

STEPS:

Go to the Data tab > From Text/CSV.

Select the Sales Report.csv file and tap the Import button.

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.

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.

In the workbook, a new sheet with the name same as the CSV file name is opened and formatted as a Table.

To convert it to a normal range:

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

In the warning dialog box, click OK.

After applying some formatting, this is the final result:

Method 4 – Using the Text to Columns Feature

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

STEPS:

Select the text to import and copy it by pressing CTRL + C on the keyboard.

Go to cell B4 (or your choice of starting cell) in a new worksheet and press CTRL + V to paste the copied text.

Select the texts in the B4:B14 range.
Click the Data tab >> Text to Columns feature in the Data Tools group.

In Convert Text to Columns Wizard – Step 1 of 3 wizard, select the Delimited data type.
Click on Next.

In Step 2 of 3, pick the Comma as Delimiters.

Note: If you have a different space delimiter, choose that instead of the Comma.

Keep the General data format checked.
Give the range of the Destination cell (in this case, we selected cell B4).
Click on Finish.

You’ll get the following output:

After formatting, it looks like this:

Method 5 – Using VBA Code

STEPS:

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

Note: By default, the Developer tab remains hidden. Learn to enable the Developer tab.

The Microsoft Visual Basic for Applications window will open.

Click the Insert tab and choose Module from the list. We get a small Module window to insert our VBA code.

Paste the following 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

Click the green-colored Play button to Run the code. Or press F5 on the keyboard to perform the same task.

Follow this video to understand the process clearly:

Apply formatting to improve aesthetics.

How to Save CSV File in Excel

STEPS:

Click on the File tab.

Click on Save As >> Browse.

Select the file path where you want to save your desired file.
Select CSV (Comma delimited) (*.csv) as Save as type.
Click on Save.

As a result, the Excel file will be 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. But we can open several CSV files simultaneously in Excel.

STEPS:

Open the Open dialog box as in Method 1.

To select multiple files:

To select adjacent files, click on the first file, then hold down the Shift key and click on the last file.
To select non-adjacent files, hold down the CTRL key and click on each individual file that you wish to open.
After selecting the files, click on Open.

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.

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

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?

Use the Power Query feature, which allows you to work with the data more efficiently by filtering, sorting, and transforming the data before importing it into Excel.

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

Method 1 – Opening a CSV File Directly

Method 2 – Opening CSV File with the Excel Application

Method 3 – Using From Text/CSV Feature (Power Query)

Method 4 – Using the Text to Columns Feature

Method 5 – Using VBA Code

How to Save CSV File in Excel

How to Open Several CSV Files Simultaneously in Excel

Things to Remember

Frequently Asked Questions

Related Articles

Leave a reply Cancel reply