Categories
Programming

Converting xls to xml via csv using Python

The title says it all, really. I wanted to learn flex mxml and actionscript, and at the same time at work I was looking to access todo data kept in an excel sheet that is used by a bunch of people in different departments and represent it in a way that helped one make sense of it. Using Python, I wrote a script that converted the exported csv file to xml, using the header line in the csv file to construct the xml elements.

Rant : Flex 3 in the current version really is *not* good at connecting to databases directly – it always needs an intermediary solution like a server (cold fusion, php, pyamf) to connect to the data. When developing AIR applications, which are local flex applications, at least you have access to the local filesystem.

So, to get *something* into flex without a server, I wrote the following Python program. It’s for version 2.5 but I reckon it will work on other versions as well. It’s command line only, you run it with as input the csv file to be converted, and it writes it out to the screen. If you want to capture it to a file, just do a redirect to a new file like this :

csv2xml.py input.csv > output.xml

The xml is actually E4X, Ecmascript for XML. The csv file must have ; as a seperator, which is standard when you export to csv from Excel 2003.

I programmed in a few prettifiers, like replacing spaces with underscores and removing ( and ) from the xml elements, but if you use accents (é) in your headers or slashes (/) it will most likely bork on that – either that or you get a malformed xml file.

Click to see the attachment : csv2xml_v18. Since my provider also provides python server-side, I’ve renamed the .py file to .txt. Simply rename it to .py again. The previous version ( csv2xml_v16 ) is still here as well.

Of course, after writing this, I discovered that somebody had already had the same need, and has written a swc for flex to access excel files directly ! Still, it was a useful programming exercise for me, keeping me skills alive… and in case you were needing this, feel free to use this.

(Visited 3,584 times, 1 visits today)

7 replies on “Converting xls to xml via csv using Python”

You mean using Python to convert XML to CSV ? Or using Flex ?

I’ve been giving it some thought, but it all depends if it is simple kind of xml. If it’s complicated with say, naming attributes and nested nodes, it becomes more problematic what you want to do with it.

It would be helpful if you post a small example of the xml you want to convert.

It seems a shame that your links are now bring up a 404 error.
The codes sounded interesting and useful.
🙁

My bad. It seems that when I moved hosting providers around end of October, I disregarded one of the very reasons that I moved to them : they allow python on their server.

So my python files are not visible on the server for the same reasons php files are not shown – they are not meant to be shown !

I’ve renamed the files to txt files. This seems to do the trick without me disabling python file handling all together…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.