Import a text file to an Excel spreadsheet

Excel 2016
Often you need to import data to the Excel spreadsheet from a text file. Microsoft Excel offers a Text Import Wizard to import data from different text file formats:
  • Comma-separated files (.csv) or tab-separated files (.txt), see how to import data from a structured text file,
  • Files with fixed-width columns without delimiters between data columns where data starts at fixed positions of the line:
Examples of files with fixed-width columns

To import data from a text file, do the following:

   1.   On the File tab, click Open (or click Ctrl+O).

   2.   On the Open pane, click the Browse button:

Open pane in Excel 2016

   3.   On the Open dialog box:

  • Select the path to the text file you want to import,
  • From the File Type dropdown list, select All Files or Text Files:
Open dialog box in Excel 2016
  • Click the Open button.

   4.   In the Text Import Wizard – Step 1 of 3 dialog box:

   4.1.   In the Original data type group, choose the Fixed width option:

Fixed width in Text Import Wizard – Step 1 of 3 dialog box Excel 2016

Note: If you see delimiters, leave the option Delimited selected and follow the steps of importing a structured text file.

   4.2.   You can change the File origin to use another encoding to display the data (you need to know the character encoding for the data or try other encodings until you see the correct data in the preview section):

File origin in Text Import Wizard – Step 1 of 3 dialog box Excel 2016

   4.3.   Click the Next > button.

   5.   In the Text Import Wizard – Step 2 of 3 dialog box:

Text Import Wizard – Step 2 of 3 dialog box in Excel 2016

   5.1.   Create, move, and delete break lines:

Break lines in Text Import Wizard – Step 2 of 3 dialog box Excel 2016

   5.2.   Click the Next > button.

   6.   In the Text Import Wizard – Step 3 of 3 dialog box:

   6.1.   Select the column and the appropriate data format for this column in the Column data format group:

Text Import Wizard – Step 3 of 3 dialog box in Excel 2016

Notes:

  1. You can skip this step and format columns after importing.
  2. You can click the Advanced... button to select the correct Decimal separator and Thousands separator (see how to change used by default decimal symbol and digit grouping symbol):
    Advanced in Text Import Wizard – Step 3 of 3 dialog box Excel 2016
  3. You can ignore some columns for importing by selecting the Do not import column (skip) in the Column data format group (the second column Session in the previous example).

   6.2.   Repeat the previous step for all columns you want to import and click Finish.

Excel imports all the data you selected:

Imported data in Excel 2016

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.