Parsing table data extending to multiple columns

Question

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

How the table looks like

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'}]

The rows appear to be fixed length in your image. Is that the case? It makes the problem much easier to solve if it is. — JonSG, Feb 16 '23 at 17:44
yes the rows are of fixed length, that's why when the data is exceeding the row's length it's going to next line. — Sandy, Feb 16 '23 at 17:52

score 0 · Accepted Answer · answered Feb 16 '23 at 18:16

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"
    }
]

Parsing table data extending to multiple columns

1 Answers1