Home > Software engineering >  How do I convert multiple multiline txt files to excel - ensuring each file is its own line, then ea
How do I convert multiple multiline txt files to excel - ensuring each file is its own line, then ea

Time:04-28

Using openpyxl and Path I aim to: Create multiple multiline .txt files, then insert .txt content into a .xlsx file ensuring file 1 is in column 1 and each line has its own row.

I thought to create a nested list then loop through it to insert the text. I cannot figure how to ensure that all the nested list string is displayed. This is what I have so far which nearly does what I want however it's just a repeat of the first line of text.

from pathlib import Path
import openpyxl
listOfText = []

wb = openpyxl.Workbook() # Create a new workbook to insert the text files
sheet = wb.active

for txtFile in range(5): # create 5 text files
    createTextFile = Path('textFile'   str(txtFile)   '.txt')
    createTextFile.write_text(f'''Hello, this is a multiple line text file.
My Name is x.
This is text file {txtFile}.''')

    readTxtFile = open(createTextFile)
    listOfText.append(readTxtFile.readlines()) # nest the list from each text file into a parent list

textFileList = len(listOfText[txtFile]) # get the number of lines of text from the file. They are all 3 as made above

# Each column displays text from each text file
for row in range(1, txtFile   1):
    for col in range(1, textFileList   1):
        sheet.cell(row=row, column=col).value = listOfText[txtFile][0]

wb.save('importedTextFiles.xlsx')

The output is 4 columns/4 rows. All of which say the same 'Hello, this is a multiple line text file.'

Appreciate any help with this!

CodePudding user response:

The problem is in the for loop while writing, change the line sheet.cell(row=row, column=col).value = listOfText[txtFile][0] to sheet.cell(row=col, column=row).value = listOfText[row-1][col-1] and it will work

  • Related