Thursday, January 10, 2008

Editing Fixed Field Length Compensation Text Files, Reports and Mainframe Files

Today my client asked me if I could take the content of an Excel spreadsheet, and export it to a text file in a very specific format. It needed to follow this format to be imported and interpreted properly by other applications. I said the task would be done quickly, before realizing that the spreadsheet had about 10,000 rows, containing complex sales transaction and compensation information which would need to be edited significantly.

When dealing with fixed field length formatted text files, text reports and mainframe files, it seems common that we need to edit something. For example it could be an ID which changed format from 8 to 9 digits and requiring a leading '0'. It could also be fields need to be moved around in the file.

To avoid some headaches and save a lot of time, do yourself a favor and download TextPad. TextPad is a simple (FREE) text editor for Windows which allows you to perform simple but powerful tasks.

TextPad offers th TextPad allows you to do vertical selections.

The image below illustrates what I mean by vertical selection.

By holding down the "Alt" key, and performing a "Click and Drag" just like when selecting text from left to right, you can select text from top to bottom.

As I said, I could select the entire Last Name "column" in this fashion, cut it, and paste it before the First Name "column". I could also select the first character of the first column and delete them entirely. Finally, if I needed the ID here to be 8 digits intead of 5, I could add manually the missing zeros, copy a few rows of those zeros by using the vertical section, and paste them before the location in which you want them to appear.

Again just to illustrate this these would be the steps to insert characters before a column:
  • Insert manually the zeros for a few rows

  • Select the zeros using the vertical selection method

  • Click on copy or press "ctrl-c"
  • Place the cursor where the new "column" will appear (in this case just before ID 0070)
  • Click on paste or press "ctrl-v"

As you can see, the 2 following rows following 0000017 and 0000023 get the coped "000" appended to them.

That's all there is to this trick, but hopefully it can, as it did for me, save you countless hours of editing large compensation files row-by-row.

1 comment:

Julien Dionne said...

Vertical selection in TextPad, also called block selection, will only work if word wrapping is disabled in the configure menu.

I learned this the hard way when a reader said block section didn't work with her TextPad version...

Another option is available in the Configure Menu. When selecting "Block Select Mode", you don't need to press any key (ALT) to select a block; the standard click and drag will do the trick.

I upgraded today from TextPad version 5.0 to TextPad 5.2.

Blog Search

Blog Directories

Sales Blogs - BlogCatalog Blog Directory

Enter your email address:

Delivered by FeedBurner

Add to Technorati Favorites

Companies Linking to Me


About Me

My photo
Ottawa, Ontario, Canada
Julien Dionne is a well-rounded consultant with global business management experience and outstanding technical, business and leadership skills. He earned a Bachelor of Applied Science in Software Engineering from the University of Ottawa, Canada, and he is a member of the Canadian Professional Sales Association. The views posted within this blog do not reflect the views of Julien’s current or previous employers and clients. Julien can be reached at