XL: Exporting Data to Text Files

ID: Q90914

The information in this article applies to:

SUMMARY

Microsoft Excel versions 3.0 and 4.0 provide an add-in macro called Flatfile.xla. You can use this macro to export all or part of a worksheet to a text file in the "flat file" format.

To create a flat file in Microsoft Excel versions 5.0 and later, click Save As on the File menu, and then click Formatted Text (Space Delimited).

When data is stored in the flat file format, it is stored in fixed- width columns that are separated by spaces instead of commas or tabs.


MORE INFORMATION

The flat file format is used to create text files that you can open in other programs that only work with text data. For example, this format is required by mainframe programs and is often necessary for government reporting. To create a flat text file, use the method that is appropriate for your version of Microsoft Excel.

Creating a Flat Text File in Excel 5.0 and Later

To create the file, follow these steps:

  1. Open the file that you want to save as a flat file.


  2. Click Style on the Format menu and clear all check boxes except Font.


  3. Click Modify, click the Font tab, and then click a non-Proportional font (for example "Courier New") from the Font list.


  4. Click OK twice.

    This will change the format style of your workbook so that the column width will be based on the number of characters that can be placed in the column.


  5. Adjust your column widths as desired.


  6. On the File menu, click Save As.


  7. In the Save As Type list, click Formatted Text (Space Delimited).


  8. In Microsoft Excel version 5.0 for Windows, click OK. In other versions of Microsoft Excel, click Save.


Issues with Space-Delimited Format in Microsoft Excel 5.0 or Later

The following sections contain information about behavior when exporting the data.

Issue 1: 240 Character Limit

Formatted Text (Space Delimited) (.prn) files have a limitation of 240 characters per line.

For additional information, please see the following article in the Microsoft Knowledge Base:

Q131554 : XL: Formatted Text (.prn) Limited to 240 Characters Per Line



Issue 2: Numeric Data Formatted with Accounting Format Lost

Numeric data may be lost in fields that are formatted with the Accounting format because the "Formatted Text (Space delimited)(*.prn)" file format does not support the number formats in the Accounting formatting category.

ARTICLE-ID: Q178128 : XL: Numeric Data May Be Lost When Saving File in PRN Format

Creating a Flat Text File in Excel Versions 3.0, and 4.0 with Flatfile.xla

To create the file, follow these steps:

  1. In Microsoft Excel for Windows, open Flatfile.xla (located in the Library directory in the location that you installed Microsoft Excel).

    In Microsoft Excel for the Macintosh, open Flat File (located in the Macro Library folder in the location that you installed Microsoft Excel).


  2. Open the worksheet from which you want to export data.


  3. Select the cells containing the data you want to export. If you want to export everything, select the entire worksheet.


  4. On the Data menu, click Export.


  5. In the To Filename box of the Export dialog box, type the name you want to give your text file.

    NOTE: If you want to use an extension (such as .txt) with your file name in Microsoft Excel for Windows, you must type it (for example, Sales.Txt).


  6. If you want to retain the alignment and number formats of your exported data, click the Retain Cell Formats check box in the Export dialog box.

    NOTE: If you click the Retain Cell Formats check box, one extra space is inserted between columns.


  7. In the Export dialog box, click Export.


Microsoft Excel creates your text file in the current directory. To determine the current directory, click Open on the File menu and note the open directory in the directory list. In Microsoft Excel for the Macintosh, the name of the current folder is displayed above the document list in the Open Document dialog box.

Issues with Flatfile.xla

The following sections contain information about the alignment and number formatting, column widths, and fonts in the exported data.

Issue 1: Alignment and Number Formatting with Flatfile.xla:

If you use the Export command to export data from your worksheet, you have the ability to keep the alignment and number formatting of the cells you are exporting. You also have the ability to use General alignment and number formatting for all data. If you use General alignment and number formatting, text data is aligned to the left, and numeric data is aligned to the right. Formatting, such as currency or custom number formatting, is not saved. 


   NOTE: The Fill, Justify, and Center Across Selection formats, may cause
   problems when you export by using Flatfile.xla. Microsoft recommends
   that you remove these formats from the cells before you export the
   data. For additional information, query on the following words in the
   Microsoft Knowledge Base: 



   Excel: Cell Alignment Formats Cause Flatfile Export to Fail


Issue 2: Column Widths with Flatfile.xla:

The column width in your text file is equal to the whole number portion of the column width for the selected cells. For example, if the column width is 12.45, the column in the text file will be 12 characters wide. Any characters beyond the twelfth character in a cell are not included in the text file. Before you export data, make sure that the columns in the worksheet are wide enough to accommodate all of the characters in the cells you want to export.

Issue 3: Fonts with Flatfile.xla:

If your worksheet is formatted with proportional fonts (for example, Times New Roman or MS Sans Serif), you may need to increase the width of the columns to ensure that all of the data is exported properly. With proportional fonts, some characters are narrower than others are; therefore, more characters may be in a cell than the number that is indicated by the column width. As a result, these characters are cut off when you export the data (see "Issue 2: Column Widths with Flatfile.xla").

To ensure that all of the data is exported properly, format all of the data with a non-proportional font, such as Courier, or experiment with various column widths until all of the data is exported.


REFERENCES

For more information about this issue, please see the following resources:

"Microsoft Excel User's Guide," version 3.0, pages 683-684

"Microsoft Excel User's Guide 2," version 4.0, page 152

Additional query words: 3.0 XLA fixed length xl font export flatfile space delimited ascii ansi limitation line width 


Keywords          : xlloadsave xladdin 
Version           : MACINTOSH:3.0,4.0,5.0,5.0a; WINDOWS:3.0,4.0,5.0,5.0c,7.0; winnt:5.0
Platform          : MACINTOSH WINDOWS winnt 
Issue type        : kbhowto

Last Reviewed: May 17, 1999