Import or export text (.txt or .csv) files

There are two ways to import data from a text file by using Microsoft Office Excel: You can open the text file in Excel, or you can import the text file as an external data range. To export data from Excel to a text file, use the Save As command.
There are two commonly used text file formats:

• Delimited text files (.txt), in which the TAB character (ASCII character code 009) typically separates each field of text.
• Comma separated values text files (.csv), in which the comma character (,) typically separates each field of text.

You can change the separator character that is used in both delimited and .csv text files. This may be necessary to make sure that the import or export operation works the way that you want it to.
 Notes 

• You can import or export up to 1,048,576 rows and 16,384 columns.
• Whether you are importing or exporting data, you may want to learn more about Excel text converters. For more information, see "Text formats" in the Help topic, File formats that are supported in Excel and "Text file formats" in the Help topic, Excel formatting and features that are not transferred to other file formats.

What do you want to do?

• Import a text file by opening it in Excel
• Import a text file by connecting to it
• Export data to a text file by saving it
• Change the delimiter that is used in a text file
• Change the separator in all .csv text files

Import a text file by opening it in Excel

You can open a text file that you created in another program as an Excel workbook by using the Open command. Opening a text file in Excel does not change the format of the file — you can see this in the Excel title bar, where the name of the file retains the text file name extension (for example, .txt or .csv).

1. Click the Microsoft Office Button , and then click Open.

The Open dialog box appears.

2. On a computer that is running Windows Vista    

• In the list, select Text Files.

On a computer that is running Microsoft Windows XP    

• In the Files of type list, select Text Files.

3. On a computer that is running Windows Vista    

• In the Address bar, locate and double-click the text file that you want to open.

On a computer that is running Microsoft Windows XP    

• In the Look in list, locate and double-click the text file that you want to open.

• If the file is a text file (.txt), Excel starts the Import Text Wizard.

Follow the instructions in the Text Import Wizard. Click Help for more information about using the Text Import Wizard or see Text Import Wizard. When you are done with the steps in the wizard, click Finish to complete the import operation.

• If the file is a .csv file, Excel automatically opens the text file and displays the data in a new workbook.

 Note   When Excel opens a .csv file, it uses the current default data format settings to interpret how to import each column of data. If you want more flexibility in converting columns to different data formats, you can use the Import Text Wizard. For example, the format of a data column in the .csv file may be MDY, but Excel's default data format is YMD, or you want to convert a column of numbers that contains leading zeros to text so you can preserve the leading zeros. To run the Import Text Wizard, you can change the file name extension from .csv to .txt before you open it, or you can Import a text file by connecting to it.

Import a text file by connecting to it

You can import data from a text file into an existing worksheet as an external data range.

1. Click the cell where you want to put the data from the text file.
2. On the Data tab, in the Get External Data group, click From Text.

3. On a computer that is running Windows Vista    

• In the Address bar, locate and double-click the text file that you want to import.

On a computer that is running Microsoft Windows XP    

• In the Look in list, locate and double-click the text file that you want to import.

Follow the instructions in the Text Import Wizard. Click Help for more information about using the Text Import Wizard, or see Text Import Wizard. When you are done with the steps in the wizard, click Finish to complete the import operation.

4. In the Import Data dialog box, do the following:
• Optionally, click Properties to set refresh, formatting, and layout options for the imported data.
• Under Where do you want to put the data?, do one of the following:
• To return the data to the location that you selected, click Existing worksheet.
• To return the data to the upper-left corner of a new worksheet, click New worksheet.
5. Click OK.

Excel puts the external data range in the location that you specify.

If Excel does not convert a column of data to the format that you want, you can convert the data after you import it. For more information, see the following Help topics:

• Convert numbers stored as text to numbers
• Convert dates stored as text to dates
• TEXT function
• VALUE function

Export data to a text file by saving it

You can convert an Excel worksheet to a text file by using the Save As command.

1. Click the Microsoft Office Button , and then click Save As.

The Save As dialog box appears.

2. In the Save as type box, choose the text file format for the worksheet.

For example, click Text (Tab delimited) or CSV (Comma delimited).

 Note   The different formats support different feature sets. For more information about the feature sets that are supported by the different text file formats, see Excel formatting and features that are not transferred to other file formats.

3. On a computer that is running Windows Vista    

• In the Address bar, browse to the location where you want to save the new text file, and then click Save.

On a computer that is running Microsoft Windows XP    

• In the Save in box, browse to the location where you want to save the new text file, and then click Save.

4. A dialog box appears, reminding you that only the current worksheet will be saved to the new file. If you are certain that the current worksheet is the one that you want to save as a text file, click OK. You can save other worksheets as separate text files by repeating this procedure for each worksheet.
5. A second dialog box appears, reminding you that your worksheet may contain features that are not supported by text file formats. If you are interested only in saving the worksheet data into the new text file, click Yes. If you are unsure and would like to know more about which Excel features are not supported by text file formats, click Help for more information.

For more information about saving files in other formats, see Save a workbook in another file format.

Change the delimiter that is used in a text file

If you use the Text Import Wizard to import a text file, you can change the delimiter that is used for a delimited text file from a TAB character to another character in Step 2 of the Text Import Wizard. In this step, you can also change the way that consecutive delimiters, such as consecutive quotation marks, are handled.
For more information about how to use the Text Import Wizard, see Import a text file by opening it or Text Import Wizard.
Top of Page

Change the separator in all .csv text files

1. In Microsoft Windows, click the Start button, and then click Control Panel.
2. Open the Regional and Language Options dialog box.
3. Do one of the following:
• In Windows Vista, click the Formats tab, and then click Customize this format.
• In Windows XP, click the Regional Options tab, and then click Customize.
4. Type a new separator in the List separator box.
5. Click OK twice.

 Note   After you change the list separator character for your computer, all programs use the new character as a list separator. You can change the character back to the default character by following the same procedure.