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.
Julienldionne's Weblog 