Import a structured text file to an Excel spreadsheet

Excel 2016
When you import data from a text file to the Excel spreadsheet, Microsoft Excel launches a Text Import Wizard to help you to import data correctly. Here you can import all or some data from text files of different formats:

For example:

Examples of structures text files

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 365

   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 365
  • Click the Open button.

Note: After clicking the Open button, some comma-separated files (.csv) open automatically.

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

Text Import Wizard – Step 1 of 3 dialog box in Excel 365

   4.1.   If you do not see the delimiters, and it seems that data starts at fixed positions in the file, you can change Original data type, by selecting Fixed width instead of Delimited (see the steps how to import data from this type of files):

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

   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 365

   4.3.   Click the Next > button.

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

   5.1.   In the Delimiters group, choose the correct delimiter for the data (by default the Tab delimiter is used, but standard formats use commas or semicolons):

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

Note: Even if you are pretty sure about the delimiter, you need to change it if you see incorrect data in the Data preview area.

   5.2.   Click the Next > button.

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

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

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

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 365
  3. You can skip 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 365

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