how can i read a txt file and format part of the txt into a table using python

Question

i am trying to read a text file and extract a specific part from it to a CSV file ,

#**This is the part of the txt file:**#
- 172.16.1.202
- Hostaname: S01
#################################################################################
- VLAN-IP addressing Sheet
##########################
- VLAN + Description
--------------------
Interface                      Status         Protocol Description
Vl1                            up             up       
Vl5                            up             up       Legacy-RC-Admin
Vl20                           up             up       Legacy-RC-server
Vl30                           up             up       Legacy-RC-iSCSI
Vl40                           down           down     WAN Interconnect VLAN
Vl50                           up             up       
Vl60                           down           down     Tech FW ICS
Vl101                          up             up       RFR Data
Vl131                          down           down     Data WLAN
Vl134                          up             up       WLAN Management
Vl151                          down           down     Factory WLAN
Vl201                          up             up       RFR Management

- VLAN + IP address
--------------------
Interface              IP-Address      OK? Method Status                Protocol
Vlan1                  unassigned      YES unset  up                    up      
Vlan5                  10.26.95.33     YES NVRAM  up                    up      
Vlan20                 10.26.93.1      YES NVRAM  up                    up      
Vlan30                 10.26.93.65     YES NVRAM  up                    up      
Vlan40                 10.63.121.251   YES NVRAM  down                  down    
Vlan50                 10.50.50.54     YES NVRAM  up                    up      
Vlan60                 10.26.95.22     YES NVRAM  down                  down    
Vlan101                10.26.92.1      YES NVRAM  up                    up      
Vlan131                10.26.81.1      YES NVRAM  down                  down    
Vlan134                10.26.82.1      YES NVRAM  up                    up      
Vlan151                10.26.83.1      YES NVRAM  down                  down    
Vlan201                10.26.80.1      YES NVRAM  up                    up

- Subnet Mask
-------------
Internet address is 10.210.130.10/30
  Internet address is 172.16.1.202/24
  Internet address is 151.151.151.151/32
  Internet address is 10.26.95.33/27
  Internet address is 10.26.93.1/26
  Internet address is 10.26.93.65/26
  Internet address is 10.63.121.251/28
  Internet address is 10.50.50.54/24
  Internet address is 10.26.95.22/29
  Internet address is 10.26.92.1/24
  Internet address is 10.26.81.1/24
  Internet address is 10.26.82.1/24
  Internet address is 10.26.83.1/24
  Internet address is 10.26.80.1/24
##################################################################################
- LAN Sheet
############
- Access or Trunk with VLANs
----------------------------
interface Port-channel2
 switchport trunk allowed vlan 5,20,30,101,134,201,381
interface Port-channel1
interface GigabitEthernet0/2
 switchport trunk allowed vlan 5,20,30,101,134,201,381
interface GigabitEthernet0/3
interface GigabitEthernet0/0
interface GigabitEthernet0/1
 channel-group 1 mode on
interface GigabitEthernet1/0
 switchport trunk allowed vlan 5,20,30,101,134,201,381
 channel-group 2 mode on
interface GigabitEthernet1/1
 switchport trunk allowed vlan 5,20,30,101,134,201,381
 channel-group 2 mode on
interface GigabitEthernet1/2
interface GigabitEthernet1/3
ip route 172.16.1.203 255.255.255.255 GigabitEthernet0/1

- Non user interface with Description
-------------------------------------
 Interface                      Status         Protocol Description
Gi0/2                          up             up       Network link to eff-e-rfr-sw-as1
Gi0/3                          up             up       
Gi0/0                          up             up       
Gi0/1                          up             up       Network link to eff-e-rfr-sw-as1
Gi1/0                          up             up       Network link to eff-e-rfr-sw-as2
Gi1/1                          up             up       Network link to eff-e-rfr-sw-as2
Gi1/2                          up             up       
Gi1/3                          up             up       
Po2                            down           down     Network link to eff-e-rfr-sw-as2
Po1                            up             up       Network link to eff-e-rfr-sw-as1
Lo50                           up             up

- Remote port
---------------
Interface: GigabitEthernet0/0,  Port ID (outgoing port): Ethernet0/0
Interface: GigabitEthernet0/0,  Port ID (outgoing port): GigabitEthernet0/0
Interface: GigabitEthernet0/2,  Port ID (outgoing port): GigabitEthernet0/2
Interface: GigabitEthernet0/0,  Port ID (outgoing port): GigabitEthernet0/0
Interface: GigabitEthernet0/0,  Port ID (outgoing port): GigabitEthernet0/0
Interface: GigabitEthernet0/1,  Port ID (outgoing port): GigabitEthernet0/1
Interface: GigabitEthernet0/0,  Port ID (outgoing port): GigabitEthernet0/0
Interface: GigabitEthernet0/0,  Port ID (outgoing port): GigabitEthernet0/0
Interface: GigabitEthernet1/1,  Port ID (outgoing port): GigabitEthernet1/1
Interface: GigabitEthernet1/0,  Port ID (outgoing port): GigabitEthernet1/0
Interface: GigabitEthernet0/0,  Port ID (outgoing port): GigabitEthernet0/1
Interface: GigabitEthernet0/0,  Port ID (outgoing port): GigabitEthernet0/0
Interface: GigabitEthernet0/0,  Port ID (outgoing port): mgmt0
Interface: GigabitEthernet0/0,  Port ID (outgoing port): GigabitEthernet0/0/0/0

The expected output is : the expected output

RJ Adriaansen · Answer 1 · 2021-05-06T21:35:47.507

1

You can use pandas.read_csv() with:

skiprows=8 to skip the first 8 rows
nrows=12 to read 12 rows
sep='\s{2,}' to use multiple whitespaces as separator
names=['Interface','Status','Protocol','Description'] to set the column names manually, as the single space between the last two column names interferes with the multiple-space delimiter that is needed because the last row contains spaces in the cells.

The same for the next part, with adapted row values. Here we can delim_whitespace=True to use all whitespaces as delimiters, as there are no columns with whitespaces in the values. pandas can now also infer the column names automatically as there are no values with spaces in this table.

Finally, join both dataframes:

import pandas as pd

df1 = pd.read_csv('filename.txt', skiprows=8, nrows=12, sep='\s{2,}', names=['Interface','Status','Protocol','Description'])
df2 = pd.read_csv('filename.txt', skiprows=23, nrows=12, delim_whitespace=True)
final_df = df1.join(df2, lsuffix='_decription', rsuffix='_ipaddress')

Result:

	Interface_decription	Status_decription	Protocol_decription	Description	Interface_ipaddress	IP-Address	OK?	Method	Status_ipaddress	Protocol_ipaddress
0	Vl1	up	up		Vlan1	unassigned	YES	unset	up	up
1	Vl5	up	up	Legacy-RC-Admin	Vlan5	10.26.95.33	YES	NVRAM	up	up
2	Vl20	up	up	Legacy-RC-server	Vlan20	10.26.93.1	YES	NVRAM	up	up
3	Vl30	up	up	Legacy-RC-iSCSI	Vlan30	10.26.93.65	YES	NVRAM	up	up
4	Vl40	down	down	WAN Interconnect VLAN	Vlan40	10.63.121.251	YES	NVRAM	down	down
5	Vl50	up	up		Vlan50	10.50.50.54	YES	NVRAM	up	up
6	Vl60	down	down	Tech FW ICS	Vlan60	10.26.95.22	YES	NVRAM	down	down
7	Vl101	up	up	RFR Data	Vlan101	10.26.92.1	YES	NVRAM	up	up
8	Vl131	down	down	Data WLAN	Vlan131	10.26.81.1	YES	NVRAM	down	down
9	Vl134	up	up	WLAN Management	Vlan134	10.26.82.1	YES	NVRAM	up	up
10	Vl151	down	down	Factory WLAN	Vlan151	10.26.83.1	YES	NVRAM	down	down
11	Vl201	up	up	RFR Management	Vlan201	10.26.80.1	YES	NVRAM	up	up

You can save the dataframe to a new csv file: final_df.to_csv('filename.csv')

edited May 06 '21 at 21:35

answered May 06 '21 at 19:50

RJ Adriaansen

3,782
2
5
18

its a txt file not a csv file should i save it in a csv file ? – Ibrahim mostafa May 06 '21 at 20:03
Not necessary, just use the txt file in `read_csv`. – RJ Adriaansen May 06 '21 at 20:05
ok las question , if i have other raw data in the txt file ,which i need for example to skip 3 row then i will need to add this txt to the same table but different columns and other will be added to the same column for ex this text is found after 3 rows of unnecessary data : Internet address is 10.210.130.10/30 Internet address is 172.16.1.202/24 Internet address is 151.151.151.151/32 Internet address is 10.26.95.33/27 Internet address is 10.26.93.1/26 Internet address is 10.26.93.65/26 Internet address is 10.63.121.251/28 Internet address is 10.50.50.54/24 – Ibrahim mostafa May 06 '21 at 20:14
I don't really get what you're saying. It is difficult to visualize without seeing what the file looks like. Could you update your question with what you want to know, or post a new question if this is a different issue? – RJ Adriaansen May 06 '21 at 20:21
i have edited the txt file in the main question , i want for example to add another column for [Interface I,P-Address ,Method ,Status) aftet skiping three rows from the file and also will skip description column and ok column – Ibrahim mostafa May 06 '21 at 21:12
Thanks, I have updated the answer. You can play with the row values for any other part that you would like to extract. – RJ Adriaansen May 06 '21 at 21:40
how can i do the same process but not with skipping rows as i want to apply the code with diffrent txt files? – Ibrahim mostafa May 09 '21 at 21:47
You'll have to tweak the setting if other files are formatted differently. It all depends on how the specific files are formatted. – RJ Adriaansen May 09 '21 at 23:20
i mean the files will differ for example that the n0. of interfaces will be increased , so by using the row method the code will be shifted, is there a way to grap a specific elements by its name or another way not by counting rows? – Ibrahim mostafa May 09 '21 at 23:22
If you know how the filenames correlate with the number of interfaces you could write a custom function that populates the `nrows` value dynamically. If you need help with that, please open a new question because it is a different topic. – RJ Adriaansen May 10 '21 at 06:48

score 0 · Answer 2 · answered May 06 '21 at 19:37

0

Go to this link Turning a text file into a tabular format I think you are looking for the same answer thanks which are already explained there.

answered May 06 '21 at 19:37

Waqar Kaleem Khan

21
3

but i have a problem that the txt file contain some data i don't need it like the first 7 lines & the needed part of the txt file looks like that with this spacing : Interface Status Protocol Description Vl1 up up Vl5 up up Legacy-RC-Admin Vl20 up up Legacy-RC-server Vl30 up up Legacy-RC-iSCSI Vl40 down down WAN Interconnect VLAN – Ibrahim mostafa May 06 '21 at 19:50

how can i read a txt file and format part of the txt into a table using python

2 Answers2