Welcome, guest | Sign In | My Account | Store | Cart

This article discusses how to use jython to connect to an MS Excel file via ODBC using the JDBC-ODBC bridge.

Python, 32 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
from java.lang import *
from java.sql import *

'''
Path to the excel file.This can be an absolute path or the relative path. 
In case of relative path, relativity starts from where the script is run from.
'''
excelfile="values.xls"

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")

cnExcel=DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=%s;READONLY=true}" % excelfile,"","")

'''
Sheet1 is the name of the workbook in the excel sheet.All the 
values in the columns of first row will be taken as the 
column names.
'''
rs=cnExcel.createStatement().executeQuery("SELECT * FROM [Sheet1$])

while rs.next():
'''
The number in getString(i) method is dependent on the number of columns
available in the excel sheet
'''
    print rs.getString(1)

'''
Play safe.Close the connection and the recordset.
'''
rs.close()
cnExcel.close()

The advantage of accessing MS Excel via ODBC is that a broad range of SQL syntax is supported.Utilizing the support for the "WHERE" clause a subset of available data can be extracted based on "WHERE" conditions. e.g. SELECT * FROM [Sheet1$] WHERE DEPARTMENT=9

Created by Zabil CM on Fri, 10 Oct 2003 (PSF)
Python recipes (4591)
Zabil CM's recipes (2)

Required Modules

  • (none specified)

Other Information and Tasks