Getting external data from a text file

The most important requirement for getting data from a text file is identifying the character where the column splits will occur – this is known as the delimiter.

For example, full names may be split by a space as in John Smith, or by a comma as in Smith, John.

Alternatively, you can choose to split the text after a fixed number of characters.

If the text and required split is more complicated, it may be easier to work with the text in Microsoft Word first and create a table there, and then copy and paste it into Excel.

To get data from a text file:

Select the cell where you want to insert the data

On the Ribbon, click the Data tab

In the Get External Data group, click the From Text button

The Import Text File dialog box will be displayed – this is similar to a standard Open dialog box.

Locate and select the text file, and then click Import

The Text Import Wizard will be started.

Step 1

The Preview box shows the content of the data file.

In Step 1, you can choose to split the text using a delimiter (a particular character), or using fixed width (a particular number of characters).

Choose Delimited or Fixed width as required

You can also choose to import data from row 1 of the source file, or any other row number.

If you do not want to import the whole file:

In the Start import at row box, enter the required row number

If you know that the file uses a particular character set:

Open the File origin list box, and then choose the required option

If the first line of data contains headers:

Click the My data has headers option so that it is ticked

When you are ready:

Click Next

Step 2 ~ Delimited

If you choose Delimited, the Delimiters options are shown in Step 2.

You can choose the character on which to create the split. As you make changes, you can see the results in the Data preview area.

In the Delimiters options, choose the required delimiter or delimiters – you can also choose Other, and then type your own delimiter

If you think that the data may contain consecutive delimiters, such as double spaces, click the Treat consecutive delimiters as one option so that it is ticked

If some of the data is text that is enclosed by speech marks, or a similar character, open the Text qualifier list box, and then choose the relevant character

When you are ready, click Next

Step 2 ~ Fixed width

If you choose Fixed width, you will see options for setting break lines.

In Step 2, you can set the widths for each column – Excel will normally suggest the break lines if it can tell that the columns are of fixed width.

To create a break line, click the ruler at the required position

To move a break line, drag the break line to a new position

To delete a break line, double-click the break line

Column breaks are indicated in the preview.

When you are ready, click Next

Step 3

In Step 3, you can adjust the individual columns that are being created.

For each column:

In the Data preview area, click the column to select it

If necessary, in the Column data format area, choose the required format for the column

When you are ready:

Click Finish

The Import Data dialog box will be displayed.

Using the Where do you want to put the data options, make sure that the correct cell is chosen in the existing worksheet, or alternatively you can choose New worksheet

Click OK

The data will be imported into Excel. A connection to the data source will probably be established, but security settings on your computer could prevent this.