Macro to ensure file conforms to Fixed Width

adrianm · Feb 07, 2007#12007-02-07T18:21+00:00

Hello again!
I am dealing with a large INP 'Fixed Width' file that after much faff I have now got down to approx. 160 errors.
These were mainly due to the original file containint non ASCII characters which were pushing rows 'out of sync'.
I have tried the UTF8 to ASCII converstion option which seemed to get rid of a load of problems, but there are 160 odd left.

An example of a problem row would be a row where the title starts at position 134 instead of 135. I have to locate the row out of sync and mouse click just before the Title and hit the space bar to move everything that follows in the row along one byte so the fixed width structure is correct.
This is a bit of a nightmare - esp as you get an hour glass of 10 seconds when you perform the next 'goto' search!

SO - I was wondering if there is macro or easy way to scan the whole file and if anything is not in it's correct column it will automatically position the start point for that field at the correct position.
I know there is the 'convert to fixed columns' option - but not sure if that will fix the 160 rows that are starting at the wrong position. Will it ignore any character left say in column 134 - thus leaving it there and corrupting the file?

I am not sure I am explaining this well - but if you could offer any advice - that would be great!

Huge thanks!
a
ps - I am using Ultra Edit32 v 9.10a

pps - the fixed width file structure I need to adhere to is:

2,16,16,100,80,80,80,80,30,4,8,8,8,8,8,8,13

Mofi · Feb 07, 2007#22007-02-07T19:26+00:00

You have not posted a correct and a wrong line as an example. So I can give you only a general answer.

A regular expression search can be used to find lines where a special character is not in a specified column. For example the following UltraEdit style regular expression search string will find all lines with at least 11 characters and the 11th character is not a comma: %??????????[~,]

You can use the forum search to find other examples. Simply search for ??????? (strange search string, I know).

Bego · Feb 07, 2007#32007-02-07T19:32+00:00

Hi Adrian,

can you also send some rows completely as an example, best also use the "code" formatting and put your lines inside

Code: Select all

just like this

What is the rule for "to be shifted to the right" ?
A blank ?

Code: Select all

12This is 16 ok   Start of 2nd 16 ok ..... 
34This is 16 ok  Start of 2nd 16 NOK! .....
56This is 16 ok    Start of 2nd also NOK! .....

Oh, Mofi, auch da

adrianm · Feb 07, 2007#42007-02-07T20:08+00:00

Here is an e.g
Artist2xx should start at position 135, not 134.
I think appostrophes have proven to be a problem as a lot of lines that have gone out of sync contain appostrophes in the title.

Argh! Sorry- I am having trouble uploading an e.g. I tried as a JPEG - nope - as a txt file - nope.
Upload Error: Could not upload Attachment to ./uploads/forums/outof_sync.jpg.
Is there an email address instead I could send to>?

Bego · Feb 07, 2007#52007-02-07T21:24+00:00

Suggestion: Place it on one of those 10.000 free upload servers in the internet.

adrianm · Feb 08, 2007#62007-02-08T15:54+00:00

Alas don't have access to such websites here.

Not sure if this describes it better?

Code: Select all

10       HOLD ON          ARTIST1      3350004
10       IT'S WRONG      ARTIST2      3350004
10       OK AGAIN         ARTIST1      3350004

Row 2 is out of sync by one position.

Mofi · Feb 08, 2007#72007-02-08T17:36+00:00

Please use

Code: Select all

if you want to post an example with multiple spaces. See the BBCode items above the edit field. I have edited your post, hopefully correct.

But I think my answer was already enough. Use an UltraEdit style regular expression search (or maybe replace).

For example the following will search for all lines which has at least 134 characters and the character in column 134 is not a space. Column numbering starts with number 1.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Find RegExp "%^(??????????... and further 120 ? ...???^)^([~ ]^)"
Replace "^1 ^2"

Or this macro for your example:

InsertMode
ColumnModeOff
HexOff
UnixReOff
Find RegExp "%^(?????????????????????????^)^([~ ]^)"
Replace "^1 ^2"

You can also use a simple regular expression replace all to mark all lines which have exactly 549 characters per line. The lines without the mark must be fixed.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Top
Find RegExp "%^(??????????... and further 520 ? ...?????????^)$)"
Replace All "#^1"

Use Find In Files with Open Files or Find with option List Lines Containing string with following regular expression search string to find all lines which does not start with the # character: %[~#]

Fix those lines and remove the # character with

Find RegExp "%#"
Replace All ""

adrianm · Feb 08, 2007#82007-02-08T18:48+00:00

Thank you - I shall give this a go!