On Feb 7, 1:40 pm, Dave Angel <d...@davea.name> wrote:
> On 02/07/2012 01:14 PM, smac2...@comcast.net wrote:> Hello. I am admittedly a
> Python novice, and ran into some trouble
> > trying to write a program that will pull multiple excel files all into
> > one file, with each file on a different sheet.
>
> > I am confident most of the code is correct, as the program runs
> > without any errors and I found the base of it online, making changes
> > as necessary for my own purposes. However, I am having trouble
> > specifying the exact directory where my code should be pulling the
> > files from. All the files are in the same folder, and I have put the
> > folder on my desktop. Am I correct in thinking that I need to change
> > the current working directory to this folder in order for Python to
> > read in these files,
>
> No, Python certainly does not constrain you to working with files only
> in the current working directory. My rule of thumb is never to change
> the cwd in a Python program. You can use relative paths to open files,
> or you can use absolute paths. There is even a library function
> os.path.abspath() for converting a relative path to an absolute one.
>
> If you do change cwd during the running of a program, then relative
> filenames that worked earlier might no longer work. You could convert
> them all to absolute paths, but that's more work.
>
> You can piece together path strings using os.path.join(). It's smart
> enough to know the path separator for your particular platform.
>
> Check out this page:http://docs.python.org/library/os.path.html
>
> > then generate my output? Or should I be doing
> > something else?
>
> > Any and all help is appreciated, thanks!
>
> --
>
> DaveA
Thanks Dave. I am a bit lost as to what the problem is then - the
program runs glitch free, but then only prints: "NOTE *** No xls files
in C:/Documents and Settings/smacdon/." as specified below by my
program. Any idea what the issue might be (my code is below):
import xlrd, xlwt
import glob, os.path
def merge_xls (in_dir, out_file="C:\Documents and Settings\smacdon
\Desktop\09 Aggregate JWS\09_merged_data.xls"):
xls_files = glob.glob(in_dir + "*.xls")
sheet_names = [os.path.basename(v)[:-4] for v in xls_files]
sheet_excl = [os.path.basename(v)[:-4] for v in xls_files if len
(os.path.basename(v)[:-4]) > 29]
merged_book = xlwt.Workbook()
if in_dir[-1:] != "/": in_dir = in_dir + "/"
xls_files.sort()
if xls_files:
for k, xls_file in enumerate(xls_files):
print "---> Processing file %s" % (xls_file)
if len (sheet_names[k]) <= 29:
book = xlrd.open_workbook(xls_file)
if book.nsheets == 1:
ws = merged_book.add_sheet(sheet_names[k])
sheet = book.sheet_by_index(0)
for rx in range(sheet.nrows):
for cx in range(sheet.ncols):
ws.write(rx, cx, sheet.cell_value(rx,
cx))
elif book.nsheets in range(2, 100):
for sheetx in range(book.nsheets):
sheet0n = sheet_names[k]+str(sheetx
+1).zfill(2)
ws = merged_book.add_sheet(sheet0n)
sheet = book.sheet_by_index(sheetx)
for rx in range(sheet.nrows):
for cx in range(sheet.ncols):
ws.write(rx, cx, sheet.cell_value(rx,
cx))
else:
print "ERROR *** File %s has %s sheets (maximum is
99)" % (xls_file, book.nsheets)
raise
else:
print "WARNING *** File name too long: <%s.xls>
(maximum is 29 chars) " % (sheet_names[k])
print "WARNING *** File <%s.xls> was skipped." %
(sheet_names[k])
merged_book.save(out_file)
print
print "---> Merged xls file written to %s using the following
source files: " % (out_file)
for k, v in enumerate(sheet_names):
if len(v) <= 29:
print "\t", str(k+1).zfill(3), "%s.xls" % (v)
print
if sheet_excl:
print "--> The following files were skipped because the
file name exceeds 29 characters: "
for k, v in enumerate(sheet_excl):
print "\t", str(k+1).zfill(3), v
else:
print "NOTE *** No xls files in %s." % (in_dir)
merge_xls(in_dir="C:\Documents and Settings\smacdon\Desktop\09
Aggregate JWS"
--
http://mail.python.org/mailman/listinfo/python-list
