The Excel Power Query feature (also popularly known as ‘Get & Transform’) is an amazing excel tool used to import and transform data from external sources and applications into Excel. In this tutorial, we would learn how to import or convert the CSV or .CSV file to excel using the power query feature.
The power query feature is available under the ‘Get & Transform Data’ group (‘Data’ tab) in Microsoft Excel 2016 and other higher excel version. If you are Excel 2010 and 2013 user, then for you I have this tutorial.
Before converting CSV to excel, let me first give you some brief about what is CSV file format and why is it so popular.
What is CSV File Format and Why People Use It?
The term CSV (or .CSV) stands for Comma Separated Values File. This file format stores and exchange a large amount of data in a tabular format between two or more applications. It consumes lesser space as compared to other data storage applications. As the name suggests, this file format, in most of the cases, uses comma character (,) as a delimiter to separate tabular values. However, it may use other delimiters as well, like a semicolon, etc.
This file looks just like an excel file, but with file extension as .csv instead of .xls/.xlsx. Though it looks like an excel file, it is very different from the later. Unlike other Excel workbooks, you can only have one worksheet in the .csv file. Also, you cannot use any formula in the .csv file format.
This is how the CSV File Icon looks like:
You can download sample CSV file from internet and save in to your PC.
Use Power Query To Convert CSV File to Excel [Step by Step Guide]
Let us now start with the steps for importing the CSV file to Excel. Follow the below steps.
- Open a new excel workbook and navigate to – Data tab > Get & Transform Data group > Get Data > From File > From Text/CSV.
- As a result, the ‘Import Data’ dialog box would pop up. Here, navigate, search, and select for the CSV file and click ‘Open’.
- A new dialog box would appear on your screen, showing a preview of the sample CSV file. Also, you would notice that excel would detect the delimiter (i.e. comma) based on the CSV file structure. If the imported CSV file has some other delimiter, then you can select the delimiter from the list.
- Now, let us load this data into Excel. To import CSV data to excel, simply click on the ‘Load’ button as shown below:
- As a result, the excel adds a new worksheet and creates a table with CSV data in this new worksheet, as shown below.
You can see that 1000 rows of data got added to the excel file (‘Queries and Connections’ pane).
Wasn’t is so simple? Now, let us see some more details on why excel users prefer to use the power query feature to convert CSV to Excel?
Why To Use Power Query To Convert CSV to Excel?
The Power Query feature is not just limited to importing data from external sources to excel. Apart from loading CSV data directly to excel, you can perform many other transformations (using the ‘Power Query Editor’ window) to clean up the imported data and then load the clean data to excel.
While you perform the transformation on the data, excel records all the steps in a sequence and stores it in the form of ‘Queries & Connections’.
But what is the purpose and use of ‘Queries and Connections’? Below is the answer for the same.
When the source CSV file data updates or if there are new rows of data in the source file, you need not perform the above-explained steps from scratch. The only step is to refresh the stored query.
On refresh, excel would re-run all the stored steps in the same sequence (including transformation steps) and get the updated CSV data to excel.
Let me give an example for better understanding.
I have, for an illustration purpose, deleted the first three columns from the source CSV file. Now, to get this updated CSV data to excel, simply go to the excel table, right-click anywhere over there, and then select the option ‘Refresh‘.
Isn’t it great !! Do you know what did Excel do in the background? On refresh, the steps stored in the ‘Queries & Connections’ were re-triggered one after the other and the excel fetched the new data from the source file to excel.