By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

Python 3.7 filter text file lines and set results into an excel cell

P: 1
I would like to retrieve a filtered data from a text file to excel. Right now I am able to retrive just the last found line in the text file

this is my text 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

I am getting 3 lines with the same name, username, from (Frank, frank, france)

Also, the prints in the code gives me the results:

Fred
Robert
John
Frank
fred
bob
john
frank
USA
USA
China
France

I want to set in the excel file 3 collumns Name, Username, From

This is my code:

Expand|Select|Wrap|Line Numbers
  1. result = []
  2. with open("text.txt") as origin_file:
  3.     for line in origin_file:
  4.         if 'username' in line:
  5.             result.append(line.split(' ')[2])
  6.             #result.append(int(line))
  7.     #print(result)
  8.     #print(len(result))
  9.     # Display all string elements in list.
  10.     for st in result:
  11.         print(st)
  12.  
  13. result2 = []
  14. with open("text.txt") as origin_file:
  15.     for line in origin_file:
  16.         if 'username' in line:
  17.             result2.append(line.split(' ')[5])
  18.             #result.append(int(line))
  19.     #print(result)
  20.     #print(len(result))
  21.     # Display all string elements in list.
  22.     for st2 in result2:
  23.         print(st2)
  24.  
  25. result3 = []
  26. with open("text.txt") as origin_file:
  27.     for line in origin_file:
  28.         if 'username' in line:
  29.             result3.append(line.split(' ')[7])
  30.             #result.append(int(line))
  31.     #print(result)
  32.     #print(len(result))
  33.     # Display all string elements in list.
  34.     for st3 in result3:
  35.         print(st3)
  36. import xlwt
  37.  
  38.  
  39. workbook = xlwt.Workbook()
  40. worksheet = workbook.add_sheet('Test')
  41.  
  42. style_string = "font: bold on"
  43. style = xlwt.easyxf(style_string)
  44.  
  45. worksheet.write(0, 0, 'IP Address', style=style)
  46. worksheet.write(0, 1, 'Hostname', style=style)
  47. worksheet.write(0, 2, 'Users found', style=style)
  48.  
  49. worksheet.write(1, 0, st)
  50. worksheet.write(1, 1, st2)
  51. worksheet.write(1, 2, st3)
  52. worksheet.write(2, 0, st)
  53. worksheet.write(2, 1, st2)
  54. worksheet.write(2, 2, st3)
  55. worksheet.write(3, 0, st)
  56. worksheet.write(3, 1, st2)
  57. worksheet.write(3, 2, st3)
  58. worksheet.write(4, 0, st)
  59. worksheet.write(4, 1, st2)
  60. worksheet.write(4, 2, st3)
  61.  
  62. workbook.save('test.xls')
Jun 6 '18 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.