Is ExcelJConnect standard JDBC driver? How to connect to XLSX in such a tool like SQuirreL SQL Client?

[t603]

Hello, developers,

may I ask You, if ExcelJConnect is standard JDBC driver? How to connect to XLSX in such a tool like SQuirreL SQL Client or any other Java database GUI tool? I tried to set up ExcelJConnect in SQuirreL SQL Client, but when tried to select any data, I have no luck (message like Unsupported operation appears). JDBC URL is "jdbc:inetexcel:C:\temp\test.xlsx?hasHeaderRow=false", connected without errors, but that is all. No data. I tried SQL SELECT: select * from "Sheet1", but no luck.

Or is ExcelJConnect targeted only for Java code as a component, library as seen in https://github.com/i-net-software/ExcelJConnect Usage chapter?

I am looking for some simple, free, read only XLSX JDBC driver. Well known XLSX drivers are full of capabilities, most of such capabilities not needed by me, but all of such (two) drivers are quite expensive, especially for my purposes.

Thank You for Your answer in advance, Stepan

[Horcrux7]

What you means with "standard JDBC driver"?

• It implements the JDBC interface
• But it is only read only, this means not standard conform.
• We have only implements a subset that it work with our application. But you can make PR to improve it.

I tried to set up ExcelJConnect in SQuirreL SQL Client

I have never test the driver with this tool.

No data. I tried SQL SELECT: select * from "Sheet1", but no luck.

This will not work. The driver make the sheet available as procedures and not as tables. You can try: {call Sheet1} if you enter SQL. But the tool should list the available procedures. We have implements it as procedures to make it simple without complex SQL parsing and filtering.

Or is ExcelJConnect targeted only for Java code as a component, library as seen in https://github.com/i-net-software/ExcelJConnect Usage chapter?

I am unclear what you means. If you use its inside of SQuirreL SQL then it is also only component, library.

I am looking for some simple, free, read only XLSX JDBC driver. Well known XLSX drivers are full of capabilities, most of such capabilities not needed by me, but all of such (two) drivers are quite expensive, especially for my purposes.

That we have written it. The question is which features do you need?

[t603]

Well, unfortunately, it SQuirreL SQL Client (free, opensource, pure Java GUI over JDBC driver available on https://squirrel-sql.sourceforge.io/) can not work with ExcelJConnect in the same ways as it can work with JDBC drivers of other databases. SQuirreL does not list any tables, procedures, functions etc. I tried to submitt call Sheet1, no luck.

The second use case, I would like to have XLSX driver, is to read data from XLSX into H2 database H2 database using CREATE LINKED TABLE command.

But never mind, I will watch Your project, if there will be some improvements in this field, because it looks like Your project is the only one free and opensource project under active development. CData and HTTX JDBC drivers are not only paid, but You have to order huge volume of licences (waste of everything), and although JDBCExcel looks very promising, but with no release after the first commit 6 years ago.

[Horcrux7]

There are some fixes. Please test the snapshot from Jitpack.

Or as direct download: https://javadoc.jitpack.io/com/github/i-net-software/ExcelJConnect/main-baf7659af0-1/ExcelJConnect-main-baf7659af0-1.jar

[t603]

Hello, Volker,

thank You for patches.

I tested ExcelJConnect "ExcelJConnect-main-baf7659af0-1.jar" with SQuirreL SQL Client 4.8.0 running on Adoptium Java 17 and with following configuration of Driver and Alias:

Testing SQL code is:

/* 
  2024-11-12a 
  working with XLSX in SQuirreL SQL Client 4.8.0 via ExcelJConnect 
  ExcelJConnect-main-baf7659af0-1.jar 
*/ 
--this is OK 
{call Sheet1}; 
--this is OK 
{call Sheet 2}; --but looks strange, it would look better with doublequotes {call "Sheet 2"} 
--this is OK 
{call š + ř}; --but looks strange, it would look better with doublequotes {call "š + ř"} 
--this is OK (direct export into CSV) 
@file 'C:\temp\temp\testOut_Sheet1.csv' {call Sheet1}; 
--this is OK (direct export into XLSX with multiple sheets) 
@msExcelWorkbook 'C:\temp\temp\testOut.xlsx' 
@sheet 'List1' {call Sheet1}; 
@sheet 'List 2' {call Sheet 2}; 
@sheet 'ě - č' {call š + ř}; 

Everything works great!

Maybe I would propose to enable doublequotes as delimiters of objects (procedures, sheets) for better reading, because

{call "Sheet 2"}

fails with

java.lang.IllegalArgumentException: There is no sheet with name ""Sheet 2"".

So, I guess, ExcelJConnect works with SQuirreL SQL Client. Thank You very much.

Stepan

P. S. Regarding usage of ExcelJConnect in H2 database in CREATE LINKED TABLE I will write another post to distinguish different usages.