Import Data from Text
Excel In 2016 has a new set of features known as Get & Transform or in earlier versions, it is known as Power Query. It provides a platform to perform a series of steps to help you to transform your excel data. There is one step which will allows you to Import data from text, if your data source is not in your Excel spreadsheet, but it is inside a text file. It is going to be very easy for you to import data from text right into your Power Query within 3 steps.
NOTE 1: You can create random excel workbook online here for practice purposes as I did in this tutorial.
NOTE 2: If You use Microsoft Excel Prior to 2016 Version, then You Need to download Power Query Separately.
For example, in this blog I’ll be using this set of data from a text file:
Here are the steps to follow:
STEP 1: Choose TXT File
If you are using Excel 365
Go to Data > Get Data > From File > From Text/CSV
or if you are using Excel 2013 or Excel 2010
Go to Power Query > From File > From Text
Now you need to select that text file (.txt) which contains your data and then click the Import.
Then you will get a preview of the text data so, if you liked it then press the Transform.
STEP2: Editing Imported Content
As you can see we have successfully imported the TXT file but the content still looked messed up isn’t it? I mean First, Last, City becomes one column.
So to edit it, first we will choose, Split Column (Transform) in power query window and then click on By Delimiter;
Now, Popup will open, Choose space under select or enter delimiter, then click on the third option, Each occurrence of the delimiter and then click OK;
Now you can see all columns are separated;
STEP3: Adding Headers and Loading Table
For this, just click on Use first row as headers (Under transform),
Now click on Close & Load option to load table into your sheet;
Therefore, We get out Spreadsheet from TXT;