Import or export a text file in Excel 2007

There are two ways to import data from a text file by using Microsoft Office Excel: You can open a text file in Excel, or you can import a data range that is imported as an external data range (external data range: A worksheet from outside of Excel, such as a database or text file). In Excel, you can format or calculate data in an external data range as you would any other data. ) text file. To export data from Excel to a text file, use the Save As command.

The following are two commonly used text file formats:

• Delimited text file (. txt), where each field of text is usually delimited with tabs (ASCII character code 009).

• Comma-delimited value text file (. csv), where commas (,) are usually used to separate each field of text.

You can change the separator characters used in delimited text files and. csv text files. This ensures that you can import or export operations in the way that you want.

Comments

• You can import or export up to 1,048,576 rows and 16,384 columns.

• You may need to learn more about the Excel text converter, whether you are importing or exporting. For more information, see "Text Format" and "Text file format" in Excel formatting and features that Help topics are not converted to other file formats in the supported file formats in Excel.

To import a text file by opening a text file

You can use the Open command to convert a text file created in another program to an Excel workbook.

1, click the Microsoft Office button, and then click Open.

The Open dialog box appears.

2, in the File type box, select text file.

3, in the Look in list, locate and double-click the text file you want to open:

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

Follow the instructions in the Text Import Wizard. Click Help for more information about using the Text Import Wizard, or see the Text Import Wizard. When you have completed the steps in the wizard, click Finish.

• If the file is a. csv file, Excel automatically converts the text file and opens it.

Note When Excel opens a. csv file, it uses the current default data formatting to explain how to import each data column. You may want to be able to use the Text Import Wizard to more flexibly convert columns to different data formats. For example, a data column in a. csv file may have a data format that is MDY, and the default data format for Excel is YMD, or you want to convert a numeric column with leading zeros to text so that leading zeros can be preserved. To use the Text Import Wizard, you can change the type of the file to. txt before you open the file, or import a text file by connecting to a text file.

To import a text file by connecting to a text file

You can import a worksheet's data range from an external data range external data range: From outside of Excel, such as a database or text file. In Excel, you can format or calculate data in an external data range as you would any other data. Text file to import data.

1, click the cell that you want to use to place the text file data.

2, on the Data tab, in the Get External Data group, click From Text.

3, in the Look in list, locate and double-click the text file 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 the Text Import Wizard. When you have completed the steps in the wizard, click Finish.

4. In the Import Data dialog box, do the following:

Optionally, click Properties to set the refresh, formatting, and layout options for the imported data.

Under where you put the data, do one of the following:

• To return the data to the selected location, click Existing Sheet.

• To return the data to the upper-left corner of the new worksheet, click New Sheet.

5, click "OK".

Excel places the external data range at the specified location.

To export data to a text file by saving data

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

1, click the Microsoft Office button, and then click Save As. The Save As dialog box appears.

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

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

3. In the Save As list, browse to the location where you want to save the new text file.

4, in the File name box, view the name that Excel suggests for the new text file, and change the name if necessary.

5, click "Save".

6, a dialog box is displayed to remind you to save only the current worksheet to a new file. If you are sure that the current worksheet is the worksheet that you want to save as a text file, click OK. You can save another worksheet as a separate text file by repeating this procedure for each worksheet.

7, the Second dialog box appears, reminding you that the worksheet may contain features that are not supported by the text file format. If you want to save the worksheet data to a new text file only, click Yes. If you are unsure and want to learn more about Excel features that are not supported by the text file format, click Help for more information.

Change the separator symbol used in a text file

For delimited text files, you can change the separator from tabs to other characters in step 2 of the Text Import Wizard. In step 2, you can also change how consecutive delimiters, such as consecutive quotes, are processed. For more information about using the Text Import Wizard, see the Text Import Wizard.

Change the separator in all. csv text files

1, click the Windows Start menu.

2, click "Control Panel".

3, open the Regional and Language Options dialog box.

4, click the Regional Options tab.

5, click "Custom".

6, in the list separator box, type a new separator character.

7, click "OK" two times.

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