Creating an excel table from repeated txt (extracted from router)

Python Help
1

Good day community,

The file i have is an output from a router, where the printed format is repeated for every tunnel. The tunnel output looks like this, and then repeats for every tunnel found. The trouble is i want to take the repeated “key” names, ie, connection, index, ip addr, etc…, and make them columns, and for every corresponding value next to it, write the cell value. Is this something i can use with pandas, or is best approach to search “key” in line and write corresponding value?

Here is a small extract.

Connection : CONNEC-VPN
Index : 63384 IP Addr : 196.x.x.x
Encryption : IKEv2: (1)AES256 IPsec: (7)AES256
Hashing : IKEv2: (1)SHA256 IPsec: (7)SHA256
Connection : PROD-VPN
Index : 68963 IP Addr : 196.5.x.x
Encryption : IKEv2: (1)AES256 IPsec: (15)AES256
Hashing : IKEv2: (1)SHA256 IPsec: (15)SHA256
Connection : COMP-VPN
Index : 73644 IP Addr : 197.x.x.x
Encryption : IKEv1: (1)AES256 IPsec: (4)AES256
Hashing : IKEv1: (1)SHA1 IPsec: (4)SHA1

Thank you,

I have simply tried to see if excel can pivot the file, but it complains about duplicate values. And in previous attempts on older projects with a similar file, i couldn’t get it right with panda csv writer.

2

Essentially, what you need to build is a text parser, but you’ll need to establish some rules.

If we start and pull your log file apart, we get:

Connection : CONNEC-VPN
Index : 63384 IP Addr : 196.x.x.x
Encryption : IKEv2: (1)AES256 IPsec: (7)AES256
Hashing : IKEv2: (1)SHA256 IPsec: (7)SHA256

Connection : PROD-VPN
Index : 68963 IP Addr : 196.5.x.x
Encryption : IKEv2: (1)AES256 IPsec: (15)AES256
Hashing : IKEv2: (1)SHA256 IPsec: (15)SHA256

Connection : COMP-VPN
Index : 73644 IP Addr : 197.x.x.x
Encryption : IKEv1: (1)AES256 IPsec: (4)AES256
Hashing : IKEv1: (1)SHA1 IPsec: (4)SHA1

… which can be called ‘records’. The fields are a little less well defined and you say that you’d like them in columns, so the first step is to decide what the column headers will be and mock-up a table, so that you can see what your goal is.

The data can be separated at the :, for the most part; the exception being line two, as there’s no colon after the index number.