July 20, 2008

Codeigniter Excel and PHP’s Spreadsheet Excel Writer Pear Package and how to not have to use PEAR

Filed under: codeigniter, cool, php, tip, xls — michaelangela @ 3:08 am

Well that’s a long title but it sums it up. I wanted to generate Excel from Codeigniter. Because of that I needed a way to use it whether or not PEAR is running/installed, etc. My local dev box doesn’t (yet) have it but that’s a story for another time.

The talented developers at Pizza SEO repackaged one of the recent versions of Spreadsheet Excel Writer as a set of classes together. No PEAR, no installing of separate items, etc. Normally I would like PEAR and it is on the deployment server but I wanted a portable flexbile version that worked within CodeIgniter and this fit the bill. Give them a visit! 🙂

PHP Excel Creator library | Pizza SEO

Pizza SEO has developed library for writing Excel (XLS) documents in PHP from former PEAR class Spreadsheet Excel Writer (see PEAR). We have cleaned this class, so PEAR installation is not required anymore and class is available as the standalone class.


July 6, 2008

Getting rid of the dreaded “can’t encode” errors with Python and MySQL and SqlSoup

Filed under: automation, cool, dev, mysql, python, sqlalchemy, sqlsoup, tip, xls — michaelangela @ 10:47 pm

So I was getting problems inserting data that has the ‘™’ symbol in it. Brutal stuff. Couldn’t find a way around it. Did it by hand in the end. Nasty. The problem was that I was getting

UnicodeEncodeError: 'latin-1' codec can't encode character u'\u2122' in position 12: ordinal not in range(256)

where the '\u2122' is the ‘™’ symbol. There were some others as well. Finally happened upon this post which, fortunately, cleared it up. To use this tip with SqlSoup, the connection string needs a couple of extra parameters.

Instead of

db = SqlSoup('mysql://user:password@host:port/database')


db = SqlSoup('mysql://user:password@host:port/database?charset=utf8&use_unicode=0')

The requirement of course is that the database tables are set up with the UTF-8 charset.

But now I can thrash data at will! Mwahahahaha! Sorry… but now I can pull in unicode data from an Excel file and throw it into a database with rediculous ease. OK not rediculous ease but wow this simplifies it.

[edit]I like to use iPython to do my data thrashing with SqlSoup and SqlAlchemy. If you use the second connection string with the charset and use_unicode options, don’t reset the db connection to the previous one without those. Once doing that I couldn’t set it back and had to exit and restart the session. Bummer.[/edit]

mysql unicode issues – sqlalchemy | Google Groups

a. have use_unicode=0, set charset=’utf8′ on the connection, AND

b. ensure you have
table options = {‘mysql_charset’: ‘utf8’} when creating tables, and
all should be well.

Python Excel file reader

Filed under: cool, python, tip, xls — michaelangela @ 1:36 am

This thing is just brilliant! I am working on a project that uses PHP. But I still do my data mangling with Python because of tools like SqlSoup running under SqlAlchemy. Using those under iPython has saved incredible amounts of time. I actually like working with data in this way. It just makes it more fun. Well, I have some data in an Excel file that has multiline text in it. That can be a pain with CSV files. A little digging located a Python Excel writer which had stopped development, with the suggest to use a Python port of a Perl writer, which while cool for writing, has trouble reading… but a helpful post pointed to xlrd. A quick “sudo easy_install-2.5 xlrd” later and I was able to do:

import xlrd
book = xlrd.open_workbook("myfile.xls")
sh = book.sheet_by_index(0)
for r in range(sh.nrows)[1:]:
print sh.row(r)[:4]

Sweet. Indeed. All of the Unicode elements and multilines are intact and good to go. Good stuff.

Python Package Index : xlrd 0.6.1

Library for developers to extract data from Microsoft Excel ™ spreadsheet files

Extract data from new and old Excel spreadsheets on any platform. Pure Python (2.1 or later). Strong support for Excel dates. Unicode-aware.

Blog at WordPress.com.