This article discusses how to use jython to connect to an MS Excel file via ODBC using the JDBC-ODBC bridge.
Python, 32 lines
Download
Copy to clipboard1 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
Tags: jython
