Sunday, 14 October 2007

Fixed-width Text File Editor - Excel.

Recently I needed to work with NAV to produce some fixed width text files. I wanted a tool to help me check the output file against my file specification. What I really needed was something that let me see the contents of the file nicely split up by column with the columns numbered along the top of the file.

I did a quick search on Google and found little. Then I thought, what about Excel? Maybe I could use Excel to somehow show the file in the format I wanted. Well It was remarkably easy.

I created two worksheets. My first sheet simply contained one cell which contained the full text of the record in my file. Each row contained one record.

In my second sheet, I created a series of column headings and then used the MID function to take a single character from my first sheet based upon the column I was in. The formula looked like this:

MID('Text File'!$A1,Columns!B$1,1)

I then copied this function to all of my cells. I also put in some row headings and some conditional formatting to change the colour of the background if the length of the field was 0 (i.e. no character.)

After generating my text file, I would open it in notepad, select all (Ctrl+A) and copy it, then paste into the Text File worksheet of excel and check the data in the columns against my specification.

It did the job quite nicely.

1 comment:

Jay N said...

This is useful.

My problem, is that I need to then edit one character in some but not all of a 7000 row file and save it out again as a good fixed-width file. That is a more complicated task; I haven't found a good solution. Any suggestions are appreciated.

I do like your way of looking character by character--it helped identify the issue.