Microsoft KB Archive/214104

{|
 * width="100%"|

XL2000: Macro for Exporting Data to Text Files

 * }

-

The information in this article applies to:


 * Microsoft Excel 2000

-

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 Excel, 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 and that only work with text data. For example, this format is required by mainframe programs and is often necessary for government reporting.

Creating a Flat Text File
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 then click to 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.

The format style of your workbook is now changed so that the column width is based on the number of characters that can be placed in the column.
 * 1) Adjust the column widths to suit your preferences.
 * 2) On the File menu, click Save As.
 * 3) In the Save as type list, click Formatted Text (Space Delimited).
 * 4) Click Save.

Issues with Space-Delimited Format
The following sections contain information about Excel's behavior when exporting the data.

Issue 1: 240-Character Limit
Formatted Text (Space Delimited) (.prn) files have a limitation of 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.

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 that 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 issues when you export by using Flatfile.xla. Microsoft recommends that you remove these formats from the cells before you export the data.

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 is 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 that 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; 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.

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

Keywords : xlloadsave kbhowto xladdin

Version : WINDOWS:2000

Platform : WINDOWS

Issue type : kbhowto kbinfo

Technology : kbvcSearch