Question 135546 in Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – From several TXT create an excel book where each txt is a different sheet

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:PythonVisual Studio Code ProyectsM2M RealArchivos")

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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:PythonVisual Studio Code ProyectsM2M RealArchivos")

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

  2. 0

    move the line 

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

    outside the for loop for txt file.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search