How do I write data into an Excel file automatically using Python?

Question

I have to put some numerical data, which I received in text file into an Excel file (no, Pandas won't work, because of the way that data is written in the file) and for some reason, it just won't work it just won't work. My latest attempt to get it done is below. I also tried with openpyxl with no success.

wb = w.Workbook('D:\\IJS\\JEFF444.xlsx')
ws = wb.add_worksheet()


with open(trimmed_data, 'r') as file:
    lines = file.readlines()
    row = 8
    for index in range(len(lines)):
        if len(lines[index].split()) != 0:
            lst = lines[index].split()
            print(lst)
            if lst[-1][-3:] == '444':
                print(lst[3].split('/')[0])
                ws.write(f'B{row}', lst[3].split('/')[0])
                ws.write(f'E{row}', float(lines[index + 2].split()[1]))
                ws.write(f'F{row}', float(lines[index + 2].split()[1]) * float(lines[index + 2].split()[2]))
                ws.write(f'J{row}', float(lines[index + 5].split()[1]))
                ws.write(f'K{row}', float(lines[index + 5].split()[1]) * float(lines[index + 5].split()[2]))
                ws.write(f'O{row}', float(lines[index + 8].split()[1]))
                ws.write(f'P{row}', float(lines[index + 8].split()[1]) * float(lines[index + 8].split()[2]))
                ws.write(f'T{row}', float(lines[index + 11].split()[1]))
                ws.write(f'U{row}', float(lines[index + 11].split()[1]) * float(lines[index + 11].split()[2]))
                row += 1
    file.close()

wb.close()

Hey, what data are we working with ? When you say Pandas won't work what does it say? df.to_excel Given you have a data frame called df simply do df.to_excel() — Andrew
– Andrew, Commented Aug 29, 2022 at 15:31
"It won't work" is not something we can help. Have you tried making your code simpler, eliminating the conditionals and just writing some static text in specific cells to see if it works then? once you have some basic code working add more code and if it doesn't work explain what is not working (expected result vs actual result). Your current code is not executable since there are variables not defined. — user2261062
– user2261062, Commented Aug 29, 2022 at 15:31

Pilonyen · Accepted Answer · 2022-08-29 15:35:06Z

1

Excel can read .csv Files wich you can create with the integrated Library csv from Python. If you don't have to use .xlsx maybe this could be the way to go. Here you can learn how to use it: https://realpython.com/python-csv/

There are tons of Tutorials out there on YouTube if you still need help with csv.

Hopefully this can help you :)

answered Aug 29, 2022 at 15:35

Pilonyen

337 bronze badges

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

Collectives™ on Stack Overflow

How do I write data into an Excel file automatically using Python?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related