How to Import Delimited Text Files in Excel 2013 - dummies

How to Import Delimited Text Files in Excel 2013

By Stephen L. Nelson, E. C. Nelson

To import the ASCII text file, first open the text file itself from within Excel. When you open the text file, Excel starts the Text Import Wizard. This wizard walks you through the steps to describe how information in a text file should be formatted and rearranged as it’s placed in an Excel workbook.

Here are the steps that you take to import a delimited text file:

1Choose the File menu’s Open command or click the Data tab’s Get External Data from Text button.

Excel displays the Open dialog box or the Import Text File dialog box.

2Choose Text Files from the drop-down list.

The drop-down list box you use to choose the type of file you want appears to the right of the File Name text box.

3Use the Look In drop-down list to identify the folder in which you placed the exported text file.

You should see the text file listed in the Open dialog box.

4To open the text file, double-click its icon.

Excel may be able to guess how the delimited data file organizes its data and just open the file in the Excel workbook window. Or Excel may start the Text Import Wizard.

5Select the Delimited radio button.

This tells Excel that the fields in the text file are separated by (one or more) delimiters.

6In the Start Import at Row text box, identify the point in the delimited text file that should be the first row of the spreadsheet.

In general, ASCII text files use the first several rows of the file to show report header information. For this reason, you typically want to start importing at row 10 or 20 or 5.

Don’t get too tense about this business of telling the Text Import Wizard which row is the first one that should be imported. You can easily delete the extraneous rows later in Excel.

You can preview the to-be-imported report shown on the bottom section of the Text Import Wizard dialog box.

7Click Next.

Excel displays the second dialog box of the Text Import Wizard. You use this second Text Import Wizard dialog box to identify the character or characters used as the delimiter to break the text into columns. For example, if the file that’s being imported is a tab-delimited file, select the Tab check box in the Delimiters area.

8Click Next.

The third Text Import Wizard dialog box appears.

9Choose the data format for the columns in your new workbook:

To choose the default format for a column, click that column in the Data Preview box and then select one of the Column Data Format radio buttons.

To use the Date format as the default for a column, select the Date radio button and use the Date drop-down list to choose a Date format.

The Data Preview box on the second Text Import Wizard dialog box shows how the file will look after it’s imported based on the delimiters that you identified. Experiment a bit to make sure that you import the data in a clean format.

10Identify any columns that Excel should skip importing.

If you don’t want to import a column, select the column and then select the Do Not Import Column (Skip) radio button.

11Nit-pick how the data appears in the text file.

Click the Advanced command button of the third Text Import Wizard dialog box to display the Advanced Text Import Settings dialog box. Here, you can specify in more detail how the data in the text file is arranged.

Click OK to return to the third Text Import Wizard dialog box.

12Click Finish.

Excel imports the delimited text file according to your specifications. As with a straight text file, the data probably won’t be perfectly laid out. But you won’t find it difficult to clean up the new workbook. A few deletions, a little resizing, and pretty soon the workbook will look the way you want.