<div>Hi,</div>
<div> </div>
<div>I have been provided with the program below. Which sets out to merge singlesheet excel files into one multisheet excel workbook.</div>
<div> </div>
<div>Though it manages to merge multiple files into one workbook many of the formats are not preserved. For example, what was originally 22.92% is converted to 0.2292...</div>
<div> </div>
<div>I was hoping if somebody could please help me understand how this can be fixed. Ideally I would like to create a program which merges files whilst keeping all formats (if at all possible).</div>
<div> </div>
<div>#######################################</div>
<div>"""<br>Merge all xls files in a given directory into one multisheet xls file.<br>The sheets get the orginal file name, without the extension.<br>File names should not exceed 29 characters<br>"""</div>
<div>import xlrd, xlwt<br>import glob, os.path</div>
<div>def merge_xls (in_dir, out_file="K:/Stats Jobs 2003/St0883 (CITB - J33882)/3. Results/Excel/merged_output.xls"):<br> <br> xls_files = glob.glob(in_dir + "*.xls")<br> sheet_names = [os.path.basename(v)[:-4] for v in xls_files]<br>
sheet_excl = [os.path.basename(v)[:-4] for v in xls_files if len(os.path.basename(v)[:-4]) > 29]<br> merged_book = xlwt.Workbook()<br> <br> if in_dir[-1:] != "/": in_dir = in_dir + "/"<br>
xls_files.sort()</div>
<div> if xls_files:<br> for k, xls_file in enumerate(xls_files):<br> print "---> Processing file %s" % (xls_file)<br> if len (sheet_names[k]) <= 29:<br> book = xlrd.open_workbook(xls_file)<br>
if book.nsheets == 1:<br> ws = merged_book.add_sheet(sheet_names[k])<br> sheet = book.sheet_by_index(0)<br> for rx in range(sheet.nrows):<br> for cx in range(sheet.ncols):<br>
ws.write(rx, cx, sheet.cell_value(rx, cx))<br> elif book.nsheets in range(2, 100):<br> for sheetx in range(book.nsheets):<br> sheet0n = sheet_names[k]+str(sheetx+1).zfill(2)<br>
ws = merged_book.add_sheet(sheet0n)<br> sheet = book.sheet_by_index(sheetx)<br> for rx in range(sheet.nrows):<br> for cx in range(sheet.ncols):<br>
ws.write(rx, cx, sheet.cell_value(rx, cx))<br> else:<br> print "ERROR *** File %s has %s sheets (maximum is 99)" % (xls_file, book.nsheets)<br> raise<br>
else:<br> print "WARNING *** File name too long: <%s.xls> (maximum is 29 chars) " % (sheet_names[k])<br> print "WARNING *** File <%s.xls> was skipped." % (sheet_names[k])</div>
<div> merged_book.save(out_file)</div>
<div> print<br> print "---> Merged xls file written to %s using the following source files: " % (out_file)<br> for k, v in enumerate(sheet_names):<br> if len(v) <= 29:<br> print "\t", str(k+1).zfill(3), "%s.xls" % (v)<br>
print<br> if sheet_excl:<br> print "--> The following files were skipped because the file name exceeds 29 characters: "<br> for k, v in enumerate(sheet_excl):<br> print "\t", str(k+1).zfill(3), v<br>
else:<br> print "NOTE *** No xls files in %s. Nothing to do." % (in_dir)</div>
<div>merge_xls(in_dir="C:/temp/test merge xls/")<br>#######################################</div>
<div> </div>
<div> </div>
<div>Many thanks in advance.</div>
<div> </div>
<div>Jigs</div>
<div> </div>
<div> </div>
<div class="gmail_quote">2008/11/3 J Sutar <span dir="ltr"><<a href="mailto:jsutar@gmail.com">jsutar@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<div>Mr Roskam,</div>
<div> </div>
<div>We have interacted before on the SPSS listserve, you have helped me out fair few times!</div>
<div> </div>
<div>I need a helping hand again, do you have the final solution to this problem, mergin xls from a given directory?</div>
<div> </div>
<div>Also where do I download the relevant modules from?</div>
<div> </div>
<div>Cheers</div>
<div>Jigs<br><br></div>
<div class="gmail_quote">2008/10/15 Albert-jan Roskam <span dir="ltr"><<a href="mailto:fomcl@yahoo.com" target="_blank">fomcl@yahoo.com</a>></span>
<div>
<div></div>
<div class="Wj3C7c"><br>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Hi John,<br><br>Thanks! Using a higher xlrd version did the trick! Regarding your other remarks:<br>-yep, input files with multiple sheets don't work yet. I kinda repressed that ;-) Spss outputs only single-sheet xls files, but I agree it's nicer if the programs works in other cases too.<br>
-and no, I don't intend to use data fields. Wouldn't it be easier to convert those to string values if I ever came across them?<br><br>Thanks again!<br>Albert-Jan<br><br><br>--- On Wed, 10/15/08, John Machin <<a href="mailto:sjmachin@lexicon.net" target="_blank">sjmachin@lexicon.net</a>> wrote:<br>
<br>> From: John Machin <<a href="mailto:sjmachin@lexicon.net" target="_blank">sjmachin@lexicon.net</a>><br>> Subject: Re: newbie - merging xls files using xldt and xlwt<br>> To: <a href="mailto:python-list@python.org" target="_blank">python-list@python.org</a><br>
> Date: Wednesday, October 15, 2008, 3:14 PM<br>
<div>
<div></div>
<div>> On Oct 15, 9:16 pm, Albert-jan Roskam<br>> <<a href="mailto:fo...@yahoo.com" target="_blank">fo...@yahoo.com</a>> wrote:<br>> > Hi,<br>> ><br>> > I wrote the program below to merge all xls files in a<br>
> given directory into one multisheet xls file. It uses xlwt<br>> and xlrd. The xls files I use for input are generated by<br>> Spss. When I open and re-save the files in Excel, the<br>> program works, but when I use the xls files as they were<br>
> created by Spss, the program returns an error message (see<br>> below). Some details: Python 2.4, xlwt version 0.7.0, xlrd<br>> version 0.5.2, Win NT.<br>> ><br>> > Any ideas?<br>><br>> The version of xlrd that you are using is an antique. Go to<br>
> <a href="http://pypi.python.org/pypi/xlrd" target="_blank">http://pypi.python.org/pypi/xlrd</a> and get the latest<br>> version. If that<br>> doesn't work, send me a copy of one of the files that<br>> is displaying<br>
> this problem.<br>> ><br>> > Thanks in advance!<br>> ><br>> > Albert-Jan<br>> ><br>> > """<br>> > Merge all xls files in a given directory into one<br>> multisheet xls file.<br>
> > The sheets get the orginal file name, without the<br>> extension.<br>> > File names should not exceed 31 characters, as this is<br>> the maximum<br>> > sheet name length<br>> > """<br>
> ><br>> > import xlrd, xlwt<br>> > import glob, os.path<br>> ><br>> > def merge_xls (in_dir,<br>> out_file="d:/merged_output.xls"):<br>> ><br>> > xls_files = glob.glob(in_dir +<br>
> "*.xls")<br>> > sheet_names = []<br>> > merged_book = xlwt.Workbook()<br>> ><br>> > [sheet_names.append(os.path.basename(v)[:-4])<br>> for k, v in enumerate(xls_files)]<br>
><br>> Wah! Try this:<br>> sheet_names = [os.path.basename(v)[:-4]) for v in<br>> xls_files]<br>><br>><br>> > for k, xls_file in enumerate(xls_files):<br>> > if len (sheet_names[k]) <= 31:<br>
> > book = xlrd.open_workbook(xls_file)<br>> > ws =<br>> merged_book.add_sheet(sheet_names[k])<br>> > for sheetx in range(book.nsheets):<br>> > sheet =<br>
> book.sheet_by_index(sheetx)<br>> > for rx in range(sheet.nrows):<br>> > for cx in<br>> range(sheet.ncols):<br>> > ws.write(rx, cx,<br>
> sheet.cell_value(rx, cx))<br>><br>> I presume that you're not too worried about any date<br>> data.<br>><br>> If an input file has more than 1 sheet, you are creating<br>> only one<br>> sheet in the output file, and overwriting cells.<br>
><br>> > else:<br>> >[snip]<br>> ><br>> > merge_xls(in_dir="d:/temp/")<br>> ><br>> > *** WARNING: OLE2 inconsistency: SSCS size is 0 but<br>> SSAT size is non-zero ***<br>
> > put_cell 0 1<br>> ><br>> > Traceback (most recent call last):<br>> [snip]<br>> > self._cell_types[rowx][colx] = ctype<br>> > IndexError: list assignment index out of range<br>><br>
> Yeah, symptom of an xls file with a DIMENSIONS records that<br>> lies ...<br>> xlrd has like Excel become more resilient over time :-)<br>><br>> BTW, consider joining the python-excel group at<br>> <a href="http://groups.google.com/group/python-excel" target="_blank">http://groups.google.com/group/python-excel</a><br>
><br>> Cheers,<br>> John<br>> --<br>> <a href="http://mail.python.org/mailman/listinfo/python-list" target="_blank">http://mail.python.org/mailman/listinfo/python-list</a><br><br><br><br>--<br><a href="http://mail.python.org/mailman/listinfo/python-list" target="_blank">http://mail.python.org/mailman/listinfo/python-list</a><br>
</div></div></blockquote></div></div></div><br></blockquote></div><br>