Excel-Erweiterung

Die Erweiterung excel stellt Funktionen bereit, um Zahlen nach den Formatierungsregeln von Excel zu formatieren (über eine Hülle um die i18npool-Bibliothek) sowie Excel-Dateien (.xlsx) zu lesen und zu schreiben. Dateien im Format .xls werden jedoch nicht unterstützt.

Installation und Laden

Die Erweiterung excel wird beim ersten Einsatz transparent aus dem offiziellen Erweiterungs-Repository automatisch geladen. Wenn Sie sie manuell installieren und laden möchten, führen Sie aus:

INSTALL excel;
LOAD excel;

Excel-Skalarfunktionen

Funktion	Beschreibung
excel_text(number, format_string)	Formatiert die angegebene number nach den Regeln in format_string
text(number, format_string)	Alias für excel_text

Beispiele

SELECT excel_text(1_234_567.897, 'h:mm AM/PM') AS timestamp;

timestamp
9:31 PM

SELECT excel_text(1_234_567.897, 'h AM/PM') AS timestamp;

timestamp
9 PM

XLSX-Dateien lesen

Eine .xlsx-Datei zu lesen ist so einfach wie ein unmittelbares SELECT daraus, z. B.:

SELECT *
FROM 'test.xlsx';

a	b
1.0	2.0
3.0	4.0

Wenn Sie zusätzliche Optionen für den Import setzen möchten, verwenden Sie stattdessen die Funktion read_xlsx. Die folgenden benannten Parameter werden unterstützt.

Option	Type	Default	Description
header	BOOLEAN	automatisch ermittelt	Ob die erste Zeile als Namen der resultierenden Spalten behandelt werden soll.
sheet	VARCHAR	automatisch ermittelt	Der Name des Blatts in der xlsx-Datei, das gelesen werden soll. Standard ist das erste Blatt.
all_varchar	BOOLEAN	false	Ob alle Zellen als VARCHAR gelesen werden sollen.
ignore_errors	BOOLEAN	false	Ob Fehler ignoriert und Zellen, die nicht in den ermittelten Spaltentyp gewandelt werden können, stillschweigend durch NULL ersetzt werden sollen.
range	VARCHAR	automatisch ermittelt	Der zu lesende Zellbereich in Tabellenkalkulationsnotation. A1:B2 liest beispielsweise die Zellen von A1 bis B2. Wird nichts angegeben, wird der Bereich als rechteckige Region zwischen der ersten Zeile aufeinanderfolgender nicht leerer Zellen und der ersten leeren Zeile über dieselben Spalten ermittelt.
stop_at_empty	BOOLEAN	automatisch ermittelt	Ob das Lesen der Datei bei einer leeren Zeile abgebrochen werden soll. Wenn eine explizite Option range angegeben ist, ist der Standard false, sonst true.
empty_as_varchar	BOOLEAN	false	Ob leere Zellen beim automatischen Ermitteln der Spaltentypen als VARCHAR statt als DOUBLE behandelt werden sollen.

SELECT *
FROM read_xlsx('test.xlsx', header = true);

a	b
1.0	2.0
3.0	4.0

Alternativ kann die Anweisung COPY mit der Formatoption XLSX verwendet werden, um eine Excel-Datei in eine bestehende Tabelle zu importieren. In diesem Fall werden die Typen der Spalten in der Zieltabelle genutzt, um die Zelltypen der Excel-Datei umzuwandeln.

CREATE TABLE test (a DOUBLE, b DOUBLE);
COPY test FROM 'test.xlsx' WITH (FORMAT xlsx, HEADER);
SELECT * FROM test;

Typ- und Bereichsermittlung

Excel speichert in Zellen im Wesentlichen nur Zahlen oder Strings und erzwingt nicht, dass alle Zellen einer Spalte denselben Typ haben. Die Erweiterung excel muss daher beim Import eines Excel-Blatts die Spaltentypen „erraten“ und festlegen. Fast alle Spalten werden als DOUBLE oder VARCHAR ermittelt, mit einigen Einschränkungen:

• Die Typen TIMESTAMP, TIME, DATE und BOOLEAN werden nach Möglichkeit anhand des auf die Zelle angewendeten Formats ermittelt.
• Textzellen mit TRUE und FALSE werden als BOOLEAN erkannt.
• Leere Zellen gelten standardmäßig als DOUBLE, sofern empty_as_varchar nicht auf true gesetzt ist; dann werden sie als VARCHAR typisiert.

Wenn die Option all_varchar auf true gesetzt ist, gilt nichts davon und alle Zellen werden als VARCHAR gelesen.

Wenn keine Typen explizit angegeben sind (z. B. wenn Sie die Funktion read_xlsx statt COPY TO ... FROM '⟨file⟩.xlsx'{:.language-sql .highlight} verwenden), werden die Typen der resultierenden Spalten anhand der ersten „Datenzeile“ im Blatt ermittelt, und zwar:

• Wenn kein expliziter Bereich angegeben ist
  • Die erste Zeile nach der Kopfzeile, wenn eine Kopfzeile gefunden oder durch die Option header erzwungen wird
  • Die erste nicht leere Zeile im Blatt, wenn keine Kopfzeile gefunden oder erzwungen wird
• Wenn ein expliziter Bereich angegeben ist
  • Die zweite Zeile des Bereichs, wenn in der ersten Zeile eine Kopfzeile gefunden oder durch die Option header erzwungen wird
  • Die erste Zeile des Bereichs, wenn keine Kopfzeile gefunden oder erzwungen wird

Das kann Probleme verursachen, wenn die erste „Datenzeile“ nicht repräsentativ für den Rest des Blatts ist (z. B. weil sie leere Zellen enthält). In dem Fall können die Optionen ignore_errors oder empty_as_varchar Abhilfe schaffen.

Wenn dagegen die Syntax COPY TO ... FROM '⟨file⟩.xlsx'{:.language-sql .highlight} verwendet wird, findet keine Typermittlung statt. Die Typen der resultierenden Spalten ergeben sich aus den Spaltentypen der Tabelle, in die kopiert wird. Alle Zellen werden einfach durch Cast von DOUBLE oder VARCHAR in den Zielspaltentyp umgewandelt.

XLSX-Dateien schreiben

Das Schreiben von .xlsx-Dateien wird über die Anweisung COPY mit XLSX als Format unterstützt. Die folgenden zusätzlichen Parameter werden unterstützt.

Option	Type	Default	Description
header	BOOLEAN	false	Ob die Spaltennamen als erste Zeile im Blatt geschrieben werden sollen
sheet	VARCHAR	Sheet1	Der Name des Blatts in der xlsx-Datei, das geschrieben werden soll.
sheet_row_limit	INTEGER	1048576	Die maximale Anzahl Zeilen in einem Blatt. Wird dieses Limit überschritten, wird ein Fehler ausgelöst.

Warnung Viele Werkzeuge unterstützen höchstens 1.048.576 Zeilen in einem Blatt. Eine Erhöhung von sheet_row_limit kann dazu führen, dass andere Software die Datei nicht mehr lesen kann.

Diese werden der Anweisung COPY nach dem FORMAT als Optionen übergeben, z. B.:

CREATE TABLE test AS
    SELECT *
    FROM (VALUES (1, 2), (3, 4)) AS t(a, b);
COPY test TO 'test.xlsx' WITH (FORMAT xlsx, HEADER true);

Typumwandlungen

XLSX-Dateien unterstützen im Wesentlichen nur das Speichern von Zahlen oder Strings – das Äquivalent zu VARCHAR und DOUBLE. Beim Schreiben von XLSX-Dateien gelten daher die folgenden Typumwandlungen.

• Numerische Typen werden beim Schreiben in eine XLSX-Datei nach DOUBLE gecastet.
• Zeitliche Typen (TIMESTAMP, DATE, TIME usw.) werden in Excel-„Seriennummern“ umgewandelt, also die Anzahl der Tage seit 1900-01-01 für Daten und den Tagesbruchteil für Uhrzeiten. Sie erhalten dann ein „Zahlenformat“, damit sie in Excel als Datum oder Uhrzeit erscheinen.
• TIMESTAMP_TZ und TIME_TZ werden nach UTC-TIMESTAMP bzw. TIME gecastet; die Zeitzoneninformation geht verloren.
• BOOLEAN-Werte werden in 1 und 0 umgewandelt und mit einem „Zahlenformat“ versehen, damit sie in Excel als TRUE und FALSE erscheinen.
• Alle anderen Typen werden nach VARCHAR gecastet und als Textzellen geschrieben.