473,289 Members | 1,866 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Re: newbie - merging xls files using xldt and xlwt

Hi John,

Thanks! Using a higher xlrd version did the trick! Regarding your other remarks:
-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.
-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?

Thanks again!
Albert-Jan
--- On Wed, 10/15/08, John Machin <sj******@lexicon.netwrote:
From: John Machin <sj******@lexicon.net>
Subject: Re: newbie - merging xls files using xldt and xlwt
To: py*********@python.org
Date: Wednesday, October 15, 2008, 3:14 PM
On Oct 15, 9:16*pm, Albert-jan Roskam
<fo...@yahoo.comwrote:
Hi,

I wrote the program below to merge all xls files in a
given directory into one multisheet xls file. It uses xlwt
and xlrd. The xls files I use for input are generated by
Spss. When I open and re-save the files in Excel, the
program works, but when I use the xls files as they were
created by Spss, the program returns an error message (see
below). Some details: Python 2.4, xlwt version 0.7.0, xlrd
version 0.5.2, Win NT.

Any ideas?
The version of xlrd that you are using is an antique. Go to
http://pypi.python.org/pypi/xlrd and get the latest
version. If that
doesn't work, send me a copy of one of the files that
is displaying
this problem.

Thanks in advance!

Albert-Jan

"""
Merge all xls files in a given directory into one
multisheet xls file.
The sheets get the orginal file name, without the
extension.
File names should not exceed 31 characters, as this is
the maximum
sheet name length
"""

import xlrd, xlwt
import glob, os.path

def merge_xls (in_dir,
out_file="d:/merged_output.xls"):

* * xls_files * = glob.glob(in_dir +
"*.xls")
* * sheet_names = []
* * merged_book = xlwt.Workbook()

* * [sheet_names.append(os.path.basename(v)[:-4])
for k, v in enumerate(xls_files)]

Wah! Try this:
sheet_names = [os.path.basename(v)[:-4]) for v in
xls_files]

* * for k, xls_file in enumerate(xls_files):
* * * * if len (sheet_names[k]) <= 31:
* * * * * * book = xlrd.open_workbook(xls_file)
* * * * * * ws =
merged_book.add_sheet(sheet_names[k])
* * * * * * for sheetx in range(book.nsheets):
* * * * * * * * 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))

I presume that you're not too worried about any date
data.

If an input file has more than 1 sheet, you are creating
only one
sheet in the output file, and overwriting cells.
* * * * else:
[snip]

merge_xls(in_dir="d:/temp/")

*** WARNING: OLE2 inconsistency: SSCS size is 0 but
SSAT size is non-zero ***
put_cell 0 1

Traceback (most recent call last):
[snip]
* * self._cell_types[rowx][colx] = ctype
IndexError: list assignment index out of range
Yeah, symptom of an xls file with a DIMENSIONS records that
lies ...
xlrd has like Excel become more resilient over time :-)

BTW, consider joining the python-excel group at
http://groups.google.com/group/python-excel

Cheers,
John
--
http://mail.python.org/mailman/listinfo/python-list


Oct 15 '08 #1
0 2331

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Cy Huckaba | last post by:
I have an XML document that is linked to other document and I can't figure out what the best way to try and merge them before query qith an XpathNavigator. Simple example...a root xml document...
3
by: Mike | last post by:
Hi! I also asked this question in C# group with no results: I have 2 datasets loaded with data from two xml files having the same schema. The files contain data from yesterday and today. I'd...
2
by: Nikhil Prashar | last post by:
I'm trying to merge two XML files that have the same structure but not necessarily the same nodes in the same order. I've tried opening the files as datasets and using the DataSet.Merge() function,...
2
by: daniel.knights | last post by:
Hello, I was wondering if anyone could shed some light on an issue I am having. I have 3 or 4 xml files which i need to merge onto another xml file called build_log.xml, which is produced using...
10
by: n o s p a m p l e a s e | last post by:
Is it possible to merge two DLL files into one? If so, how? Thanx/NSP
1
by: adamrace | last post by:
Hi, I've got two excel files, one has a list of products and their current prices and they all have a product ID, I have another file with a list of price's that need updating. I was wondering...
0
by: veer | last post by:
Hello sir. I am making a program on merging in Visual Basic. The program is that I have a folder which is not on my hard drive contain 80 Mdb files and each Mdb file contains two tables. I want to...
5
by: Albert-jan Roskam | last post by:
Hi, I wrote the program below to merge all xls files in a given directory into one multisheet xls file. It uses xlwt and xlrd. The xls files I use for input are generated by Spss. When I open and...
2
by: nicstel | last post by:
Hello, I'm trying to find documentation about the xlwt (py_excelerator). I want to change the border of some cells. But the only types that I found is: double and dashed. How to do a simple line...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.