Recipe7.19.Using ODBC to Get Excel Data with Jython


Recipe 7.19. Using ODBC to Get Excel Data with Jython

Credit: Zabil CM

Problem

Your Jython script needs to extract data from a Microsoft Excel file.

Solution

Jython, just like Java, can access ODBC through the JDBC-ODBC Bridge, and Microsoft Excel can in turn be queried via ODBC:

from java import lang, sql lang.Class.forName('sun.jdbc.odbc.JdbcOdbcDriver') excel_file = 'values.xls' connection = sql.DriverManager.getConnection(     'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=%s;READONLY=true}' %     excelfile, '', '') # Sheet1 is the name of the Excel workbook we want.  The field names for the # query are implicitly set by the values for each column in the first row. record_set = connection.createStatement( ).executeQuery(              'SELECT * FROM [Sheet1$]') # print the first-column field of every record (==row) while record_set.next( ):     print record_set.getString(1) # we're done, close the connection and recordset record_set.close( ) connection.close( )

Discussion

This recipe is most easily used on Microsoft Windows, where installing and configuring ODBC, and the Microsoft Excel ODBC driver in particular, is best supported. However, with suitable commercial products, you can equally well use the recipe on an Apple Macintosh or just about any other Unix version on the planet.

Using ODBC rather than alternate ways to access Microsoft Excel has one substantial advantage that is not displayed in this recipe: with ODBC, you can use a broad subset of SQL. For example, you can easily extract a subset of a workbook's row by adding a WHERE clause, such as:

SELECT * FROM [Sheet1$] WHERE DEPARTMENT=9

Since all of the selection logic can be easily expressed in the SQL string you pass to the executeQuery method, this approach lends itself particularly well to being encapsulated in a simple reusable function.

If you're coding in Classic Python (CPython) rather than Jython, you can't use JDBC, but you can use ODBC directly (typically in the DB API-compliant way supported by mxODBC, http://www.egenix.com/files/python/mxODBC.html) to perform this recipe's task in a similar way.

See Also

The Jython site (http://www.jython.org); JDBC's home page (http://java.sun.com/products/jdbc); Recipe 12.7, for another way to access Excel data (by parsing the XML file that Excel can be asked to output).



Python Cookbook
Python Cookbook
ISBN: 0596007973
EAN: 2147483647
Year: 2004
Pages: 420

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net