Use the wizard to import data from a text file into Excel

You can use Microsoft Office Excel to import data from a text file into a worksheet. The Text Import Wizard checks the text file that you are importing and ensures that the data is imported in the way you expect.

Step 1 (total 3 steps)

Original data type Select Delimited if the items in the text file are delimited by tabs, colons, semicolons, spaces, or other characters. If all items in the text file have the same length, select fixed width.

Import start line type or select a line number to specify the first row to import data into.

The original format of the file select the character set used in the text file. In most cases, you can keep the default settings for text formatting. If you know that a text file is created using a character set that is different from the one used on your computer, you should change this setting to be the same as the character set on your computer. For example, if your computer is set up to use character set 1251 (Cyrillic, Windows), and you know that the file was generated using the character set 1252 (Western European character, Windows), you should set the original file format to 1252.

Preview File The box displays text in the form of text that is divided into columns in the worksheet.

Step 2 (Total 3 steps) (separate data)

Separator Select the character that separates the values in the text file. If no characters are listed, select the other check box, and then type the characters in the box that contains the cursor. These options are not available if the data type is fixed width.

Continuous delimiters are treated as a single processing Select this option if the data contains a separator with more than one character between the data fields, or if the data contains more than one custom delimiter.

Text qualifier Select the character in the text file that encloses the value. When Excel encounters a text qualifier, all the text that follows the qualifier and precedes the next qualifier is imported as a value, even if the text contains a separator. For example, if the delimiter is a comma (,), the text qualifier is a double quotation mark ("), and" Dallas, Texas "is imported into a cell in Dallas, Texas form. If there is no qualifier or the specified single quotation mark (') is an identifier, "Dallas, Texas" will be imported into two adjacent cells in the form of "Dallas and Texas".

If the separator appears between two text identifiers, Excel omits the identifier in the import value. If there are no delimiters in the two text qualifier, Excel will include the identifier in the import value. Therefore, "Dallas Texas" (using the double quotation mark text qualifier) is imported into a cell in the form of "Dallas Texas".

Data preview View the text in this box to verify that the text is divided into columns on the worksheet in the way you expect.

Step 2 (Total 3 steps) (fixed-width data)

Data Preview Sets the field width in this section. Click the Preview window to set the column breaks that are represented by vertical lines. Double-click a column break to delete it or move it by dragging it.

Step 3 (Total 3 steps)

Click the Advanced button to specify the type of decimal separator and thousand separator used in the text file. When you import data into Excel, the separator matches the separator in your country/region specified in the Regional Settings (Windows Control Panel).

Column data format Click the data format of the column that you selected in the Data Preview section. If you do not want to import the selected columns, click Do not import this column (skip).

When you select the data format for the selected column, the column heading under Data Preview displays the format. If you select Date, in the Date box, select the date format.

Select the data format closest to the preview data so that Excel can accurately transform the imported data. For example:

• To convert all currency numeric character columns to Excel currency format, please select General. • To convert all numeric character columns to Excel text format, select text. • To convert all date character columns (sorted by year, month, and day) to Excel date format, select the date , and then select the YMD date type in the Date box.

If the conversion can produce some unexpected results, Excel imports the columns in a regular format. For example:

• If the column contains mixed formatting (such as alphanumeric characters), Excel converts a column to a regular format. • Excel converts a column to a regular format if each date in the Date column is in the order of year, month, and day, and the date and MDY date types are selected. Contains the date word.