Consolidate several lines of a CSV file with firewall rules

This is the place for queries that don't fit in any of the other categories.

Consolidate several lines of a CSV file with firewall rules

Postby starriol » Fri Oct 11, 2013 3:30 pm

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:

Code: Select all
NO.;NAME;SOURCE;DESTINATION;VPN  ;SERVICE;ACTION;TRACK;INSTALL ON;TIME;COMMENT
1;;fwxcluster;mcast_vrrp;;vrrp;accept;Log;fwxcluster;Any;"VRRP;;*Comment suppressed*
;;;;;[b]igmp**;;;;;
2;;fwxcluster;fwxcluster;;FireWall;accept;Log;fwxcluster;Any;"Management FWg;*Comment suppressed*
;;[b]fwmgmpe**;[b]fwmgmpe**;;[b]ssh**;;;;;
;;[b]fwmgm**;[b]fwmgm**;;;;;;;
3;NTP;G_NTP_Clients;cmm_ntpserver_pe01;;ntp;accept;None;fwxcluster;Any;*Comment suppressed*
;;;[b]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. The strings in bold are the ones that were moved:

Code: Select all
NO.;NAME;SOURCE;DESTINATION;VPN  ;SERVICE;ACTION;TRACK;INSTALL ON;TIME;COMMENT
1;;fwxcluster,[b]fwmgmpe**,[b]fwmgm**;mcast_vrrp,[b]fwmgmpe**,[b]fwmgm**;;vrrp,[b]ssh**;accept;Log;fwxcluster;Any;*Comment suppressed*
;;;;;;;;;;
;;;;;;;;;;
3;NTP;G_NTP_Clients;cmm_ntpserver_pe01,[b]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.

So any help getting started would be MOST welcomed, I'm still a newbie and would like to start learning with something I actually need.

Thanks!
Last edited by micseydel on Fri Oct 11, 2013 5:53 pm, edited 1 time in total.
Reason: First post lock.
starriol
 
Posts: 4
Joined: Fri Oct 11, 2013 3:24 pm

Re: Consolidate several lines of a CSV file with firewall ru

Postby starriol » Fri Oct 11, 2013 8:11 pm

I made a mess up there, and I can't edit it.
Please see the correct code here, and an image explaining this more clearly:

Image

Original CSV:

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

After running the script:

NO.;NAME;SOURCE;DESTINATION;SERVICE;ACTION;
1;;fwgcluster;mcast_vrrp;vrrp-igmp;accept;
;;;;;;
2;Testing;fwgcluster-fwmgmpe-fwmgm;fwgcluster-fwmgmpe-fwmgm;FireWall-ssh;accept;
;;;;;;
starriol
 
Posts: 4
Joined: Fri Oct 11, 2013 3:24 pm

Re: Consolidate several lines of a CSV file with firewall ru

Postby micseydel » Sat Oct 12, 2013 11:38 pm

I've been looking at this on and off for the last day or so and I'm not really sure what you're trying to accomplish. It looks like you can do it (whatever it is) using these steps though, assuming your text file isn't huge:
Code: Select all
import re

your_string = '...'

parts = re.split('\n\d+', your_string)

for part in parts:
    for line in part.splitlines():
        values = line.split(';')
Join the #python-forum IRC channel on irc.freenode.net!

Please do not PM members regarding questions which are meant to be discussed publicly. The point of the forum is so that others can benefit from it. We don't want to help you over PMs or emails.
User avatar
micseydel
 
Posts: 1390
Joined: Tue Feb 12, 2013 2:18 am
Location: Mountain View, CA

Re: Consolidate several lines of a CSV file with firewall ru

Postby starriol » Sun Oct 13, 2013 12:14 am

Hey Mic!
A few hours before you replied, I ended up solving it with another code.

Thank you anyway.

Here it is, in case anyone is interested:

Code: Select all
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
          )
starriol
 
Posts: 4
Joined: Fri Oct 11, 2013 3:24 pm


Return to General Coding Help

Who is online

Users browsing this forum: W3C [Linkcheck] and 2 guests

cron