Here we discuss conversion to/from fixed width text files. The 'See also' section below includes how to use the fixed width text profile editor and provides step by step 'how to' examples.
Using the 'MS Excel' Conversion Method
Remember, there are two conversion methods, 'Convert XLS', and 'MS Excel'. If you select the 'MS Excel' conversion method the output file type should be set to "[36] Text Printer File (*.prn)". MS Excel determines the column width, pad character and alignment (left or right) for you. Note that output file types "[21] Text (MS-DOS)" and "[42] Unicode Text" are tab delimited files, not fixed width text files. Using the 'MS Excel' conversion method, you can convert any file MS Excel can open to a fixed width text file.
Using the 'Convert XLS' Conversion Method
You have 3 choices for how the output file will be produced. The first two choices, 1 and 2 are specifically meant for converting from a Excel file to a TXT file. Choice 3 can be used for converting to and from a fixed width text file. All of these choices are available by pressing F2 (or the clicking on the Options button) then selecting the Converting to/from a fixed width text file tab.
1) Retain column width as seen within MS Excel will produce a text file with the same spacing as seen when you open that file within MS Excel. With this option you risk losing data that is truncated due to insufficient column size.
2) Autosize column width to retain all data allows you to optionally have the columns of the Excel file 'auto-sized/auto-formatted' to the appropriate width, so that when converting to a text file, all the data will be retained.
3) Specify column sizes, pad characters and alignments is the most powerful of the 3 choices! With this option you can specify:
A) Width: Exactly how many characters each column will have.
B) Pad Character: What pad character to use. When there is empty space (the data is smaller than the width specified) this character will be used. Most often the Space character is used.
C) Alignment: If padding was required, you can choose to align the data to the left or right of the pad characters.
D) Remove: If you don't want some of the columns, no problem!
E) Position: Move the position of the columns. That's right, you can re-order the columns to anything you want.
You can convert from XLS, XLSX, XLSM and CSV to a fixed width text file. You can also convert from a fixed width text file to XLS, XLSX, CSV, XML, or HTML. Even converting from a fixed width text file to a fixed width text file is possible (you may want to extract a 'range' or change to/from Unicode in this case).
A special Fixed Width Text Profile Editor has been provided to make this very easy to use (press F3 to display it).
Additional options you should know about
In addition to specifying the width, pad character, alignment etc. for each column you should be aware of several other options. For example, if you are converting from an Excel file you may want to specify which sheets to convert, whether or not to create a new file for each of the sheets specified. Whether or not the input file is an Excel, you could specify a range to use. A range could be something like "A1:B10" or "D:D" to only use the fourth column for example. All of these items are available by going clicking the Options button then selecting the Excel tab.
See also:
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