Delete line based on value in specific position

jeremyw · Nov 26, 2008#12008-11-26T16:45+00:00

Not sure if this can be done. I have UE 12.20b+1

What I would like to do is loop through the file and if the following is TRUE, then delete the entire line.

If line begins with OBINP200

AND

Column 522 - 530 value NOT EQUAL to all zero's or blank

AND

Column 537 - 550 value NOT EQUAL to all zero's or blank

Then delete the line.

Once it loops through every Line the begins with OBINP200 and deletes everything that has value's then I want to delete the following Column Ranges.

1 - 14
79 - 518
552 - 1900

Thanks in advance for any help you can provide.

Jane · Nov 26, 2008#22008-11-26T18:40+00:00

Ok,
Just so I'm clear, on the data you supplied, based on the column matching criteria you gave, you would delete all starting with OBINP200 except rows 9, 18, and 27 because these have all zeros at 537-550 - is that correct?

jeremyw wrote:Once it loops through every Line the begins with OBINP200 and deletes everything that has value's then I want to delete the following Column Ranges.

Then you want to remove the ranges specified for ALL ROWS?
There appear to be header rows at rows 1, 10, 19, and 28.
Is this correct?

Jane

jeremyw · Nov 26, 2008#32008-11-26T20:56+00:00

Jane wrote:Just so I'm clear, on the data you supplied, based on the column matching criteria you gave, you would delete all starting with OBINP200 except rows 9, 18, and 27 because these have all zeros at 537-550 - is that correct?

Correct

Jane wrote:Then you want to remove the ranges specified for ALL ROWS?
There appear to be header rows at rows 1, 10, 19, and 28.
Is this correct?

Correct. I'm not worried about the rows that begin with DOCHEADR so those lines can be deleted as well.

Jane · Dec 04, 2008#42008-12-04T01:29+00:00

Sorry for the delay. Nobody seems to have responded with an Ultraedit solution.
I've attached a file of the results (already deleted), incorporating the OBINP100 line you wanted to capture. I'm not sure how your result file has a space between 00639409 and KZZ828064. This started as a solid string and had some characters removed from the centre.

I have a solution using a few lines of Perl. The first line was added to include OBINP100.

Code: Select all

if (/^OBINP100/) {print OUT substr($_,14,8)}
if (/^OBINP200/) {
    if (substr($_,521,9) eq "000000000" or substr($_,521,9) eq "         " or substr($_,536,14) eq "00000000000000" or substr($_,536,14) eq "              "){
        print OUT substr($_,14,8) . substr($_,34,44) . substr($_,517,33) . substr($_,1900,)
    }
}

There are 4 lines of code - the 3rd line has wrapped in the display above
I realize you probably don't have this - otherwise you wouldn't be trying to solve it with UE. However, I won't leave you high and dry.
I can add a couple of lines of code to have it process all files in a directory and print out files matching with groomed results. I can compile it into and EXE file that you can run.
If this is of interest, let me know and I will attach it next response.

Cordially,
Jane

Mofi · Dec 04, 2008#52008-12-04T11:12+00:00

With the few Perl lines from Jane I could easily understand what should be done and wrote the following UltraEdit macro which produces the same result as the Perl script from Jane. I hope, it works also for UE v12.20b+1 because it uses the Perl regular expression engine for making the regular expressions easier, although it would be also possible with the UltraEdit or the Unix regex engine.

The macro property Continue if a Find with Replace not found must be checked for this macro.

InsertMode
ColumnModeOff
HexOff
PerlReOn
Bottom
IfColNumGt 1
"
"
EndIf
Top
Find MatchCase RegExp "^DOCHEAD.+\r\n"
Replace All ""
Find MatchCase RegExp "^OBINP100......(........).+$"
Replace All "\1"
Find MatchCase RegExp "^OBINP200.{6}(.{8}).{12}(.{44}).{439}(.{4}( {9}|0{9}).{20}).+$"
Replace All "\1\2\3"
Find MatchCase RegExp "^OBINP200.{6}(.{8}).{12}(.{44}).{439}(.{19}( {14}|0{14})).+$"
Replace All "\1\2\3"
Find MatchCase RegExp "^OBINP200.+\r\n"
Replace All ""

Add UnixReOn or UnixReOff at the end of the macro if you do not use Perl regular expressions by default - see search configuration. Macro command PerlReOn sets the regular expression option to Perl compatible regular expression.

jeremyw · Dec 04, 2008#62008-12-04T14:49+00:00

Thanks Jane & Mofi. It's strange, the code Mofi gave works on the test file, but when I load a different test fil, it appears to remove all the OBINP100 lines (leaving the customer #), but it doesn't appear to move on to the OBINP200 lines. I'm not sure if you can step through a macro like you can in VBA so I'm not sure how to debug to see what is going wrong.

Any suggestions?

Jane · Dec 04, 2008#72008-12-04T17:21+00:00

I'm not sure but it appears the problem may be with the line terminators in this new file which are \n rather than \r\n as in the first file. The following 2 regex lines in the macro won't match these.

Find MatchCase RegExp "^DOCHEAD.+\r\n"
...
Find MatchCase RegExp "^OBINP200.+\r\n"

If you get rid of the \r in these lines it should work.

Find MatchCase RegExp "^DOCHEAD.+\n"
...
Find MatchCase RegExp "^OBINP200.+\n"

OR
Have Ultraedit convert it to a DOS file when you open it.

Nice job Mofi,
Jane

Mofi · Dec 04, 2008#82008-12-04T18:22+00:00

Jane wrote:I'm not sure but it appears the problem may be with the line terminators in this new file.

Jane, you are absolutely right. I have enabled the configuration settings Automatically convert to DOS format and Save file as input format (UNIX/MAC/DOS) at Advanced - Configuration - File Handling - DOS/UNIX/MAC Handling to work always with DOS terminated lines to avoid problems with macros/scripts/copying/pasting, but keep the line ending format of the file because I work also with UNIX files sometimes. The second test file is a UNIX file as UltraEdit indicates in the status bar at bottom of the UltraEdit main window and without conversion to DOS every \r\n must be changed to \n (UNIX only) or \r*\n (UNIX or DOS, but not MAC).

Jane wrote:Nice job Mofi

Ditto! With your Perl code and your result file it was easy for me to get the idea how to do this with some regular expression replaces.

jeremyw · Dec 04, 2008#92008-12-04T20:44+00:00

PERFECT!!!

If possible, could you help with 2 more things on this file? I guess if I had put it in the original spec it would have been better, but as I'm working with this more, I see 2 more things that would make this easier when I transition this to the person that will be taking over for me.

1. Jane, earlier you asked how I got a space between the 00639409 and the KZZ828064. Those are 2 different fields and I had added a couple of spaces in there to make it a little easier to read. Would it be easy enough to add that to the code to put 2 or 3 spaces after position 9?

2. Is it possible to take the data on Row 1 (60336600) and move it to the line below it and then put 2 or 3 spaces after it? This way all of my data is on a single line. I will be copying this data to a spreadsheet that another user will go off of and if it's all in one row, it will make it easier to work with.

Thank you again for all your help. This is going to be a major time saver.

Jeremy

Jane · Dec 04, 2008#102008-12-04T23:53+00:00

Your request should be easy enough to fill.

jeremyw wrote:Would it be easy enough to add that to the code to put 2 or 3 spaces after position 9?

To do this you just have to put the spaces you need between the \1 and \2 in the replace string

jeremyw wrote:Is it possible to take the data on Row 1 (60336600) and move it to the line below it and then put 2 or 3 spaces after it?

To do this you need to remove the \n after it.
You can't do this initially or it will mess up the start of the regex search on the next line.
However, once it is processed as per Mofi's macro, and if the results are always a 8 digit number, we can do this by a search and replace like this:

Search for a line which 8 digit number - capture the line but not \n
^(\d{8})\n

Replace with "\1 " - note the space after \1

I have added this to the macro below.
I have also added a space between the \1 and \2 in the 2 other lines (in red) to allow separation of the groups you wanted.
If you want more than one space you can add more

InsertMode
ColumnModeOff
HexOff
PerlReOn
Bottom
IfColNumGt 1
"
"
EndIf
Top
Find MatchCase RegExp "^DOCHEAD.+\n"
Replace All ""
Find MatchCase RegExp "^OBINP100......(........).+$"
Replace All "\1"
Find MatchCase RegExp "^OBINP200.{6}(.{8}).{12}(.{44}).{439}(.{4}( {9}|0{9}).{20}).+$"
Replace All "\1 \2\3"
Find MatchCase RegExp "^OBINP200.{6}(.{8}).{12}(.{44}).{439}(.{19}( {14}|0{14})).+$"
Replace All "\1 \2\3"
Find MatchCase RegExp "^OBINP200.+\n"
Replace All ""
Find MatchCase RegExp "^(\d{8})\n"
Replace All "\1 "

It worked fine on the data you sent.
You should be able to do future tweaks by studying what has been done here already.

Cheers,
Jane

jeremyw · Dec 08, 2008#112008-12-08T13:55+00:00

Thanks Jane. I'm driving myself nuts on the last one. I have added the

Find MatchCase RegExp "^(\d{8})\n"
Replace All "\1 "

to the end of the macro and that seems to be the only thing that is not working.

This is the full macro I have.

InsertMode
ColumnModeOff
HexOff
PerlReOn
Bottom
IfColNumGt 1
"
"
EndIf
Top
Find MatchCase RegExp "^DOCHEAD.+\r\n"
Replace All ""
Find MatchCase RegExp "^OBINP100......(........).+$"
Replace All "\1"
Find MatchCase RegExp "^OBINP200.{6}(.{8}).{12}(.{44}).{439}(.{4}( {9}|0{9}).{20}).+$"
Replace All "\1 \2 \3"
Find MatchCase RegExp "^OBINP200.{6}(.{8}).{12}(.{44}).{439}(.{19}( {14}|0{14})).+$"
Replace All "\1 \2 \3"
Find MatchCase RegExp "^OBINP200.+\r\n"
Replace All ""
Find MatchCase RegExp "^(\d{8})\n"
Replace All "\1 "

Jane · Dec 09, 2008#122008-12-09T05:26+00:00

Jeremyw,

One thing - if you look at the last macro I gave you, all line endings were \n including ^(\d{8})\n because you were using a Unix file. In the one you show in the post above you have two that are \r\n, and the last one (^(\d{8})\n) is \n.
You can't mix these!
If yours was working except for the last line I gave you, that means you must have converted the document to DOS. If this is the case then change the last one to \r\n also.
Figure out what type of file you are processing and use the appropriate terminator.

Jane

jeremyw · Dec 09, 2008#132008-12-09T13:33+00:00

Thank you Jane!!!! That is exactly what it was.

Code: Select all

60336600 00644044 KZZ82A464      K64AWC ASST 47811 W812 12         000702407      00000000000000
60336600 00644045 KZZ82A464      K64AWC ASST 47811 W812 12         000702407      00000000000000
60336600 00644046 KZZ82A464      K64AWC ASST 47811 W812 12         000702407      00000000000000
60336600 00644047 KZZ82A464      K64AWC ASST 47811 W812 12         000702407      00000000000000
60336600 00644048 KZZ82A464      K64AWC ASST 47811 W812 12         000702407      00000000000000

I added the \r to the end and everything works.

Thank you very much for your help.

Mofi · Dec 09, 2008#142008-12-09T16:19+00:00

Jane, you are really good. I have not seen that Jeremyw has switched again from UNIX to DOS for the line ending.

Jeremyw, please replace ALL occurences of \r\n by \r*\n in the macro. Then it would not be important anymore if the file you run this macro on uses DOS (carriage return + line feed) or UNIX (line feed only) line endings as I have already written once.

jeremyw · Dec 09, 2008#152008-12-09T16:46+00:00

Thanks Mofi. Made that change.

Thank you both for all your help. This will be a big time saver.