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.  

Previously in Power QueryHow to Import Data from CSV Using Power Query?

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:

Import Data from Text

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

Import Data from Text

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.

Import Data from Text

Then you will get a preview of the text data so, if you liked it then press the Transform.

Import Data from Text

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;

Import Data from Text

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;

Import Data from Text

Now you can see all columns are separated;

Import Data from Text

STEP3: Adding Headers and Loading Table

For this, just click on Use first row as headers (Under transform),

Import Data from Text

Now click on Close & Load option to load table into your sheet;

Import Data from Text

Therefore, We get out Spreadsheet from TXT;

Import Data from Text