Import external data from text files

In this lesson you will learn how to import data from a text file. As a example I will import lyrics of Justin Bieber's Love me song.

On the Data ribbon, the first section is called Get External Data:

from text ribbon

The third command from the left: From Text allows you to import data from a text file. After its use, the window appears in which you specify the file to import. Then start the Text Import Wizard:

In the first step of the wizard, specify the type of the file:

  • Is it a delimited file? The following columns are displayed with a sign (eg. space, comma or tab). When Excel finds such a character in the line of the file knows that more data is entered in the next column.
  • Is it a file with fixed width? In this file the next column take up a certain amount of characters. For example, the first column is ten characters and the other eight. After entering in the first column of ten characters typed to the next the next eight and so on.

Sometimes, the data in the file are preceded by a header. The system generates a text file that can be placed in the file generation date, name or other comments to the data. The Start import at row allows you to bypass this information and start downloading data from the right place.

File Origin box usually leave unchanged. If the file in the preview below instead of letters you can see incomprehensible characters, you need to change this option.

Text Import Wizard Step 1 of 3

Clicking Next move on to the second step of the wizard:

In this step, you specify which character separates columns in the file. The default is a tab. Below you can see preview of the imported file, the data is already in its columns. So I press on.

If you choose pre-delimited text (as in this example), this is where you can specify which columns are separated by a text file. In this case, it is a space.

Text Import Wizard Step 2 of 3

In the third step just choose data format of imported data. I chose text.

Text Import Wizard Step 3 of 3

Next Excel will ask you where to paste the text. I want to import it to existing worksheet beginning from A1 cell.

Import Data

Excel imported text. Every word is in separated cell as I wanted to do.

Imported Justin Bieber Love Me Lyrics

Now I can change every word of this "song" into better one.


Tip! There is some other way to import text files to Excel. Try change the extension of file from txt to xls. It sometimes works.