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 Datenbank wird eine Dummyverbindung 'jdbc:xlsx:' verwendet
  • als Schema wird eine Excel-Datei angegeben
  • als Tabelle wird der Blattname angegeben, alternativ kann auch ein numerischer Index verwendet werden (:1 für das erste Blatt, :2 für das zweite Blatt, usw.)
  • 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.

Alternativ kann auch ein numerischer Index verwendet werden, um auf ein Blatt zuzugreifen:

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

Hier wird :1 verwendet, um auf das erste Blatt in der Excel-Datei zuzugreifen. :2 würde auf das zweite Blatt verweisen, :3 auf das dritte, usw. Die Reihenfolge entspricht der Reihenfolge, in der die Blätter in der Excel-Datei definiert sind.

Die Spalten werden nacheinander aus dem Blatt selektiert. Dabei werden ihnen die in der COLUMNS-Klausel vorgegebenen Spaltennamen gegeben. Enthält eine Zeile weniger Zellen als in COLUMNS angegeben, werden die überzähligen Spalten als NULL geliefert.

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.
  4. Fehlt ein in der COLUMNS-Klausel angegebener Spaltenname in der Überschriftenzeile, wird für diese Spalte NULL zurückgegeben. Die Abfrage bricht nicht ab.
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 Excel-Dokument generiert der ExcelJDBC-Treiber die zusätzliche Pseudospalte "rownumber". Die Spalte "rownumber" 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", "filename" und "filedate".

SELECT directory, filename, filedate
    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 filedate
--------- -------- --------
csvjdbc/src/test/resources/de/softquadrat/jdbc/csv CsvValidate.json 2026-01-17 11:22:33.345
csvjdbc/src/test/resources/de/softquadrat/jdbc/csv x.csv 2024-11-27 11:22:33.345

Anmerkungen

Datentypen

Der ExcelJDBC-Treiber liefert alle Zelleninhalte als Text. Zahlen werden mit BIGINT() oder DOUBLE() umgewandelt. Datum und Uhrzeit werden mit DATE() und TIMESTAMP() umgewandelt (siehe unten).

Datumsangaben

Excel speichert Datum und Uhrzeit als fortlaufende Zahl (Serial): der ganzzahlige Teil ist der Kalendertag, der Nachkommateil die Uhrzeit. Serial 1 entspricht dem 1.1.1900.

Excel behandelt 1900 fälschlich als Schaltjahr (Lotus-1-2-3-Kompatibilität). Serial 59 und 60 sind deshalb beide der 28.2.1900, Serial 61 ist der 1.3.1900.

DATE() und TIMESTAMP() ohne 2. Formatparameter erkennen solche Zahlen (Spaltenwerte und Literale) und rechnen sie um. Texte, die keine Zahl sind, werden wie beim SQL-Parser als ISO-Datum bzw. ISO-Zeitstempel gelesen.

SELECT hiredate
       , DATE(hiredate) hiredate_as_date
       , TIMESTAMP(hiredate) hiredate_as_ts
       , DATE('2024-03-27') iso_date
FROM "exceljdbc/src/test/resources/de/softquadrat/jdbc/excel/chinook.xlsx"."Employee"
COLUMNS(hiredate)
HEADLINE 1
WHERE rownumber = 1

hiredate hiredate_as_date hiredate_as_ts iso_date
-------- ---------------- -------------- --------
37482    2002-08-14       2002-08-14 00:00:00.0 2024-03-27

Ein Nachkommateil wird bei DATE() ignoriert und bei TIMESTAMP() als Uhrzeit verwendet: TIMESTAMP('37482.75') ergibt 2002-08-14 18:00:00.

Eine reine Zahl ohne Bindestriche ist immer eine Serial, nicht ein ISO-Jahr: DATE('2026') ist Serial 2026 und liefert '1905-07-18' und nicht den 1.1.2026. DATE('2026-02') und DATE('2026-02-18') werden dagegen als ISO Datum interpretiert.

Mit einem zweiten Parameter mit Formatangaben gelten dieselben Muster wie bei den SQL-Funktionen; die Serial-Umrechnung findet dann nicht statt.

Null-Werte

Ist eine Zelle im Excel-Blatt leer, liefert der ExcelJDBC-Treiber "null" als Wert zurück. Dasselbe gilt, wenn mit HEADLINE ein in COLUMNS genannter Spaltenname in der Überschriftenzeile fehlt.

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.