Delete line based on value in specific position

Delete line based on value in specific position

7
NewbieNewbie
7

    Nov 26, 2008#1

    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.
    TestFile.zip (1.49 KiB)   293
    The attached file only has a few lines to loop through, but my daily file could have thousands of lines.

    22
    Basic UserBasic User
    22

      Nov 26, 2008#2

      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

      7
      NewbieNewbie
      7

        Nov 26, 2008#3

        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.

        22
        Basic UserBasic User
        22

          Dec 04, 2008#4

          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

          6,681583
          Grand MasterGrand Master
          6,681583

            Dec 04, 2008#5

            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.
            Best regards from an UC/UE/UES for Windows user from Austria

            7
            NewbieNewbie
            7

              Dec 04, 2008#6

              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?

              22
              Basic UserBasic User
              22

                Dec 04, 2008#7

                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

                6,681583
                Grand MasterGrand Master
                6,681583

                  Dec 04, 2008#8

                  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.
                  Best regards from an UC/UE/UES for Windows user from Austria

                  7
                  NewbieNewbie
                  7

                    Dec 04, 2008#9

                    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

                    22
                    Basic UserBasic User
                    22

                      Dec 04, 2008#10

                      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

                      7
                      NewbieNewbie
                      7

                        Dec 08, 2008#11

                        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 "

                        22
                        Basic UserBasic User
                        22

                          Dec 09, 2008#12

                          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

                          7
                          NewbieNewbie
                          7

                            Dec 09, 2008#13

                            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.

                            6,681583
                            Grand MasterGrand Master
                            6,681583

                              Dec 09, 2008#14

                              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.
                              Best regards from an UC/UE/UES for Windows user from Austria

                              7
                              NewbieNewbie
                              7

                                Dec 09, 2008#15

                                Thanks Mofi. Made that change.

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