Thanks to a comment from a user of the program who had problems getting the script to process csv files on the mac, I’ve updated the script to version 1.8 so it will now open them without first needing to save the file in unix format when exporting to csv from Office for Mac.

For those of you who noted the version skip, version 1.7 has not been released, but just includes a simple test to see if the first field is empty or not. If empty the line is dropped. It’s been commented out in the latest version. Since my hosting provider also does python, I have renamed the file to .txt. Simply rename it to .py to have it working.

csv2xml_v18

[For the Pythonistas : I've set the csv module to now open the files in read 'universal' mode]

I’ve not been posting a lot on my blog lately, as I have been busy on several things at the same time. But I’m still programming in Flex (and in Python) and have been trying out ways to get them to work together. More details later on, including on my pet flex project that uses converted xml.

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.

After using Flex for a bit, and blogging here that the backend is not so extensive, I discovered that Google has brought out the Google App Engine (GAE) sometime this year. It allows you to write the backend in Python and the frontend in html or ajax or even flex ! It even comes with a templating engine, is currently in beta, and is free !

It should be the best of both worlds, if I can get it work together.

There’s actually not that many examples floating on the net that go into much detail into how to set this up. Lots of references to PyAMF (Python with Action Messaging Format so that it can talk Flash), but I find the examples are almost all from people in the know, and for a ‘script’ programmer like me I sure could use some (very) detailed explanations !

I finally found a good example of a simple Flex client that connected to a python Google App Engine on this site. After two nights of puttering around, I finally got my modified example to work and understood what was happening (I’m still learning flex, actionscript3 and now GAE – it’s a bit much sometimes). I actually write the xml back via a python hack where I do a loop and write the tags one by one… tss, I’m sure there’s a better way (pyAMF ?).

Now I still need to get my head around what PyAMF actually does and how it integrates between Flex and GAE. This seems to be a nice example from the PyAMF wiki.

After two weeks of so of looking into Flex, MXML and ActionScript, the key differences between the two languages are for me that Flex is first most oriented to the ‘front’ as opposed to python, that is more oriented to the ‘backend’.

With front I mean that everything so far that I discovered in Flex is oriented to presentation to the web client. There’s heaps of stuff in Flex that you can use to easily develop and present quality stuff to the web visitor (or even to the desktop, if you develop in Flex for AIR, Adobe’s desktop runtime solution that uses the same code as your web application).

But to get to the data from the backend, you cannot simply write an SQL query in your code; since flex code is compiled to flash, anybody with a flash decompiler could read the credentials you are using (as opposed to php or python code on a webserver, which is executed and where only the results are shown to the client; code is never shown).

So that means that to connect to a database, you need an intermediary solution; a php script to talk to or a coldfusion server, or a java scriptlet thingey; anything else really that can talk to a data server without giving away the keys to the kingdom aka the credentials to the db server.

Python on the other hand is very good at connecting to just about anything : from csv files to xml to SQL in Oracle, Access or Mysql database servers : it all works. However, presentation wise, there is a lack of an easy presentation framework that works as in Flex. There are several ways to present your results in Python, but they are not baked in; you have to go and search them. QT, PythonCard, Tkinter and others are all there, but all have differences. All require an extended time to learn.

PHP is a mixture of both : it can connect to just about everything, but my personal opinion is that development is slightly more convoluted than Flex, plus you really need to take care not to mix code and presentation.

Using the various MVC frameworks around (Symphony is nice, but biiiiig to learn as it involves writing parts in YAML, yet another (markup) language) you can do this, but my personal opinion is that it’s a lot to learn if you just develop a few web pages. There’s Django for the pythonistas, which I’m partial to and keep returning to, but haven’t actually made anything usefull with.

It’s all good and well to have ‘flash and bang’ for your web customer, but you need to show them your data as well – unless it’s an arty type, he’s there  on your site for a reason : to get informed about something.

My hope is that the next flash version or flex version will have data binding implemented via another easier way; Adobe wants to make this big, it’s the reason why they open sourced flex in the first place…

Moving your Applications directory to your /private/var/root partition (aka your MEDIA partition) is all well and good, but when you install python on your iphone, it’s installed in both /usr/bin where the main executable resides and the library modules are installed in /usr/lib/Python2.5 .

So they still consume a lot of space on your root partition. In fact, I finally checked my hard disk space on the root partition of the iphone, and there was a pityfull 4.2 Megabytes left over.

So I moved python over to the media partition using the following commands : I first created a python directory on my MEDIA partition, then I moved the python library directory over there and finally I created a symbolic link from there to where it is expected to be.

mkdir /private/var/root/python
mv /usr/lib/Python2.5 /private/var/root/python
ln -s /private/var/root/python/Python2.5 /usr/lib/Python2.5

After that, ‘df -h’ gave the message that 27 Megabyte was free on the root partition !

Excellent