Print output to Excel

Question

I am having trouble formatting output from the following code:

import sys
import time
import select
import paramiko
import openpyxl

ip='address'
port='port'
username='uname'
password='pass'

cmd="wstalist | grep -w 'mac\|hostname\|tx\|rx\|lastip\|platform\|distance\|signal'"

ssh=paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(ip,port,username,password)

stdin,stdout,stderr=ssh.exec_command(cmd)
outlines=stdout.readlines()
resp=''.join(outlines)
print(resp)

Currently the output is exactly as follows:

"mac": "04:18:D6:96:7C:51",
    "lastip": "10.1.63.143",
    "tx": 130.0,
    "rx": 39.0,
    "signal": -46,
    "distance": 450,
            "signal": -44,
            "hostname": "Mcdonalds kenako",
            "platform": "PowerBeam M5 400",
            "signal": -44,
    "mac": "80:2A:A8:0E:A3:1E",
    "lastip": "10.1.63.190",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -46,
    "distance": 450,
            "signal": -42,
            "hostname": "Lear sewing PE",
            "platform": "PowerBeam M5 400",
            "signal": -42,
    "mac": "80:2A:A8:7C:9A:C2",
    "lastip": "10.1.63.189",
    "tx": 130.0,
    "rx": 117.0,
    "signal": -62,
    "distance": 4950,
            "signal": -60,
            "hostname": "Sandoll Consultant",
            "platform": "PowerBeam M5 400",
            "signal": -58,
    "mac": "44:D9:E7:56:E6:CB",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -63,
    "distance": 4200,
            "signal": -58,
            "hostname": "Louise Swart",
            "platform": "PowerBeam M5 400",
            "signal": -57,
    "mac": "04:18:D6:9A:5A:86",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -58,
    "distance": 3900,
            "signal": -62,
    "mac": "44:D9:E7:6A:26:B7",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -64,
    "distance": 6750,
            "signal": -62,
            "hostname": "Larry Hunt",
            "platform": "PowerBeam M5 400",
            "signal": -62,
    "mac": "04:18:D6:98:CA:7F",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -59,
    "distance": 6000,
            "signal": -60,
            "hostname": "Louis Nel",
            "platform": "PowerBeam M5 400",
            "signal": -59,
    "mac": "80:2A:A8:7C:8D:EF",
    "lastip": "0.0.0.0",
    "tx": 117.0,
    "rx": 130.0,
    "signal": -63,
    "distance": 6900,
            "signal": -60,
            "hostname": "Florence Naidoo",
            "platform": "PowerBeam M5 400",
            "signal": -59,
    "mac": "80:2A:A8:74:C0:AB",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -47,
    "distance": 300,
            "signal": -47,
            "hostname": "ETC",
            "platform": "PowerBeam M5 400",
            "signal": -43,
    "mac": "80:2A:A8:08:CB:20",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 104.0,
    "signal": -66,
    "distance": 6750,
            "signal": -63,
            "hostname": "Nicholis Lourens",
            "platform": "PowerBeam M5 400",
            "signal": -60,
    "mac": "00:27:22:56:EC:35",
    "lastip": "10.1.63.141",
    "tx": 65.0,
    "rx": 65.0,
    "signal": -62,
    "distance": 5400,
            "signal": -62,
            "hostname": "Africape",
            "platform": "AirGrid M5 HP",
            "signal": -60,
    "mac": "44:D9:E7:2A:43:00",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -59,
    "distance": 3000,
            "signal": -56,
            "hostname": "Ruth Pedzisai",
            "platform": "PowerBeam M5 400",
            "signal": -55,
    "mac": "44:D9:E7:DA:51:28",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -63,
    "distance": 3450,
            "signal": -60,
            "hostname": "Thandeka Tonjeni",
            "platform": "PowerBeam M5 400",
            "signal": -60,
    "mac": "24:A4:3C:90:2F:9F",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -62,
    "distance": 5400,
            "signal": -61,
            "hostname": "WK Construction",
            "platform": "PowerBeam M5 400",
            "signal": -59,
    "mac": "04:18:D6:38:65:01",
    "lastip": "0.0.0.0",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -47,
    "distance": 2100,
            "signal": -48,
            "hostname": "OLDBRONCO",
            "platform": "PowerBeam M5 400",
            "signal": -48,

The indentation you see on the output is as exactly how it is displayed on the out of the command.I thought to mention it in case it would be a factor to the follow:

1) The above is a snippet of a single client's info...I need guidance on removing the second and third instances of the "signal" entry

2) If it will help, I would like the output be represented as follows or (what I really want to actually do) export the information to an Excel spreadsheet:

...Desired Output Format:

Station MAC         Device Name     IP Address      Signal      Distance     Tx/Rx
00:27:22:54:CB:5E   Deon Nelson     192.168.10.1    -66         1950"         144.0/144.0

FYI...This command is run on Ubiquity Wireless devices

I thank you in advance!!!

score 1 · Answer 1 · answered Feb 10 '17 at 11:35

1

I use XlsxWriter lib to create Excel files:

Take a look on the documentation in the Contents Tap of the web page..

answered Feb 10 '17 at 11:35

Ika8

391
1
12

At this point I am looking for a way to grab the individual fields so I can manipulate them to the "desired output" format – tsietsi rampa Feb 10 '17 at 12:06
That will also help me map them into the spreadsheet, don't you think...? – tsietsi rampa Feb 10 '17 at 12:07
Very help indeed, but the only drawback to this method is that it only displays one client's information... Am I missing something here – tsietsi rampa Feb 10 '17 at 14:26

Sangbok Lee · Accepted Answer · 2017-02-16T08:14:43.233

0

Some formatting and writing a CSV file would do enough. I modified it to process multiple input now... I chose "mac" for the seperator key.

input_str = """\
"mac": "04:18:D6:96:7C:51",
    "lastip": "10.1.63.143",
    "tx": 130.0,
    "rx": 39.0,
    "signal": -46,
    "distance": 450,
            "signal": -44,
            "hostname": "Mcdonalds kenako",
            "platform": "PowerBeam M5 400",
            "signal": -44,
    "mac": "80:2A:A8:0E:A3:1E",
    "lastip": "10.1.63.190",
    "tx": 130.0,
    "rx": 130.0,
    "signal": -46,
    "distance": 450,
            "signal": -42,
            "hostname": "Lear sewing PE",
            "platform": "PowerBeam M5 400",
            "signal": -42,
    "mac": "80:2A:A8:7C:9A:C2",
    "lastip": "10.1.63.189",
    "tx": 130.0,
    "rx": 117.0,
    "signal": -62,
    "distance": 4950,
            "signal": -60,
            "hostname": "Sandoll Consultant",
            "platform": "PowerBeam M5 400",
            "signal": -58,"""

input_list = [x.strip() for x in input_str.split(",\n")]  # split and strip indentations
input_list = input_list[::-1]  # reverse list to ignore duplicating keys later

# http://stackoverflow.com/questions/988228/convert-a-string-representation-of-a-dictionary-to-a-dictionary
import ast

input_list_of_dict = ["{" + x + "}" for x in input_list]
parsed_list_of_dict = []
list_seperator_key = "mac"
input_dict = {}
for i in range(len(input_list_of_dict)):
    input_dict.update(ast.literal_eval(input_list_of_dict[i]))
    if list_seperator_key in input_dict:
        parsed_list_of_dict.append(input_dict)
        input_dict = {}

parsed_list_of_dict = parsed_list_of_dict[::-1]  # reverse list again


# writes a CSV file
header = "Station MAC,Device Name,IP Address,Signal,Distance,Tx/Rx\n"

with open("foo.csv", "w") as f:
    f.write(header)

    for i in range(len(parsed_list_of_dict)):
        f.write(parsed_list_of_dict[i]['mac'] + ",")
        f.write(parsed_list_of_dict[i]['hostname'] + ",")
        f.write(parsed_list_of_dict[i]['lastip'] + ",")
        f.write(str(parsed_list_of_dict[i]['signal']) + ",")
        f.write(str(parsed_list_of_dict[i]['distance']) + ",")
        f.write(str(parsed_list_of_dict[i]['tx']) + "/" + str(parsed_list_of_dict[i]['rx']) + "\n")

Now open foo.csv with Excel.

edited Feb 16 '17 at 08:14

answered Feb 10 '17 at 12:58

Sangbok Lee

2,132
3
15
33

Very help indeed, but the only drawback to this method is that it only displays one client's information... Am I missing something here – tsietsi rampa Feb 10 '17 at 14:27
Yes this code is based on the input you provided and I thought the input was just one client's information. You can extend this code to read multiple clients' information or if you provide more generalized input, I can help you too. – Sangbok Lee Feb 10 '17 at 14:31
I was afraid that would have made the question lengthy. I understand I should put this code in a for loop don't know how I would break(or separate) the information of each client to create a new line – tsietsi rampa Feb 10 '17 at 16:09
here is the output – tsietsi rampa Feb 10 '17 at 16:10
Output is too long to add as comment."" – tsietsi rampa Feb 10 '17 at 16:13
Okay, you can edit the question or post a new questioin. And if my answer was helpful please accept it. – Sangbok Lee Feb 10 '17 at 16:20
I just edited the question to include the full output. Please have a look and advise – tsietsi rampa Feb 15 '17 at 14:40
The CSV seems to be displaying duplicates of just one client. I am suspecting this line :"parsed_list_of_dict = parsed_list_of_dict[::-1] " to be ignoring all other client's information based on the duplicating dictionary keys...Any suggestions? – tsietsi rampa Feb 16 '17 at 08:52
I tested it and it's fine(version is 3.5.2). Use Python console and check the contents of `parsed_list_of_dict` and tell me which version you use. – Sangbok Lee Feb 16 '17 at 09:04
I am using python 2.7 and the version of the interpreter is 2.7.13 Would that make any difference? – tsietsi rampa Feb 16 '17 at 09:17
Just to add, I did check on the console and it yeilds the same result as the later created CSV file – tsietsi rampa Feb 16 '17 at 10:17
Turns out I made changes to the code when I was trying things out... @Sangobok_Lee you need not worry...All is sorted...I shall accept answer as helpful – tsietsi rampa Feb 16 '17 at 10:30

Print output to Excel

2 Answers2