There are two ways to import data from a text file with Excel: you can open it in Excel, or you can import it as an external data range. To export data from Excel to a text file, use the Save As command and change the file type from the drop-down menu.
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 ensure that the import or export operation works the way you want it to.
Import a text file by opening it in Excel
Using the Open command, you can open a text file that you created in another program as an Excel workbook. 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).
- Go to File > Open and browse to the location that contains the text file.
- Select Text Files in the file type dropdown list in the Open dialog box.
- 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. When you are done with the steps, 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.
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.
- Go to File > Save As.
- The Save As dialog box appears.
- In the Save as type box, choose the text file format for the worksheet.
- For example, click Text (Tab delimited) or CSV (Comma delimited).
- Browse to the location where you want to save the new text file, and click Save.
- 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 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.
- A second dialog box appears, reminding you that your worksheet may contain features 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 want to know more about which Excel features are not supported by text file formats, click Help for more information.