#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    4
    Rep Power
    0

    Consolidate several lines of a CSV file with firewall rules


    Hi guys.
    I have a CSV file, which I created using an HTML export from a Check Point firewall policy.
    Each rule is represented as several lines, in some cases. That occurs when a rule has several address sources, destinations or services.
    I need the output to have each rule described in only one line.
    It's easy to distinguish when each rule begins. In the first column, there's the rule ID, which is a number.

    Let me show you an example. The strings that should be moved are in bold:

    NO.;NAME;SOURCE;DESTINATION;SERVICE;ACTION;
    1;;fwgcluster;mcast_vrrp;vrrp;accept;
    ;;;;igmp;;
    2;Testing;fwgcluster;fwgcluster;FireWall;accept;
    ;;fwmgmpe;fwmgmpe;ssh;;
    ;;fwmgm;fwmgm;;;

    What I need ,explained in pseudo code, is this:

    Read the first column of the next line. If there's a number:
    Evaluate the first column of the next line. If there's no number there, concatenate (separating with a comma) \
    the strings in the columns of this line with the last one and eliminate the text in the current one

    The output should be something like this. The strings in bold are the ones that were moved:

    NO.;NAME;SOURCE;DESTINATION;SERVICE;ACTION;
    1;;fwgcluster;mcast_vrrp;vrrp-igmp;accept;
    ;;;;;;
    2;Testing;fwgcluster-fwmgmpe-fwmgm;fwgcluster-fwmgmpe-fwmgm;FireWall-ssh;accept;
    ;;;;;;
    The empty lines are there only to be more clear, I don't actually need them.

    Thanks!
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    4
    Rep Power
    0
    Hi guys!

    If anyone needs it, I ended up solving this with this code:

    Code:
    import csv 
    # adjust these 3 lines 
    WRITE_EMPTIES = False 
    INFILE = "input.csv"
    OUTFILE = "output.csv"
    with open(INFILE, "r") as in_file: 
      r = csv.reader(in_file, delimiter=";") 
      with open(OUTFILE, "wb") as out_file: 
        previous = None 
        empties_to_write = 0 
        out_writer = csv.writer(out_file, delimiter=";") 
        for i, row in enumerate(r): 
          first_val = row[0].strip() 
          if first_val: 
            if previous: 
              out_writer.writerow(previous) 
              if WRITE_EMPTIES and empties_to_write: 
                out_writer.writerows( 
                  [["" for _ in previous]] * empties_to_write 
                  ) 
                empties_to_write = 0 
            previous = row 
          else: # append sub-portions to each other 
            previous = [ 
              "|".join( 
                subitem 
                for subitem in existing.split(",") + [new] 
                if subitem 
                ) 
              for existing, new in zip(previous, row) 
              ] 
            empties_to_write += 1 
        if previous: # take care of the last row 
          out_writer.writerow(previous) 
          if WRITE_EMPTIES and empties_to_write: 
            out_writer.writerows( 
              [["" for _ in previous]] * empties_to_write 
              )
  4. #3
  5. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,995
    Rep Power
    481
    Originally Posted by starrlol
    (separating with a comma)
    I see no commas in your desired output. Nor do I see empty lines in the output.
    [code]Code tags[/code] are essential for python code and Makefiles!

IMN logo majestic logo threadwatch logo seochat tools logo