Here we provide a step by step example of how to convert the sheet named FlowerPower within an Excel file (say c:\input\Flowers.XLS) into a fixed width text file (c:\output\Floral.TXT). For the example, let's say we are given the format specification below:
Character Position |
Number of Characters |
Field |
Pad With |
Alignment |
1-7 |
7 |
Date |
Space |
Left |
8-19 |
12 |
Amount |
Space |
Left |
20-23 |
4 |
State |
Space |
Right |
24-102 |
79 |
Description |
Space |
Left |
103-181 |
79 |
Flower Type |
Space |
Left |
182-260 |
79 |
Destination |
Space |
Left |
In summary we have 6 columns that are all padded with the space character and all but the 3rd column is left aligned. Follow these directions to accomplish this task:
Run 'Convert XLS' and set the Action To Do to Convert Files, and the Conversion Method to Use 'Convert XLS'.
Press F3 to display the fixed width profile editor.
Enter 1 for the Total Number of Columns, the click the Apply button.
Enter 79 for the Width, choose the [32] Space for the Pad With, and Left for the Alignment.
Enter 6 for the Total Number of Columns, the click the Apply button
Click on the first row in the grid and type in 7 for the width, then <enter>. Typing <enter> takes you to the next item.
Type 12<enter>4<enter> (Note the last three columns already have the width of 79)
Click on the 3rd row in the grid and specify Right alignment. Double clicking the 3rd row is a quick way to toggle the alignment. We are done editing the profile.
At this point you may want to give a unique name to this profile, if so enter it in the Profile Name drop down list box.
Click the Save and Close button
Press F2 to display the conversion options and click on the Excel tab. Specify FlowerPower for the sheet to convert. If in your case it is the 2nd sheet within the workbook, you can use 2 for the sheet identifier. You may also specify other things here such as a range (i.e. "B2:J10"). Click the Apply button to return to the main user interface.
Enter c:\input\Flowers.XLS for the Input File and c:\output\Floral.TXT for the Output File. Be certain the Output File Type is what you want it to be, in our case, [21] Text (ASCII) Fixed Width (*.TXT). The Input File Type should be specified as an Excel Workbook (either xls or xlsx).
Click Add, then Convert.
In the future if you have a similar fixed width text profile required, you can skip steps 3-9 and simply select the profile from the list provided. Furthermore, you can save the program configuration know as a Conversion Job from the file menu to recall at a later time.
See also:
Conversion of Fixed Width Text Files
Using the Fixed Width Text Profile Editor
Step by Step Example: Converting from Excel to a Fixed Width Text File
Step by Step Example: Converting from a Fixed Width Text File to Excel