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')