Excel: Exporting Data to Text Files

• Microsoft Excel for Windows, versions 3.0, 4.0, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
• Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a
• Microsoft Excel for OS/2, versions 3.0
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

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, the data 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 computer programs that work only with text data. For example, this format is typically required by mainframe programs and is often necessary for government reporting. To create a flat text file, use the following 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. On the File menu, click Save As.

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

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

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 directory in which you installed Microsoft Excel).

   In Microsoft Excel for the Macintosh, open Flat File (located in the Macro Library folder in the folder in which 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 or select the entire worksheet if you want to export everything in it.

4. On the Data menu, click Export.

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

   NOTE: In Microsoft Excel for Windows, if you want to use a file name extension (such as .txt) with your file name, you must include it in the file name you type, 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 the Export button.

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:

When you use the Export command to export data from your worksheet, you have the option to keep the alignment and number formatting of the cells you are exporting. You also have the option to use General alignment and number formatting for all data. When 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

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 insure 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 insure that all of the data is exported properly, format all of the data with a monospace (non-proportional) font, such as Courier, or experiment with various column widths until all of the data is exported.

REFERENCES

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

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