Consolidate several lines of a CSV file with firewall rules

[Tim Chase]

On 2013-10-11 08:01, Starriol wrote:
> NO.;NAME;SOURCE;DESTINATION;VPN  ;SERVICE;ACTION;TRACK;INSTALL
> ON;TIME;COMMENT
> 1;;fwxcluster;mcast_vrrp;;vrrp;accept;Log;fwxcluster;Any;"VRRP;;*Comment
> suppressed* ;;;;;igmp**;;;;;
> 2;;fwxcluster;fwxcluster;;FireWall;accept;Log;fwxcluster;Any;"Management
> FWg;*Comment
> suppressed* ;;fwmgmpe**;fwmgmpe**;;ssh**;;;;; ;;fwmgm**;fwmgm**;;;;;;;
> 3;NTP;G_NTP_Clients;cmm_ntpserver_pe01;;ntp;accept;None;fwxcluster;Any;*Comment
> suppressed* ;;;cmm_ntpserver_pe02**;;;;;;; 
> 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:
> 
> NO.;NAME;SOURCE;DESTINATION;VPN  ;SERVICE;ACTION;TRACK;INSTALL
> ON;TIME;COMMENT
> 1;;fwxcluster,fwmgmpe**,fwmgm**;mcast_vrrp,fwmgmpe**,fwmgm**;;vrrp,ssh**;accept;Log;fwxcluster;Any;*Comment
> suppressed* ;;;;;;;;;; ;;;;;;;;;;
> 3;NTP;G_NTP_Clients;cmm_ntpserver_pe01,cmm_ntpserver_pe02**;;ntp;accept;None;fwxcluster;Any;*Comment
> suppressed* ;;;;;;;;;;
>
> The empty lines are there only to be more clear, I don't actually
> need them.

Though there are a couple oddities in your source data, I'll take a
crack at it.  First, there are the dangling open-quotes on #1 that
cause most CSV parsers (I tested both Gnumeric and Python's csv
module) to read until the subsequent line is read.  If this is
intentional, then it's all good.  If not, you get weird behaviors.
That said, you can try the code below (adjusting the 3 lines for
your desired filenames and whether you *want* to write emptied rows)
to see if it gets you what you want:

##########################################################

import csv
# adjust these 3 lines
WRITE_EMPTIES = True
INFILE = "input.txt"
OUTFILE = "output.txt"
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
          )
####################################################

Hope this helps,

-tkc