Input data =
"Port Native VLAN Trunk VLANs
\r\n-------------------------------------------------------------------------\r\n1/1/2 1 1-2,77,90,802,998-999\r\n1/1/4
2 2,10,12-15,25,30,44,555,802\r\n1/1/5 None 10,555\r\n1/1/6 2 2,10,12-15,25-26,30,44,77,80,90,150,190,260,555,\r\n
767,802,997-999,1379\r\n1/1/7 None 10,555\r\n1/1/8 2 2,10,12-15,25,30,44,555,802\r\n1/1/9 2 2,10,12-15,25-26,30,44,77,80,90,150,190,260,555,\r\n 767,802,997-999,1379\r\n"
I have input data that look like this and want to convert this to a Python dictionary with keys as headers of table and values as the column data
Expected Output:
[{'Port': '1/1/2', 'Native VLAN': '1', 'Trunk VLANs': '1-2,77,90,802,998-999'}, {'Port': '1/1/4', 'Native VLAN': '2', 'Trunk VLANs': '2,10,12-15,25,30,44,555,802'}, {'Port': '1/1/5', 'Native VLAN': 'None', 'Trunk VLANs': '10,555'}, {'Port': '1/1/6', 'Native VLAN': '2', 'Trunk VLANs': '2,10,12-15,25-26,30,44,77,80,90,150,190,260,555,767,802,997-999,1379'}]
Since the rows are fixed length, we can simply slice them to get our dictionary data. If the final value ends with a comma it signals to us that the next row has additional data for this one..
result = []
with open("in.txt", "r") as file_in:
## -------------------
## skip pas the header
## -------------------
rows = iter([row.strip() for row in file_in.readlines()[2:]])
## -------------------
## -------------------
## read the rows of actual data
## -------------------
for row in rows:
port = row[0:11].strip()
native_vlan = row[11:23].strip()
## -------------------
## make sure the trunk_vlans includes all data
## -------------------
trunk_vlans = row[23:].strip()
if trunk_vlans.endswith(","):
trunk_vlans += next(rows).strip()
## -------------------
result.append({
"Port": port,
"Native VLAN": native_vlan,
"Trunk VLANs": trunk_vlans,
})
import json
print(json.dumps(result, indent=4))
With an "in.txt" of:
Port Native VLAN Trunk VLANs
-------------------------------------------------------------------------
1/1/2 1 1-2,77,90,802,998-999
1/1/4 2 2,10,12-15,25,30,44,555,802
1/1/5 None 10,555
1/1/6 2 2,10,12-15,25-26,30,44,77,80,90,150,190,260,555,
767,802,997-999,1379
1/1/7 None 10,555
1/1/8 2 2,10,12-15,25,30,44,555,802
1/1/9 2 2,10,12-15,25-26,30,44,77,80,90,150,190,260,555,
767,802,997-999,1379
Out output would be:
[
{
"Port": "1/1/2",
"Native VLAN": "1",
"Trunk VLANs": "1-2,77,90,802,998-999"
},
{
"Port": "1/1/4",
"Native VLAN": "2",
"Trunk VLANs": "2,10,12-15,25,30,44,555,802"
},
{
"Port": "1/1/5",
"Native VLAN": "None",
"Trunk VLANs": "10,555"
},
{
"Port": "1/1/6",
"Native VLAN": "2",
"Trunk VLANs": "2,10,12-15,25-26,30,44,77,80,90,150,190,260,555,767,802,997-999,1379"
},
{
"Port": "1/1/7",
"Native VLAN": "None",
"Trunk VLANs": "10,555"
},
{
"Port": "1/1/8",
"Native VLAN": "2",
"Trunk VLANs": "2,10,12-15,25,30,44,555,802"
},
{
"Port": "1/1/9",
"Native VLAN": "2",
"Trunk VLANs": "2,10,12-15,25-26,30,44,77,80,90,150,190,260,555,767,802,997-999,1379"
}
]