Md. Abdul Kader
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 file 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.
Download Practice Workbook
Download the following practice workbook and CSV file. It will help you to realize the topic more clearly.
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.
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.
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.
Alternatively, you can open the file after opening a blank workbook in Excel.
- Just, go to File >> Open >> Browse.
- 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.
After using the above two methods (opening the File Explorer or Excel workbook), you’ll get the following output.
- Just add a suitable title. Finally, the output will look as follows after applying the necessary formatting.
Read More: How to Open CSV File with Columns in Excel (3 Easy Ways)
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 (also, you can use the web version of Excel for free).
- 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.
- 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.
The output is just similar to the previous method.
Read More: How to Open Notepad or Text File in Excel with Columns (3 Easy Methods)
Similar Readings
- 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. 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.
- Next, select the Sales Report.csv file and then tap the Import button.
- 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.
- 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.
- In the workbook, it opens in a new sheet with the name same as the CSV file name and is formatted as a 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.
- In the warning dialog box, click OK.
After applying to format, this is the final result.
Read More: How to Import Text File to Excel Using VBA (3 Easy Ways)
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.
- 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.
- Select the texts in the B4:B14 range and click the Data tab >> Text to Columns feature on the Data Tools group.
- In Convert Text to Columns Wizard – Step 1 of 3 wizard, you have to choose the Delimited data type.
- Then, click on Next.
- Then, (in Step 2 of 3), pick the Comma as Delimiters.
- 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.
Eventually, you’ll get the following output.
And, after formatting, it looks like that.
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.
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.
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
- 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.
Read More: How to Convert CSV to Excel with Columns (5 Methods)
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.
- Then, click on Save As >> Browse.
- 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.
As a result, it will store the Excel file 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.
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.
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.
