save .txt data in excel worksheet

Question

I want to extract data from a .txt file and store it in an excel worksheet. It seems to work but the script does not save all of the data. Here is my code:

def save_excel(filename, date, item, price, counter):
    
    workbook = xlsxwriter.Workbook('Ausgabenliste.xlsx')
    worksheet = workbook.add_worksheet(filename)

    #write header
    worksheet.write(0, 0, "Datum")     
    worksheet.write(0, 1, "Einkauf")     
    worksheet.write(0, 2, "Preis")  

    worksheet.write(counter, 0, date)    
    worksheet.write(counter, 1, item)  
    worksheet.write(counter, 2, price) 

    workbook.close()   

with open (filename) as f:
    lines = f.readlines()
    print(lines)
    counter = 1
    
    for line in lines:
        l=line.split()
        a = l [-3]
        b = l [-2]
        c = l [-1]
        """print(l)
        print(a)
        print(b)
        print(c)
        print(counter)"""
        save_excel(filename,a, b, c, counter)
        counter += 1

The .txt file contains this information:

02.09.2020 Pizza 1,50
02.09.2020 Pizza 1,50
02.09.2020 Nussschnecke 2,05
02.09.2020 jlkjlsdf 546

and the output looks like this:

enter image description here

This is a total noob question. I just don´t understand why the information inbetween is lost.

Thank you very much.

Each time you call the `save_excel()` function you are writing a new file, so the output is just the last call of the function. — 7koFnMiP, Sep 03 '20 at 07:20

Sebastien D · Accepted Answer · 2020-09-03T08:16:36.830

2

This could be achieved very easily with pandas:

import pandas as pd
df = pd.read_csv(filename, sep=" ", columns = ['Datum','Einkauf', 'Preis'])
df.to_excel('Ausgabenliste.xlsx', sheet_name=filename,index=False)

edited Sep 03 '20 at 08:16

answered Sep 03 '20 at 07:21

Sebastien D

4,369
4
18
46

Thank you! That works! How would i add a header (like date, item, price) and is it possible to add a sum of all the items automatically? – Sebastian Schmidt Sep 03 '20 at 08:08
this code works nearly as desired. It takes the .txt and stores it in the excel worksheet, but it creates a new excel every time. How would i open an existing workbook and append a worksheet for every .txt file? I want to run this once a month – Sebastian Schmidt Sep 04 '20 at 04:34
1

here is how you could achieve that : https://stackoverflow.com/a/46672894/7652544 – Sebastien D Sep 04 '20 at 07:37

save .txt data in excel worksheet

1 Answers1