Sample Fixed Width Text File

I have a post or two in my head that deals with fixed width text files, something I’ve been dealing with quite a bit. Like me, you probably don’t run into fixed width text files in your personal life. It’s usually some crappy computer program at your job that forces you to deal with them. The problem is that I can’t simply throw around private company information, so I had to replicate a real fixed width file with obfuscated information. Before I post about all the lovely things I’ve been doing with these files, I thought I’d post about how I made the sample.

fixed width text file sample

That procedure is a bit longer than I like, but it’s not something I wanted to spend a lot of time on. It reads in the legitimate file, then goes line-by-line through it. When it encounters a line that needs changing, it creates fake data to put in place of the real data. I want the totals to match so I have something to compare it to after I parse the text file (in a later post), so I have to keep track of the fake numbers I make up and total them appropriately.

The GL Account is a 10 digit number that start with 1-5 and can have any other digits after that.

The description obfuscator respects spaces, but replaces anything else with an upper case letter.

The Yes/No generator gives me a Yes about 80% of the time. You have to stay positive, you know.

For the debits and credits, I wanted to stay somewhat realistic, so I kept the same scale as whatever number is there. To determine the scale, I remove all spaces, commas, and periods from the screen, then take the length x 2. A number like $9,453.65 will have a scale of 4 and will produce a number between 1,000 and 9,999. Rnd, as you know, generates a number between 0 and 1. I multiply Rnd by 10^Scale to get the right number of digits, then round it to two decimal places.

Finally, I have a Pad function to fill out spaces around numbers. First, Pad truncates the string to the right width in case it’s already too long. If it’s not too long, spaces are inserted in front of it to fill out the right width. Then there’s the sAfter argument that you’re probably wondering about. I don’t have trailing negatives in this report, but I have had them in some others. I need to be able to stick a negative or a space after the string and used the sAfter argument to do it. I believe they’re all null strings in this example.

Making realistic sample data sucks.

You can download FixedWidthExample2.txt

6 thoughts on “Sample Fixed Width Text File

  1. YES! Some of the files I have to deal with are fixed width for upload to the IRS. They are so much fun to work with and Excel does not play nice with them.

    Here is an example: http://www.irs.gov/pub/irs-pdf/p1220.pdf

    Around page 80 the real fun starts.
    They add a bonus challenge by having the first row or two of the reports be completely different sizes and information then the rest of the report.

  2. I was thinking the same thing as John (create it and PRN it.)

    I’d use a UDF that does the obfuscation for the description. That’d be a hairy set of formulas, though if you set up a range of randomly-created letter combos in various lengths you could probably create a lookup approach and assemble them formulaically. Too late on Friday to solve that one…

  3. Random description….

    =MultConc(CHAR(LARGE(ROW(65:90),ROW(INDIRECT(“1:”&RANDBETWEEN(1,26))))))

    MultConc being a UDF that Excel should have always had


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *