Ch 10: Excell and Groovy SQL

Trying to follow the example on page 163 I get:

  groovy> def sql = groovy.sql.Sql.newInstance(""" 
  groovy> jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};
  groovy> DBQ=C:/Documents and Settings/me/AssayDB.xls;READONLY=false""", '', '')
  groovy> println "000" 
  groovy> sql.eachrow('SELECT * FROM [Substrates$]') {
  groovy>     println "${it.name}\t\t${it.abbreviation}" 
  groovy> }

  Exception thrown: java.sql.SQLException: No suitable driver

  java.sql.SQLException: No suitable driver
    at Script10.run(Script10:1)

I must need to install a driver of some sort?

thanks,
Eric

Eric,

Can you go into Control Panel and Data Sources(ODBC) and see if Microsoft Excel Driver is installed.
If you find it, try using the exact name as it appears in that panel (including the items in the parenthesis). Let me know what you find.

I have some Excel spreadsheets in a Unix/Linux environment and I’d like to use Groovy to read and convert the contents to XML. Any recommendations? Looking mainly at the cleanest way to read an Excel file in Unix/Linux. I’m ok with the XML conversion just added it for context.

Using http://poi.apache.org as a model.

Hi Eric,
I got your piece of code to work by adding ‘sun.jdbc.odbc.JdbcOdbcDriver’ as 4th parameter to your call to Sql.newInstance()

def sql = groovy.sql.Sql.newInstance(””“jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:/MyDataFile.xls;READONLY=false”””, ’’, ’’, ‘sun.jdbc.odbc.JdbcOdbcDriver’)