How to Use the Covariance Analysis Tool in Excel
How to Use the Custom AutoFilter on an Excel Table
How to Enter a Function in Excel with the Function Command

How to Import Straight Text in Excel 2013

When you don’t or can’t export directly to Excel, you need to take the second step of importing the ASCII text file that you created with the other program. Here are the steps that you take to import a straight text file:

1

Open the text file by choosing Open from the File menu or by choosing the Data tab’s Get External Data from Text command.

Excel displays the Open dialog box, if you choose the Open command. Excel displays a nearly identical Import Text File dialog box if you choose the Data tab’s Get External Data from Text command.

2

Choose Text Files from the drop-down list.

This list appears to the right of the File text box.

3

Use 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.

4

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

Excel starts the Text Import Wizard.

5

Select the Fixed Width radio button.

This tells Excel that the fields in the text file are arranged in evenly spaced columns.

6

In the Start Import at Row text box, identify the row in the ASCII 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 won't want to start importing at row 1; you’ll 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. If you import too many rows, 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.

7

Click Next.

Excel displays the second step dialog box of the Text Import Wizard. You use this second Text Import Wizard dialog box to break the rows of the text files into columns.

You might not need to do much work identifying where rows should be broken into columns. Excel, after looking carefully at the data in the to-be-imported text file, suggests where columns should be broken and draws vertical lines at the breaks.

8

In the Data Preview section of the second wizard dialog box, review the text breaks and amend them as needed.

If they’re incorrect, drag the break lines to a new location.

To remove a break, double-click the break line.

To create or add a new break, click at the point where you want the break to occur.

9

Click Next.

Excel displays the third step dialog box of the Text Import Wizard.

10

Choose the data format for the columns in your new workbook.

You can pick default formatting from the third Text Import Wizard dialog box for the columns of the new workbook.

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

If you choose the Date format radio button as the default for a column, use the Date drop-down list to choose a Date format.

11

Identify any columns that Excel should not import.

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

12

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

You can click the Advanced button (on the third Text Import Wizard dialog box) to display the Advanced Text Import Settings dialog box. The Advanced Text Import Settings dialog box provides text boxes that you can use to specify in more detail or with more precision how the data in the text file is arranged.

Choose what symbol is used to separate whole numbers from decimal values by using the Decimal Separator drop-down list.

Choose what symbol is used to separate thousands by using the Thousands Separator drop-down list.

Click OK after you make choices here; you return to the third wizard dialog box.

13

Click Finish.

Excel imports the text file according to your specifications and places it into a new Excel workbook. The data probably won't be perfectly laid out.

Still, when you have very large data sets, you'll find importing a tremendous timesaver. In general, you won't find it terribly difficult to clean up the new workbook. You only need to delete a few rows or perhaps columns or maybe do a bit of additional formatting or row and column resizing.

blog comments powered by Disqus
How to Query an External Database in Excel
How to Use AutoFilter on an Excel Table
How to Set Pivot Table Options in Excel
How to Import a Database Table in Excel
How to Rank by Percentile in Excel
Advertisement

Inside Dummies.com