Write to Excel

Das Modul "Write to Excel" wird über eine SQL-Abfrage gesteuert, die der datasqill Entwickler definiert. Das Modul liest Daten mithilfe dieser Abfrage aus den Quelltabellen und schreibt sie in ein Excel File.

Name Bedeutung
Modul Write to Excel
Modulklasse DsModWriteExcel
Typ Java
Zweck Basierend auf der angegebenen SQL-Abfrage sollen die Daten in ein Excel File geschrieben werden
Transformationscode SQL Abfrage
Quellen Quelltabellen in einer Datenbank, Excel Template File
Ziele Excel File

Beschreibung

Folgendes Beispiel zeigt, wie man mithilfe des Moduls einen Export to Flat File aufbauen kann.

Zunächst die SQL-Abfrage definieren:

SELECT c_custkey AS key
     , c_name
     , c_firstname
  FROM staging.customer

(Die Variable load_date in den Kopfzeilen setzen wir in einer LoopQuery.)

Als zweitens muss man das Excel Template vorbereiten: write_excel_file_template

Die farblich markierten Variablen haben folgende Bedeutung:

Name Quelle Bedeutung Optional
${load_date} Loop Query Die Spalten aus der Loop Query können in Excel benutzt werden J
${TemplateRow} - Diese Variable legt fest, ab welcher Zeile die Daten aus der Query in das Excel eingesetzt werden N
${key}, ${c_name}, ${c_firstname} Main Query Diese Variablen entsprechen den Spaltennamen aus SQL-Abfrage N

Wie im Beispiel zu erkennen ist, können die Spaltennamen des Excels mit oder ohne Angaben von Aliasen in einer SQL-Abfrage eingegeben werden.

Die Beschriftung für die Spalten kann frei definiert werden.

Datenquellen

Die Quellen des Moduls sind:

  • Excel Template File
  • Tabellen, die über die vom datasqill Entwickler definierte Abfrage und die Loop-Query gelesen werden. Für diese Tabellen muss der datasqill Laufzeit-Benutzer Leserechte besitzen.

Das Excel Template File sowie alle in der SQL-Abfrage verwendeten Quelltabellen müssen über die datasqill GUI im grafischen Datenmodell mit dem Eingang des Moduls verbunden werden.

Datenziel

Das Ziel des Moduls ist ein einzelnes Excel File zu erstellen. Für den Zielordner dieses Files muss der datasqill Laufzeit-Benutzer Schreibrechte besitzen. Das Ziel Excel File muss mithilfe der datasqill GUI im grafischen Datenmodell mit dem Ausgang der Transformation verbunden werden.

Attribute

Im Auslieferungszustand bietet das Modul folgende Attribute in der GUI für den datasqill Entwickler an:

Name Typ Bedeutung
Empty Directory Boolean Das Modul löscht alle Files in dem Zielordner vor dem Schreiben, wenn dieses Attribute auf 'Y' gesetzt ist
Excel Sheet Char Der Name des Excel Blattes, das geschrieben wird
Loop Query SQL Schleifenabfrage, um die Ergebnisse in der Hauptabfrage oder im Filenamen zu verwenden
Suppress Empty Sheet BOOLEAN Wenn aktiv, dann wird die Excel Liste ohne Daten nicht generiert

Erstes Beispiel

Für das Beispiel wird folgendes Excel Template verwendet: Beispiel Excel Template

Das Beispiel zeigt, wie man ein Excel File generiert und mithilfe einer Loop Query ein Zeitstempel für den Filenamen setzt (die Queries sind für Postgres DB geschrieben)

Loop Query:

SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') load_date

Hauptabfrage:

SELECT c_custkey AS key
     , c_name
     , c_firstname
  FROM stage.customer

write_excel_file_action

Die Spalte "load_date" aus der Loop Query im Form einer Variable ${load_date} kann man sowohl im Ziel Excel File als auch im Filename benutzen.

write_excel_file_transformation

Das Endfile wird dann z.B. für das Datum 09.11.2021 so heißen: Excel_with_data_2021-11-09.xlsx

Datentypen & Formate

Das Modul "Write to Excel" unterstützt die folgenden Datentypen

  • Zahlen (ganze Zahlen oder Dezimalzahlen)
  • Daten (Date, Time oder Timestamp)
  • Text

Wird für die Zielzellen als Formatierung in Excel Standardformatierung eingestellt, werden die Daten als Text formatiert. Das führt bei Datumsangaben zur Anzeige von ganzen Zahlen (Excel zählt dabei Tage seit 1.1.1900) und bei Zahlen können Hinweise in Form eines gelben Ausrufezeichen erscheinen, das auf eine als Text formatierte Zelle hinweist.

Um Zahlen oder Datums- und Uhrzeitangaben im gewünschten Format zu formatieren, empfiehlt es sich, diese Formatierungen bereits in der Vorlage anzuwenden.

Diese Vorlage wurde entsprechend formatiert:

format_template

Die Zellen haben folgende Formate:

  • A3: Standard
  • B3: Zahlen (ohne Nachkommastellen mit Tausendertrennzeichen)
  • C3: Datum (*Mittwoch, 14. März 2012)
  • D3: Uhrzeit (*13:30:55)
  • E3: Benutzerdefiniert (TT.MM.JJ hh:mm:ss)
  • F3: Datum (14.3.2012 Deutsch Gregorianisch)
  • G3: Benutzerdefiniert (hh:mm:ss,00)
  • H3: Benutzerdefiniert (h:mm:ss AM/PM)

Als Ergebnis erhält man folgende Excel Datei:

format_result