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:
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.
Post a Comment