The Excel Power Query also called the ‘Get & Transform’ feature is an excellent excel tool to extract and import data from various internal or external sources into excel and transform it as per your need. In this tutorial, would learn how to extract, import, and get data from a text file to excel.
This feature is pre-installed in higher excel versions (Excel 2016 and above) but not inbuilt for lower excel versions. However, there is an external power query add-in for Excel 2010 and 2013. To get the power query feature for these versions, follow our tutorial on – Install Power Query in Excel 2010 [Step by Step Guide]. To name a few, the power query feature allows you to extract data from another excel workbook or files, from websites, or from JSON file format, etc.
What is Special About Power Query?
The reason why the power query feature has become so popular amongst excel users is that this feature instantly records and stores (in sequence) everything that you perform on the imported external data in the form of connections. Now, when the source text file updates or changes, you simply have to refresh the connections. On refresh, all the recorded steps are performed exactly in sequence source external file and you finally have updated data in to excel.
Confused ? No need to worry. In the upcoming section of this blog, we would learn to import and also perform some transformation steps on the data, and see what happens on data update.
Sample Data – Convert Text File To Excel
Suppose, I have 100’s of rows of purchase or sales data in a notepad file as shown in the image below. Now, I want to convert this notepad data into an Excel table, such that when I update this text file data in the future, the excel table data also gets updated.
You can easily achieve this using the powerful Power Query tool of Excel.
Use ‘Download’ button below to get the above sample text file(s) and practice as you go ahead.
Steps to Import Data from Text File to Excel | Power Query
Below step by step procedure would help you to convert a notepad file to excel:
- Open a new workbook and go to Data tab > Get & Transform Data group > Get Data > From File > From Text/CSV, as shown below:
I use the “Excel Office 365” version for my daily excel tasks. If you are using other excel versions, the screen look may be different.
- In the ‘Import Data‘ dialog box that appears, navigate and select the text file.
- As a result, a new window would pop out on your screen with the following details:
- The title of this window is the name of the imported text file.
- It detects the file origin and the text delimiter based on the file and the data in it.
- It shows the preview of the text file data and also detects and converts the first row as the data header row.
- To directly load the text file to excel, simply click on the ‘Load’ button on the bottom right corner of this window.
- As a result, you would get all the rows of data from the text file data to excel worksheet. In the present case, there were 300 rows of data in the text file, all loaded to excel now.
- Let us now make some changes in the source text file. I have, for demonstration purposes, added some new data rows to the text file.
- Now, use the right mouse click on the table and ‘Refresh‘ option (as highlighted below).
- As a result, excel would rerun all the power query steps and extracts the updated data from the source text file. Excel has now updated 306 rows of data.
Now, let us see what did the excel do in background !!
Power Query Editor – What Did Happen in Background?
The power query feature is no different than an excel macro recorder. It records all the transformation steps that you perform and saves it in form of a query.
Now, just like macro, you can rerun the steps to get the updated source data to excel.
While extracting the text file to excel using power query, excel performed and stored three default power query steps in the background-
- Extracting data from the source text file
- Promoting the first row from the original data as table headers
- Changing the ‘Data Types‘ of the table columns
To check these steps, click anywhere on the data table, and go to Query tab > Edit group > Edit option.
Or you can even double click the query in the ‘Queries & Connections’ pane, as shown below:
This would open the ‘Power Query Editor‘ window (as shown in the image below). On the right, under the ‘Query Settings‘ pane, you can see the power query name as ‘Purchase Details – November 2020‘ and the default ‘Applied Steps’.
The ‘Power Query Editor’ window has many more data transformation options on its ribbon tabs. As you perform these additional transformation steps, excel records all the steps in sequence and places them in the ‘Applied Steps‘ section (Query Settings pane) one below the other.
Now, when the data in the source files changes, you need to only refresh the power query. On refresh, it reruns all the ‘Applied Steps’ in sequence and gives the output.
Lets Take An Example To Understand Power Query Transformation
Let us now see how the power query behaves when we add more steps to it. Lets perform the following additional transformation:
- Rename the Power Query as ‘Data Transformation’
- Create a duplicate column of the column ‘Purchase Value’
- Rename this new column as ‘Net Value’
To rename the power query, simply go to the ‘Power Query Editor’ window. In the ‘Query Settings’ pane to the right, change the default power query name and press Enter to continue.
To create a duplicate of the column, right-click on the ‘Purchase Value’ column header and select the option ‘Duplicate Column’. This would instantly create a copy of the column with the name ‘Purchase Value – Copy’.
Now, just check the ‘Applied Steps’ section. You would notice that a new step gets added with the name ‘Duplicated Column’ as shown in the image below:
To rename this new column, double click on the new column header, type a nice name and then press Enter.
Check the ‘Applied Steps’ section and you would notice a new step added at the end – ‘Renamed Column’.
Finally load the transformed power query data to excel, using the path – ‘Home’ tab > ‘Close & Load’ option (in Power Query Editor window).
Now, when the source text file gets updated with new rows of data, you need to simply refresh your power query. On refresh, it would perform the ‘Applied Steps’ in the same sequence (from top to down). Meaning, it would extract data from the source > promote its headers > change header data types > create a duplicate of column > And finally, rename the duplicate column.
With this, we have completed the tutorial on ‘How to Import Data from Text File or Notepad to Excel’. Share your thoughts on this tutorial in the comment section below.