4

I have 100 txt files in a folder (named as pos). I would like to copy all the file contents and paste them as rows in an excel file. I found some codes from stackoverflow, but they are not working. Please help me.

import xlwt
import os
import glob

wbk = xlwt.Workbook()
sheet = wbk.add_sheet('data')
path= 'C:\tweet\pos'
row = 0

for files in os.walk(path):
...     for file in files:
...         if fnmatch(file, '*.txt'):
...             L = open(os.path.join( file), "r").read()
...             sheet.write(row,5,L)
...             row += 1
...             

wbk.save('read_all_txt_in_folders.xls')
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
kevin
  • 1,914
  • 4
  • 25
  • 30
  • One problem is that `\t` is interpreted as a tab character. Either use a raw string: `r'C:\tweet\pos'` or forward slashes: `C:/tweet/pos` or escape the backslashes: `C:\\tweet\\pos` – mechanical_meat Feb 19 '13 at 17:40
  • 1
    What does "not working" mean? What happens instead? – octern Feb 19 '13 at 17:41
  • You're also importing `glob` but not using it. And there is no import for `fnmatch` – mechanical_meat Feb 19 '13 at 17:42
  • Also, are you pasting this into a file? The "..." characters are part of the prompt when python is being run interactively. They will cause errors if you type them in. – octern Feb 19 '13 at 17:45

1 Answers1

2

The following program works for me.

Notes:

  • The '\t' is being interpreted as a tab, not a path delimiter. Try using forward slashes.
  • It is import fnmatch / fnmatch.fnmatch(pattern, file). glob is not required.
  • I had to remove the trailing newlines from the strings. In my test case, using L[:-1] was sufficient. You may require a more robust solution.
  • os.walk() returns a tuple: (directory, subdirectories, files).
  • I have left my debugging statements in comments in case they help you.

.

import xlwt
import os
import fnmatch

wbk = xlwt.Workbook()
sheet = wbk.add_sheet('data')
row = 0

# sheet.write(1, 1, "Hello")

for (dir, dirs, files) in os.walk('.'):
     # print dir
     for file in files:
         # print " ", file
         if fnmatch.fnmatch(file, '*.txt'):
             L = open(os.path.join(dir, file), "r").read()
             # print "  ", L.__repr__()
             a = sheet.write(row,5,L[:-1])
             # sheet.write(row, 4, "hello")
             # print "   ", a
             row += 1

wbk.save('read_all_txt_in_folders.xls')
Robᵩ
  • 163,533
  • 20
  • 239
  • 308
  • P.s. to the experts: is there a Pythonic expression which will yield a list or generator for all of the `os.path.join(dir,file)` results? (e.g., `['./foo', './sub/bar', './sub/bar2', './sub2/bar']`? – Robᵩ Feb 19 '13 at 18:02
  • And the answer is: `(os.path.join(dir,file) for dir,dirs,files in os.walk('.') for file in files)`. See http://stackoverflow.com/a/13051822/8747 – Robᵩ Feb 19 '13 at 18:22