Python - Parsing a text file into a csv file

Question

I have a text file that is output from a command that I ran with Netmiko to retrieve data from a Cisco WLC of things that are causing interference on our WiFi network. I stripped out just what I needed from the original 600k lines of code down to a couple thousand lines like this:

AP Name.......................................... 010-HIGH-FL4-AP04
Microwave Oven      11       10      -59         Mon Dec 18 08:21:23 2017   
WiMax Mobile               11       0       -84         Fri Dec 15 17:09:45 2017   
WiMax Fixed                11       0       -68         Tue Dec 12 09:29:30 2017   
AP Name.......................................... 010-2nd-AP04
Microwave Oven             11       10      -61         Sat Dec 16 11:20:36 2017   
WiMax Fixed                11       0       -78         Mon Dec 11 12:33:10 2017   
AP Name.......................................... 139-FL1-AP03
Microwave Oven             6        18      -51         Fri Dec 15 12:26:56 2017   
AP Name.......................................... 010-HIGH-FL3-AP04
Microwave Oven             11       10      -55         Mon Dec 18 07:51:23 2017   
WiMax Mobile               11       0       -83         Wed Dec 13 16:16:26 2017

The goal is to end up with a csv file that strips out the 'AP Name ...' and puts what left on the same line as the rest of the information in the next line. The problem is some have two lines below the AP name and some have 1 or none. I have been at it for 8 hours and cannot find the best way to make this happen.

This is the latest version of code that I was trying to use, any suggestions for making this work? I just want something I can load up in excel and create a report with:

with open(outfile_name, 'w') as out_file:
    with open('wlc-interference_raw.txt', 'r')as in_file:
        #Variables
        _ap_name = ''
        _temp = ''
        _flag = False
        for i in in_file:
            if 'AP Name' in i:
                #write whatever was put in the temp file to disk because new ap now
                #add another temp variable in case an ap has more than 1 interferer and check if new AP name
                out_file.write(_temp)
                out_file.write('\n')
                #print(_temp)
                _ap_name = i.lstrip('AP Name.......................................... ')
                _ap_name = _ap_name.rstrip('\n')
                _temp = _ap_name
                #print(_temp)
            elif '----' in i:
                pass
            elif 'Class Type' in i:
                pass
            else:
                line_split = i.split()
                for x in line_split:
                    _temp += ','
                    _temp += x
                _temp += '\n'

Can you run us through what isn't working in your current solution? Do you have an example expected output? — SCB
– SCB, Commented Dec 19, 2017 at 3:46
I could not get the AP name on the same line as the intereference devices. My goal is to have a nice sortable spreadsheet. I think that I may have overcomplicated it, but I am still a novice at this — Patrick
– Patrick, Commented Dec 19, 2017 at 15:34
Thanks for the input, I have never used classes in Python before so I am going to use this to learn how to use them. Thanks! — Patrick
– Patrick, Commented Dec 26, 2017 at 20:21

OneCricketeer · Accepted Answer · 2017-12-19 04:20:34Z

I think your best option is to read all lines of the file, then split into sections starting with AP Name. Then you can work on parsing each section.

Example

s = """AP Name.......................................... 010-HIGH-FL4-AP04
Microwave Oven      11       10      -59         Mon Dec 18 08:21:23 2017   
WiMax Mobile               11       0       -84         Fri Dec 15 17:09:45 2017   
WiMax Fixed                11       0       -68         Tue Dec 12 09:29:30 2017   
AP Name.......................................... 010-2nd-AP04
Microwave Oven             11       10      -61         Sat Dec 16 11:20:36 2017   
WiMax Fixed                11       0       -78         Mon Dec 11 12:33:10 2017   
AP Name.......................................... 139-FL1-AP03
Microwave Oven             6        18      -51         Fri Dec 15 12:26:56 2017   
AP Name.......................................... 010-HIGH-FL3-AP04
Microwave Oven             11       10      -55         Mon Dec 18 07:51:23 2017   
WiMax Mobile               11       0       -83         Wed Dec 13 16:16:26 2017"""

import re

class AP:
    """ 
    A class holding each section of the parsed file
    """
    def __init__(self):
        self.header = ""
        self.content = []

sections = []
section = None
for line in s.split('\n'):  # Or 'for line in file:'
    # Starting new section
    if line.startswith('AP Name'):
        # If previously had a section, add to list
        if section is not None:
            sections.append(section)  
        section = AP()
        section.header = line
    else:
        if section is not None:
            section.content.append(line)
sections.append(section)  # Add last section outside of loop


for section in sections:
    ap_name = section.header.lstrip("AP Name.")  # lstrip takes all the characters given, not a literal string
    for line in section.content:
        print(ap_name + ",", end="") 
        # You can extract the date separately, if needed
        # Splitting on more than one space using a regex
        line = ",".join(re.split(r'\s\s+', line))
        print(line.rstrip(','))  # Remove trailing comma from imperfect split

Output

010-HIGH-FL4-AP04,Microwave Oven,11,10,-59,Mon Dec 18 08:21:23 2017
010-HIGH-FL4-AP04,WiMax Mobile,11,0,-84,Fri Dec 15 17:09:45 2017
010-HIGH-FL4-AP04,WiMax Fixed,11,0,-68,Tue Dec 12 09:29:30 2017
010-2nd-AP04,Microwave Oven,11,10,-61,Sat Dec 16 11:20:36 2017
010-2nd-AP04,WiMax Fixed,11,0,-78,Mon Dec 11 12:33:10 2017
139-FL1-AP03,Microwave Oven,6,18,-51,Fri Dec 15 12:26:56 2017
010-HIGH-FL3-AP04,Microwave Oven,11,10,-55,Mon Dec 18 07:51:23 2017
010-HIGH-FL3-AP04,WiMax Mobile,11,0,-83,Wed Dec 13 16:16:26 2017

Tip:

You don't need Python to write the CSV, you can output to a file using the command line

python script.py > output.csv

Collectives™ on Stack Overflow

Python - Parsing a text file into a csv file

1 Answer 1

Example

Output

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Example

Output

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related