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:
| 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.
