I would like to retrieve a filtered data from a text file and send to excel.
I have these lines in a text.txt file:
I am Fred username is fred from USA cd
I am Robert username is bob from USA cd
I am John username is john from China cd
I am Frank username is frank from France cd

And I need to get only the name, username and country and create a spreadsheet with these results. Three columns with headers

I have tried many codes. This is my last one, but it is sending just the last name, username and country.

import xlwt

result = []
with open("text.txt") as origin_file:
    for line in origin_file:

        if 'username' in line:
            result.append(line.split(' ')[2])
            #result.append(int(line))
    #print(len(result))
    # Display all string elements in list.
    for st in result:
        row = st
        print(row)

result2 = []
with open("text.txt") as origin_file:
    for line in origin_file:
        if 'username' in line:
            result2.append(line.split(' ')[5])
    for st2 in result2:
        row2 = st2
        print(row2)

result3 = []
with open("text.txt") as origin_file:
    for line in origin_file:
        if 'username' in line:
            result3.append(line.split(' ')[7])
    for st3 in result3:
        row3 = st3 + "\n"
        print(row3)

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Test')

style_string = "font: bold on"
style = xlwt.easyxf(style_string)

worksheet.write(0, 0, 'Name', style=style)
worksheet.write(0, 1, 'Username', style=style)
worksheet.write(0, 2, 'Country', style=style)

worksheet.write(1, 0, row)
worksheet.write(1, 1, row2)
worksheet.write(1, 2, row3)

workbook.save('test.xls')
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.