ExcelJdbc Driver

Introduction

The ExcelJdbc driver enables reading data from an Excel file using an SQL query as if from a database.

The SQL query addresses the data in an Excel sheet like a table. The following conventions apply:

  • a dummy connection 'jdbc:xlsx:' is used as the database
  • an Excel file is specified as the schema
  • the sheet name is specified as the table; alternatively, a numeric index can be used (:1 for the first sheet, :2 for the second sheet, etc.)
  • rows and columns are selected from the sheet

The following Excel file can be read, for example, using the driver:

Excel file

An SQL query over the ExcelJdbc driver would look like this:

SELECT column1
    , column2
    , column3
    FROM "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel/chinook.xlsx"."Track"
    COLUMNS(column1, column2, column3)
    WHERE rownumber <= 5

Here, the filename "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel/chinook.xlsx" is specified as the schema and "Track" is the sheet that is addressed as the table.

Alternatively, a numeric index can be used to access a sheet:

SELECT column1
    , column2
    , column3
    FROM "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel/chinook.xlsx".":1"
    COLUMNS(column1, column2, column3)
    WHERE rownumber <= 5

Here :1 is used to access the first sheet in the Excel file. :2 would refer to the second sheet, :3 to the third, etc. The order corresponds to the order in which the sheets are defined in the Excel file.

Columns are selected sequentially from the sheet. The column names specified in the COLUMN clause are assigned to them.

The result looks like this:

column1 column2 column3
------- ------- ------- 
TrackId Name    AlbumId
1       For Those About To Rock (We Salute You) 1
2       Balls to the Wall 2
3       Fast As a Shark 3
4       Restless and Wild 3

Headers

If the Excel sheet has headers (as in our example Excel), they can be used to identify column names.

The HEADLINE clause is used for this:

SELECT trackid,name,composer
FROM "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel/chinook.xlsx"."Track"
COLUMNS(trackid,name,composer)
HEADLINE 1
WHERE rownumber <= 8

The line number with the headers (after HEADLINE) has the following effects:

  1. By specifying the HEADLINE clause, the headers contained in it are not included in the result set.
  2. The header row does not have to be the first row of a sheet (all preceding rows before the header row are then ignored).
  3. Not all columns are needed in ascending order anymore; instead, columns can be selectively chosen by name.
trackid name composer
------- ---- -------- 
1       For Those About To Rock (We Salute You) Angus Young, Malcolm Young, Brian Johnson 
2       Balls to the Wall null 
3       Fast As a Shark F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 
4       Restless and Wild F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman 
5       Princess of the Dawn Deaffy & R.A. Smith-Diesel 
6       Put The Finger On You Angus Young, Malcolm Young, Brian Johnson 
7       Let's Get It Up Angus Young, Malcolm Young, Brian Johnson 
8       Inject The Venom Angus Young, Malcolm Young, Brian Johnson

Aliases

As usual, column aliases can be used to give columns different names. This is helpful when additional SQL operators are applied to the column values:

SELECT trackid
       ,BIGINT(milliseconds)/1000/60 minutes
       ,name
       ,composer
FROM "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel/chinook.xlsx"."Track"
COLUMNS(trackid,name,composer,milliseconds)
HEADLINE 1
WHERE rownumber <= 8

This produces:

trackid minutes name composer
------- ------- ---- -------- 
1       5       For Those About To Rock (We Salute You) Angus Young, Malcolm Young, Brian Johnson 
2       5       Balls to the Wall null 
3       3       Fast As a Shark F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 
4       4       Restless and Wild F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman 
5       6       Princess of the Dawn Deaffy & R.A. Smith-Diesel 
6       3       Put The Finger On You Angus Young, Malcolm Young, Brian Johnson 
7       3       Let's Get It Up Angus Young, Malcolm Young, Brian Johnson 
8       3       Inject The Venom Angus Young, Malcolm Young, Brian Johnson

Pseudo-Columns

In addition to the data columns from the Excel document, the ExcelJdbc driver generates an additional pseudo-column "rownumber". We have already used this in the examples to limit the result set. It can also be selected:

SELECT rownumber
       ,name
       ,composer
FROM "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel/chinook.xlsx"."Track"
COLUMNS(trackid,name,composer)
HEADLINE 1
WHERE rownumber <= 8

rownumber name composer
--------- ---- -------- 
1         For Those About To Rock (We Salute You) Angus Young, Malcolm Young, Brian Johnson 
2         Balls to the Wall null 
3         Fast As a Shark F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 
4         Restless and Wild F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman 
5         Princess of the Dawn Deaffy & R.A. Smith-Diesel 
6         Put The Finger On You Angus Young, Malcolm Young, Brian Johnson 
7         Let's Get It Up Angus Young, Malcolm Young, Brian Johnson 
8         Inject The Venom Angus Young, Malcolm Young, Brian Johnson

System Tables

The ExcelJdbc driver supports directories as system tables. This allows searching for files in a directory in a loop query of a datasqill module, which can then be loaded.

The following query uses the "files()" function as a system table and its columns "directory" and "filename".

SELECT directory, filename
    FROM "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel".files()
    WHERE filename = 'chinook.xlsx' or filename = 'ExcelValidate.json'
;

As a result, the searched files are returned:

directory filename
--------- -------- 
exceljdbc/src/test/resources/de/softquadrat/jdbc/excel chinook.xlsx 
exceljdbc/src/test/resources/de/softquadrat/jdbc/excel ExcelValidate.json

Notes

Data Types

The ExcelJdbc driver returns all results as text. Conversion to numbers or date fields must be performed in a subsequent processing step.

Date Values

Dates are stored in Excel as whole numbers. They represent the difference of a date to December 31, 1899. Conversion to a date value must be performed in the further processing steps.

Null Values

If a cell in the Excel sheet is empty, the ExcelJdbc driver returns "null" as the value.

SELECT column1
    , column2
    , column3
    FROM "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel/chinook.xlsx"."Track"
    COLUMNS(column1, column2, column3)
    WHERE column1 = '0'

column1 column2 column3
------- ------- ------- 
0               null

It can be seen here that a careful distinction must be made between cells with empty string (column2) and empty cells (column3).