Conversion of Fixed Width Text Files

Here we discuss conversion of 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 justification 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

Choosing the ‘Convert XLS’ conversion method provides a world of flexibility. With this method you have 3 choices on how to handle the conversion (as shown immediately below) one of them allowing you to specify each column width, pad character and justification. A built in fixed width text profile editor has been included (press F3 to display it) and is also discussed in detail below. 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).

There are 3 choices for how the output file will be created. The first two choices, 1 and 2 are specifically meant for converting from an XLS file to a text file. Choice 3 can be used for converting both 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. The 3 choices for conversion are:

1) Specify column sizes, pad characters and alignments is the most powerful of the 3 choices. Here you can specify exactly how many characters each column will have, what pad character to use and the justification. Please see the Using the Fixed Width Text Profile Editor section for further information on specifying this information.

2) 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.

3) 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.

Additional options you should know about

In addition to specifying the width, pad character and justification 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 to the Conversion menu item and pointing to the Conversion Options (F2) then selecting the Excel tab.