ExcelJdbc Treiber

Einleitung

Mit Hilfe des ExcelJdbc-Treibers können Daten aus einer Excel-Datei mit Hilfe einer SQL-Abfrage wie aus einer Datenbank gelesen werden.

Mit der SQL-Abfrage werden die Daten eines Excel-Blatts wie eine Tabelle angesprochen. Es gelten die folgenden Konventionen

  • als Schema wird eine Excel-Datei angegeben
  • als Tabelle wird der Blattname angegeben
  • die Zeilen und Spalten werden aus dem Blatt selektiert

Die folgende Excel-Datei kann man zum Beispiel mit Hilfe des Treibers einlesen:

Excel-Datei

Eine SQL-Abfrage über den ExcelJdbc-Treiber sieht dann so aus:

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

Dabei wird der Dateiname "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel/chinook.xlsx" als Schema angegeben und "Track" ist das Blatt, das als Tabelle angesprochen wird.

Die Spalten werden nacheinander aus dem Blatt selektiert. Dabei werden ihnen die in der COLUMN-Klausel vorgegebenen Spaltennamen gegeben.

Das Ergebnis sieht dann so aus:

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

Überschriften

Wenn das Excel-Blatt Überschriften hat (wie in unserem Beispiel-Excel), können diese zur Identifizierung der Spaltennamen verwendet werden.

Dazu wird die HEADLINE-Klausel verwendet:

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

Die Angabe der Zeilennummer mit den Überschriften (hinter HEADLINE) hat folgende Auswirkungen:

  1. Durch Angabe der HEADLINE-Klausel werden die darin enthalten Überschriften nicht in die Ergebnismenge übernommen.
  2. Die Zeile mit den Überschriften muss nicht die erste Zeile eines Blattes sein (alle vorausgehenden Zeilen vor der Überschriftenzeile werden dann irgnoriert).
  3. Es werden nicht mehr alle Spalten in aufsteigender Reihenfolge benötigt, sondern es können gezielt Spalten per Namen ausgewählt werden.
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

Aliase

Wie gewohnt können Spaltenaliase vergeben werden, um den Spalten andere Namen zu geben. Das ist dann hilfreich, wenn weitere SQL Operatoren auf die Spaltenwerte angewendet werden:

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

Das liefert dann:

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-Spalten

Neben den Datenspalten aus dem Json-Dokument generiert der JsonJdbc-Treiber eine zusätzliche Pseudospalte "rownumber". Diese haben wir auch schon in den Beispielen benutzt, um die Ergebnismenge zu begrenzen. Man kann sie auch selektieren:

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

Systemtabellen

Der ExcelJdbc-Treiber unterstützt Verzeichnisse als Systemtabellen. Das erlaubt in einer Loop-Query eines datasqill Moduls nach Dateien in einem Verzeichnis zu suchen, die dann anschließend geladen werden können.

Die folgende Abfrage benutzt die Funktion "files()" als Systemtabelle und ihre Spalten "directory" und "filename".

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

Als Ergebnis werden die gesuchten Dateien zurückgeliefert:

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

Anmerkungen

Datentypen

Der ExcelJdbc-Treiber liefert alle Ergebnisse als Text zurück. Eine Umwandlung in Zahlen oder Datumsfelder muss in einem nachfolgenden Arbeitsschritt erfolgen.

Datumsangaben

Datumsangaben werden in Excel als ganze Zahlen gespeichert. Sie geben die Differenz eines Datums zum 31.12.1899 an. Eine Umwandlung in einen Datumswert muss in den weiteren Verarbeitungssschritten durchgeführt werden.

Null-Werte

Ist eine Zelle im Excel-Blatt leer, liefert der ExcelJdbc-Treiber "null" als Wert zurück.

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

Hier ist zu erkennen, dass zwischen Zellen mit leerem String (column2) und leeren Zellen (column3) sorgfältig unterschieden werden muss.