- slide 1 of 2
See all Microsoft Excel tips
Strategy: There are a couple of options. Typically, the other application will either want each column to be separated by a fixed number of spaces or separated by a comma. Files with columns separated by a comma are called Comma Separated Values, or CSV files. CSV files are easier to create than space-separated files.
Method 1: Create a CSV file.
1) Use File – Save As. In the Save as Type dropdown, choose CSV, as shown in Fig. 87.
2) Choose the Save button. Important: Only the current worksheet is saved in the CSV file. If you have multiple worksheets in the workbook, you will have to save each worksheet separately. Also, Excel will generally warn you that you are saving the file in a format that will leave out incompatible features, as shown in Fig. 88.
3) You can generally keep two versions of each file. Save it as XLS and then save it as CSV.
Result: Fig. 89 shows the created file as it appears when edited with Notepad. Pay particular attention to the “Molson, Inc” entry. Because cell D4 already had a comma, Excel was smart enough to surround Molson, Inc with quotation marks, as shown in Fig. 89.
Gotcha: The dates in column C are written to the file in the same format as they were shown on the worksheet. Most programs will not understand a date such as “1-Jan-04”. You probably should format column C to appear as mm/dd/yyyy before exporting to CSV. Check the documentation of the program that will import the information.
Method 2: The second option is to create a file where each field is supposed to take a fixed number of characters. In this case, the other application will usually give you a file specification for you to follow. It might indicate:
Refer to Chart 1 below.
1) If this is the case, you will have to go through the columns in the worksheet, resetting the column width. If the other program expects the Region field to be 12 characters wide, select column A and use Format – Column – Width to change to 12, as shown in Fig. 90.
2) As shown in Fig. 91, format the dates as specified by the other system. Make sure that the Revenue, Cost, and Profit columns show two decimal places. The other system probably will not want field headings. Delete row 1.
3) Use File – Save As. In the Save as Type dropdown, select Formatted Text (Space Delimited), as shown in Fig. 92.
Note: Excel changes the file name to have a .prn extension. Even if you try
to change the extension to .txt here, Excel will still save the file as SaveAsText.Txt.Prn, as shown in Fig. 93. It is best to leave it as .prn and then rename in Windows Explorer.
4) As shown in Fig. 94, Excel will warn you that you will lose features if you have multiple sheets. Choose OK.
Fig. 95 shows the resulting file as viewed in Notepad:
Summary: Data in Excel can easily be exported to a text file. Determine if the receiving system needs CSV or text formatted by spaces before exporting.
Commands Discussed: File – Save As
See all Microsoft Excel tips