How to append data to excel into single excel sheet using for loop

Question

I am trying to read log files from different folders and store then in an Excel sheet.

import os
import pandas as pd
import bs4

path = "D:\\logfolder"
filelist = []

for root, dirs, files in os.walk(path):
    for file in files:
        if file.endswith("History.txt"):
            filelist.append(os.path.join(root, file))

for name in filelist:             
        print(name + "\n")   # D:\logfolder\logfolder_1\History.txt
                             # D:\logfolder\logfolder_2\History.txt
                             # D:\logfolder\logfolder_3\History.txt
                             # D:\logfolder\logfolder_4\History.txt

for name in filelist:
    with open(name,"r") as f:
        soupObj = bs4.BeautifulSoup(f, "lxml")

    df = pd.DataFrame([(x["uri"], *x["t"].split("T"), x["u"], x["desc"])
                       for x in soupObj.find_all("log")],
                      columns=["Database", "Date", "Time", "User", "Description"])
    df.to_excel("logfile.xlsx", index=False)

The expected output is to get a final Excel sheet of all logfile.txt from all log-folders but I am only getting the data from the last logfile.txt with its logfolder_4 in the final Excel sheet with its logfile.xlsx.

What I am doing wrong ?

Timeless · Accepted Answer · 2023-01-23 21:47:35Z

1

I believe you need pandas.concat.

Replace the last for-loop bloc by this :

dfList = []
for name in filelist:
    with open(name,"r") as f:
        soupObj = bs4.BeautifulSoup(f, "lxml")

    df = pd.DataFrame([(x["uri"], *x["t"].split("T"), x["u"], x["desc"])
                       for x in soupObj.find_all("log")],
                      columns=["Database", "Date", "Time", "User", "Description"])
    
    dfList.append(df)

pd.concat(dfList).to_excel("logfile.xlsx", index=False)

answered Jan 23, 2023 at 21:47

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to append data to excel into single excel sheet using for loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related