1

I am trying to access Excel as a database using the groovy.sql package, which is a GDK extension to JDBC. I am aware that Excel is not a database and that for most tasks Apache POI works better. I am using an Excel file named weather.xlsx as a test document. The workbook contains only one sheet which looks like this:

Sample Excel file

City Temperature
Denver 19
Boston 12
New York 22

I am using this code:

import groovy.sql.Sql

class ExcelAsDB {
    static void main(args) {
        def sql = Sql.newInstance(
                """jdbc:odbc:Driver=
                {Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
                    DBQ=C:\\path_to_file\\weather.xlsx;READONLY=false""", '', '')

        println "City\t\tTemperature"
        sql.eachRow('SELECT * FROM [temperatures$]') {
            println "${it.city}\t\t${it.temperature}"
        }
    }
}

When I run the code, I get the following error message:

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:odbc:Driver=
                {Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
                    DBQ=C:\path_to_file\weather.xlsx;READONLY=false
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
    at groovy.sql.Sql.newInstance(Sql.java:396)
    at groovy.sql.Sql$newInstance.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:157)
    at Excel.main(Excel.groovy:7)

I've read in similar questions such as java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver Exception occurring. Why? and JDBC ODBC Driver Connection that the ODBC bridge was removed from Java with Java 8. However, I was under the impression that if I connect directly to the Excel driver, rather than setup a DSN for the Excel file, I should be able to access the file anyway.

I am using a Windows computer and have confirmed that the Excel Driver is installed. I am using Groovy Version: 3.0.8 JVM: 16 Vendor: Oracle Corporation OS: Windows 10.

qwerty
  • 810
  • 1
  • 9
  • 26
  • 1
    Do you have both 32- and 64-bit versions of the Excel driver ? – Robin Mackenzie Jul 04 '21 at 02:58
  • @RobinMackenzie Looks like I only have the 64-bit version. – qwerty Jul 04 '21 at 03:00
  • 1
    And is your groovy installation running 64-bit ? Sometimes this 'driver not found' stuff is due to different 'bitness'. Not necessarily your root cause, but worth checking. – Robin Mackenzie Jul 04 '21 at 03:06
  • @RobinMackenzie I'm using a 64 bit operating system, and I downloaded Groovy using the installer, which I believe checks and downloads the right one. – qwerty Jul 04 '21 at 03:10
  • Jdbc-odbc bridge has been removed from java 8+. https://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/bridge.html I know that people copying jdbcOdbc.dll from java 7, but it's risky... – daggett Jul 04 '21 at 04:24
  • Java 8 doesn't have the JDBC-ODBC bridge, so it does not support the `jdbc:odbc:` URL prefix. – Mark Rotteveel Jul 04 '21 at 09:30

0 Answers0