CODEWITHSUNDEEP
pythonopenpyxl
rose
rose

Reputation: 137

content from multiple txt files into single excel file with separate sheets using python

this question is similar to content from multiple txt files into single excel file using python.

Can i have those data in separate sheets instead of one sheet ?

If I have for example 3 txt files that looks as follows:
file1.txt:

a 10
b 20
c 30

file2.txt:

d 40
e 50
f 60

file3.txt:

g 70
h 80
i 90

i would like to have file1 as sheet1, file2 as sheet 2 etc in one single workbook. using openpyxl will be much helpful.

Upvotes: 0

Views: 536

Answers (1)

Edo Akse
Edo Akse

Reputation: 4401

The logic is slightly different than my other answer, but the basics are the same. Biggest change is loading the files inside a loop, and naming the sheets...

requirements.txt

pandas
openpyxl

code

# we use pandas for easy saving as XSLX
import pandas as pd


filelist = ["file01.txt", "file02.txt", "file03.txt"]


def load_file(filename: str) -> list:
    result = []
    with open(filename) as infile:
        # the split below is OS agnostic and removes EOL characters
        for line in infile.read().splitlines():
            # the split below splits on space character by default
            result.append(line.split())
    return result


with pd.ExcelWriter("output.xlsx") as writer:
    sheet_names = ["sheet1", "sheet2", "sheet3"]
    for idx, filename in enumerate(filelist):
        df = pd.DataFrame(load_file(filename))
        df.to_excel(writer, sheet_name=sheet_names[idx], index=False, header=False)

Upvotes: 0

Related Questions