Hi All,

I'm currently working on finding a solution to strip/edit a texfile in order to pass it through to Oracle's SQLLOADER which in turn will import the data into a table.

The textfile will need to be imported into a clob column inside the first table after which the imported data will need to be placed in the proper columns but unfortunately I haven't got a clue as to how I need to start but I think Python is my best bet...

The textfile looks like the first attachment.

Now, what I don't need from the textfile is the header and the last part of the textfile starting from "Summary for fw0204.txt".
What I do need in my database import file is the first part of the textfile containing the 'sort of' columns but is a bit distorted with spaces etc...
It should look like this when 'the' script is done with it and it can be passed on to an sqlloader import file:

The output file needs to look like the last attachment

How do I create a python script to use the above data from the textfile into an inputfile for sqlloader?

You help will be much appreciated, thanks in advance!

cheers

Attachments
Importance Pass/Fail  Rule                                Device          Line#        Instance
10         pass       1.2.2.1 - Forbid DHCP Server Service fw0204.txt                           
10         pass       1.1.5.4 - Forbid weak SNMP Community Strings fw0204.txt                           
10         pass       1.1.4.3 - Require Encrypted User Passwords fw0204.txt                           
10         pass       1.1.4.2 - Require Enable Password   fw0204.txt                           
10         pass       1.1.4.1 - Require Local User and Encrypted Password fw0204.txt                           
10         pass       1.1.2.1 - Require Local Password    fw0204.txt                           
10         FAIL       1.2.2.2 - Forbid HTTP Server Service fw0204.txt      356               n/a
7          FAIL       1.2.3.2 - Require Console Logging Severity Level fw0204.txt      2                 n/a
6          pass       1.1.2.4 - Require SSH Version 2     fw0204.txt                           
6          FAIL       1.1.2.4 - Require SSH for Remote Device Access fw0204.txt      2                 n/a
5          pass       1.2.3.8 - Require Timestamps in Log Messages fw0204.txt                           
5          pass       1.2.3.7 - Require System Logging    fw0204.txt                           
5          pass       1.2.3.5 - Require Logging to Syslog Server fw0204.txt                           
5          pass       1.1.2.6 - Require SSH Access Control fw0204.txt                           
5          pass       1.1.2.2-1.1.2.3 - Require PDM-ASDM Access Control fw0204.txt                           
5          pass       1.1.1.4 - Verify that authentication, authorization and accounting AAA configuration uses required servers and protocols. fw0204.txt                           
5          pass       1.1.1.1 - Require AAA Service       fw0204.txt                           
5          FAIL       1.1.2.5 - Require Telnet Timeout for Login Sessions fw0204.txt      2                 n/a
5          FAIL       1.1.2.5 - Require SSH Timeout for Login Sessions fw0204.txt      2                 n/a
5          FAIL       1.1.2.4 - Disable Telnet for Remote Device Access fw0204.txt      422               n/a
5          FAIL       1.1.1.2-1.1.1.3 - Require Local AAA Authentication for Serial, Enable, HTTP, and VTY Lines fw0204.txt      2                 n/a
3          pass       1.2.3.1 - Forbid Console Logging    fw0204.txt                           
3          FAIL       1.2.3.6 - Require Logging Trap Severity Level fw0204.txt      2                 n/a
3          FAIL       1.2.3.4 - Require Logging History Level fw0204.txt      2                 n/a
3          FAIL       1.2.3.3 - Require Logging Facility  fw0204.txt      2                 n/a

Summary for fw0204.txt

#Checks #Passed #Failed %Passed
25      15      10      60     

PerfectWeightedScore ActualWeighedScore %WeightedScore
156                  104                66            

Overall Score (0-10)
6           

Note: PerfectWeightedScore is the sum of the importance value of all rules.
ActualWeightedScore is the sum of the importance value of all rules passed,
minus the sum of the importance each instance of a rule failed
Importance Pass/Fail  Rule                                Device          Line#        Instance
10         pass       1.2.2.1 - Forbid DHCP Server Service fw0204.txt                           
10         pass       1.1.5.4 - Forbid weak SNMP Community Strings fw0204.txt                           
10         pass       1.1.4.3 - Require Encrypted User Passwords fw0204.txt                           
10         pass       1.1.4.2 - Require Enable Password   fw0204.txt                           
10         pass       1.1.4.1 - Require Local User and Encrypted Password fw0204.txt                           
10         pass       1.1.2.1 - Require Local Password    fw0204.txt                           
10         FAIL       1.2.2.2 - Forbid HTTP Server Service fw0204.txt      356               n/a
7          FAIL       1.2.3.2 - Require Console Logging Severity Level fw0204.txt      2                 n/a
6          pass       1.1.2.4 - Require SSH Version 2     fw0204.txt                           
6          FAIL       1.1.2.4 - Require SSH for Remote Device Access fw0204.txt      2                 n/a
5          pass       1.2.3.8 - Require Timestamps in Log Messages fw0204.txt                           
5          pass       1.2.3.7 - Require System Logging    fw0204.txt                           
5          pass       1.2.3.5 - Require Logging to Syslog Server fw0204.txt                           
5          pass       1.1.2.6 - Require SSH Access Control fw0204.txt                           
5          pass       1.1.2.2-1.1.2.3 - Require PDM-ASDM Access Control fw0204.txt                           
5          pass       1.1.1.4 - Verify that authentication, authorization and accounting AAA configuration uses required servers and protocols. fw0204.txt                           
5          pass       1.1.1.1 - Require AAA Service       fw0204.txt                           
5          FAIL       1.1.2.5 - Require Telnet Timeout for Login Sessions fw0204.txt      2                 n/a
5          FAIL       1.1.2.5 - Require SSH Timeout for Login Sessions fw0204.txt      2                 n/a
5          FAIL       1.1.2.4 - Disable Telnet for Remote Device Access fw0204.txt      422               n/a
5          FAIL       1.1.1.2-1.1.1.3 - Require Local AAA Authentication for Serial, Enable, HTTP, and VTY Lines fw0204.txt      2                 n/a
3          pass       1.2.3.1 - Forbid Console Logging    fw0204.txt                           
3          FAIL       1.2.3.6 - Require Logging Trap Severity Level fw0204.txt      2                 n/a
3          FAIL       1.2.3.4 - Require Logging History Level fw0204.txt      2                 n/a
3          FAIL       1.2.3.3 - Require Logging Facility  fw0204.txt      2                 n/a

Summary for fw0204.txt

#Checks #Passed #Failed %Passed
25      15      10      60     

PerfectWeightedScore ActualWeighedScore %WeightedScore
156                  104                66            

Overall Score (0-10)
6           

Note: PerfectWeightedScore is the sum of the importance value of all rules.
ActualWeightedScore is the sum of the importance value of all rules passed,
minus the sum of the importance each instance of a rule failed
Importance Pass/Fail  Rule                                          																			Device          Line#        Instance
10         pass       1.2.2.1 - Forbid DHCP Server Service          																			fw0204.txt                           
10         pass       1.1.5.4 - Forbid weak SNMP Community Strings  																			fw0204.txt                           
10         pass       1.1.4.3 - Require Encrypted User Passwords 																				fw0204.txt                           
10         pass       1.1.4.2 - Require Enable Password   																						fw0204.txt                           
10         pass       1.1.4.1 - Require Local User and Encrypted Password 																		fw0204.txt                           
10         pass       1.1.2.1 - Require Local Password    																						fw0204.txt                           
10         FAIL       1.2.2.2 - Forbid HTTP Server Service 																						fw0204.txt      356          n/a
7          FAIL       1.2.3.2 - Require Console Logging Severity Level 																			fw0204.txt      2            n/a
6          pass       1.1.2.4 - Require SSH Version 2     																						fw0204.txt                           
6          FAIL       1.1.2.4 - Require SSH for Remote Device Access 																			fw0204.txt      2            n/a
5          pass       1.2.3.8 - Require Timestamps in Log Messages 																				fw0204.txt                           
5          pass       1.2.3.7 - Require System Logging    																						fw0204.txt                           
5          pass       1.2.3.5 - Require Logging to Syslog Server 																				fw0204.txt                           
5          pass       1.1.2.6 - Require SSH Access Control 																						fw0204.txt                           
5          pass       1.1.2.2-1.1.2.3 - Require PDM-ASDM Access Control 																		fw0204.txt                           
5          pass       1.1.1.4 - Verify that authentication, authorization and accounting AAA configuration uses required servers and protocols. fw0204.txt                           
5          pass       1.1.1.1 - Require AAA Service       																						fw0204.txt                           
5          FAIL       1.1.2.5 - Require Telnet Timeout for Login Sessions 																		fw0204.txt      2            n/a
5          FAIL       1.1.2.5 - Require SSH Timeout for Login Sessions 																			fw0204.txt      2            n/a
5          FAIL       1.1.2.4 - Disable Telnet for Remote Device Access 																		fw0204.txt      422          n/a
5          FAIL       1.1.1.2-1.1.1.3 - Require Local AAA Authentication for Serial, Enable, HTTP, and VTY Lines 								fw0204.txt      2            n/a
3          pass       1.2.3.1 - Forbid Console Logging    																						fw0204.txt                           
3          FAIL       1.2.3.6 - Require Logging Trap Severity Level 																			fw0204.txt      2            n/a
3          FAIL       1.2.3.4 - Require Logging History Level 																					fw0204.txt      2            n/a
3          FAIL       1.2.3.3 - Require Logging Facility  																						fw0204.txt      2            n/a

What I do need in my database import file is the first part of the textfile containing the 'sort of' columns but is a bit distorted with spaces etc...

Perhaps you could describe with words the precise way the lines are 'distorted'. If it can't be described precisely, it can't be programmed ...

Here simple one, if you use Linux or similar, add appropriate Exception type to mkdir except

import os
import webbrowser

def process(fn):
    with open(fn) as infile, open('./output/'+fn, 'w') as outfile:
        outfile.write(next(infile)[:26] + '\n')
        for line in infile:
            if '.txt' not in line:
                break
            else:
                outfile.write(line.rsplit('.txt',1)[0].rsplit(None,1)[0]+'\n')
    webbrowser.open('./output/' + fn)

try:
    os.mkdir('./output')
#add here exception produced in Linux, if you use that
except WindowsError as e:
    print('Directory exists, using existing one')

process('t1.txt')
This article has been dead for over six months. Start a new discussion instead.