From several TXT create an excel book where each txt is a different sheet-CodePudding

I am trying to read several TXT files, I make some modifications to them and then I convert them into a DataFrame with Pandas and there I also run some modification processes, so far everything is fine, everything works perfect, I do it through a for loop but at the moment of save the xlsx file and create the first sheet and it doesn't create new sheets, it just creates the first one.

the code is the following:

from netmiko import ConnectHandler
from datetime import datetime
import re
from pathlib import Path
import os
import pandas as pd

ruta = Path("E:\Python\Visual Studio Code Proyects\M2M Real\Archivos")

def is_free(valor):
    color = 'green' if valor == "free"  else 'white'
    return 'background-color: %s' % color

list_txt = [ruta/"Router_a.txt", ruta/"Router_b.txt"]

for txt in list_txt:

  host = txt.stem

  sheet_name=f'{host}-Gi0-3-4-2'

  ruta_host = f'{ruta}\\interfaces.xlsx'

  df = pd.read_fwf(txt)

  df["Description"] = (df.iloc[:, 3:].fillna("").astype(str).apply(" ".join, axis=1).str.strip())

  df = df.iloc[:, :4]

  df = df.drop(columns = ["Status", "Protocol"])

  df.Interface = df.Interface.str.extract('Gi0/3/4/2\.(\d )')

  df = df[df.Interface.notnull()].reset_index()

  df = df.drop(columns = ["index"])

  df['Interface'] = df['Interface'].astype(int)

  df = df.set_index('Interface').reindex(range(1,50)).fillna('free').reset_index()

  df = df.style.applymap(is_free)

  with pd.ExcelWriter(ruta_host, mode='a') as writer:

    df.to_excel(writer, sheet_name, index=False)

the format of the txt is as follows, it is worth clarifying that basically both txt from both routers are almost identical:

Interface          Status      Protocol    Description
Gi0/3/4/2          up          up          ENLACE A Router_X
Gi0/3/4/2.5        up          up          Frontera Cliente A
Gi0/3/4/2.6        up          up          Frontera Cliente B
Gi0/3/4/2.7        up          up          Frontera Cliente C
Gi0/3/4/2.8        up          up          Frontera Cliente D
Gi0/3/4/2.9        up          up          Frontera Cliente E

Any idea what I'm doing wrong?

CodePudding user response：

I was able to solve my problem and here is the script:

from netmiko import ConnectHandler
from datetime import datetime
import re
from pathlib import Path
import os
import pandas as pd
from openpyxl import load_workbook

ruta = Path("E:\Python\Visual Studio Code Proyects\M2M Real\Archivos")

def is_free(valor):
    color = 'green' if valor == "free"  else 'white'
    return 'background-color: %s' % color

list_txt = [ruta/"FRUVI01.txt", ruta/"FRUVE01.txt"]

for txt in list_txt:

  with open(txt, "r") as f:

    lines = f.readlines()

  with open(txt, "w") as fw:
    for line in lines:

      if not re.match("-{5}|\s |([A-Za-z0-9] ( [A-Za-z0-9] ) )", line):
        fw.write(line)

for txt in list_txt:

  host = txt.stem

  sheet_name=f'{host}-Gi0-3-4-2'

  ruta_host = ruta/'ejemplo.xlsx'

  df = pd.read_fwf(txt)

  df["Description"] = (df.iloc[:, 3:].fillna("").astype(str).apply(" ".join, axis=1).str.strip())

  df = df.iloc[:, :4]

  df = df.drop(columns = ["Status", "Protocol"])

  df.Interface = df.Interface.str.extract('Gi0/3/4/2\.(\d )')

  df = df[df.Interface.notnull()].reset_index()

  df = df.drop(columns = ["index"])

  df['Interface'] = df['Interface'].astype(int)

  df = df.set_index('Interface').reindex(range(1,3580)).fillna('free').reset_index()

  df = df.style.applymap(is_free)

  if not ruta_host.exists():
    writer = pd.ExcelWriter(ruta_host)
    writer.close()            
  else:
    pass  

  with pd.ExcelWriter(ruta_host, mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:

    df = df.to_excel(writer, sheet_name, index=False)

Because I had to create a create a file and it was generated with a sheet with the name "Sheet1", add the following script to delete it at the end and outside the loop:

wb = load_workbook(ruta_host)
if 'Sheet1' in wb.sheetnames:
  wb.remove(wb['Sheet1'])
  wb.save(ruta_host)
else:
  pass

CodePudding user response：

move the line

with pd.ExcelWriter(ruta_host, mode='a') as writer

outside the for loop for txt file.