CSV und Excel über SQLite Datenbank zusammenführen

In diesem Beispiel werden wir aufzeigen wie Daten aus einer CSV Textdatei und einer Excel Liste zu einem „Großen" Export Textdatei Flat File Report zusammengeführt werden können.

Hinweis: Mit Version 4.1.4 ist die hier beschriebene temporären SQLite Datenbank nicht mehr notwendig! Das Beispiel kann komplett und sehr einfach auf Basis von InMemory Adapter Tabellen umgestellt werden. Am Ende des Artikels finden Sie dazu eine Beschreibung (inkl. Tutorial Video, siehe weiter unten) wie unterschiedliche Datenquellen (CSV, Excel und weitere) mit dem InMemory Adapter zu einer Ausgabe zusammengeführt bzw. angereichert werden können.

Allgemeine Infos zur Excel - CSV Zusammenführung (Datenmigration)

Oft kommt es vor, dass Daten aus verschiedenen (Daten) Quellen vorliegen und diese in einer konsolidierten Liste (Export Datei) zusammengeführt werden sollen. Der Einfachheit verwenden wir hier nur zwei unterschiedliche Datenquellen CSV und Excel. Nach dem gleichen Schema können Sie aber beliebig viele und andere Datenquellen miteinander kombinieren.

Wir benötigen für jede Datenquelle eine separate FlowHeater Definition um die Daten zuerst in eine temporäre SQLite DB zwischen zu speichern. Sie fragen sich vielleicht warum SQLite? Es würde sich theoretisch auch jede andere Datenbank dafür eignen. SQLite hat den Vorteil, dass Sie dafür weiter keine anderen Datenbanktools benötigen. Der FlowHeater bringt für SQLite Datenbanken bereits alles mit was wir benötigen.

CSV Textdatei in SQLite Datenbank importieren

Wir starten mit dem CSV Import in die SQLite Datenbank. Erzeugen Sie dafür eine neue FlowHeater Definition und wählen als Adapter für die READ Seite den TextFile Adapter und auf der WRITE Seite den SQLite Adapter aus. Konfigurieren Sie den READ Adapter so dass die CSV Textdatei „text-daten.csv" gelesen werden kann und schließen Sie den Configurator für die CSV Datei.

CSV Feldnamen nach SQLite übernehmen

Gehen Sie nun mit der Maus im Designer über den SQLite Adapter der WRITE Seite (noch nicht den Configurator öffnen) und wählen mit einem Rechtsklick die Option "READ Felder übernehmen" aus.

Öffnen Sie nun den Configurator für den SQLite Adapter auf der WRITE Seite. Geben Sie für den Datenbanknamen "Adressen.sqlite" an und aktivieren die Optionen:

• Datenbank anlegen falls nicht existiert
• Tabelle anlegen falls nicht existiert
• Daten anfügen (Insert)
• Tabelle vor dem Import löschen.

SQLite Tabellenname und PrimaryKey

Wechseln Sie anschließend auf den Reiter "Felder / Datentypen" und geben für den Tabellennamen falls nicht existiert den Namen "t_Adressen" ein. Dies ist in diesem Fall notwendig, da unsere SQLite Datenbank noch nicht existiert und wir keine Tabelle auswählen können.
Jetzt müssen wir dem SQLite Adapter mitteilen welches Feld unserer CSV Datei als PrimaryKey angelegt werden soll. Klicken Sie hierzu in der Feldliste das Feld ID an, stellen den FlowHeater Datentypen auf Integer und aktivieren Sie für dieses Feld die Option "PrimaryKey". Beenden Sie den SQLite Adapter Configurator Dialog mit OK.

Jetzt müssen wir die Felder der CSV Textdatei (READ Seite) noch mit den Felder der SQLite Datenbank (WRITE Seite) verbinden. Anschließend können wir die CSV Datei in unsere SQLite Datenbank importieren. Führen Sie dazu die Definition einmalig aus und speichern diese unter den Namen "Import-CSV-nach-SQLite.fhd" ab.

Excel Arbeitsblatt in SQLite Datenbank importieren

SQLite Tabellenschema anpassenWir legen eine weitere neue FlowHeater Definition an und wählen hierfür die Adapter Excel für die READ Seite und SQlite für die WRITE Seite aus. Anschließend konfigurieren wir den Excel Adapter so dass die Excel Arbeitsmappe "Excel-Daten.xls" gelesen werden kann.
Anschließend öffnen wir den Configurator für den SQLite Adapter auf der READ Seite und wählen unsere oben im ersten Schritt erzeugte SQLite Datenbank "adressen.sqlite" aus. Aktivieren Sie hier nur die Optionen

• Fehlende Felder automatisch an Tabelle anfügen
• Daten aktualisieren

Und wechseln anschließend auf den Reiter "Felder / Datentypen". Hier müssen Sie lediglich die Tabelle t_Adressen aus der SQLite Tabellenauflistung auswählen und einmalig den Button "Felder einlesen" klicken um das Tabellenschema zu laden. Verlassen Sie anschließend den SQLite Configurator Dialog mit OK.

Verbinden Sie nun das ID Feld der Excel Datei (READ Seite) mit dem ID Feld des SQLite Adapters (WRITE Seite). Für die 2 zusätzlichen Felder in der Excel Liste haben wird momentan noch keine passenden Felder in unserer SQLite Datenbank Tabelle. Da wir die Option "Fehlende Felder automatisch an Tabelle anfügen" aktiviert haben können wir diese Felder einfach in einen leeren Bereich des SQLite Adapters im Designer ziehen. Der SQLite Adapter fügt diese Felder dann automatisch an die Tabelle an und passt das Tabellenschema an die neuen Struktur an.

Wenn Sie jetzt die Definition ausführen wird die SQLite Tabelle um 2 Felder erweitert und der FlowHeater aktualisiert die Daten der Excel Datei anhand des Feldes ID (PrimaryKey) in der SQLite Datenbank. Speichern Sie die Definition unter den Namen "Import-Excel-nach-SQLite.fhd" ab.

SQLite Tabelle in FlatFile Report exportieren

Mit den zwei Definitionen haben wir die Daten der zwei Datenquellen (CSV und Excel) zu einer zusammengeführt. Jetzt müssen wir diese Daten noch als FlatFile Report exportieren. Legen Sie dazu ein neue Definition an und wählen auf der READ Seite den SQLite Adapter und auf der WRITE Seite den Textfile Adapter aus.
Öffnen Sie den SQLite Konfigurator und geben für die Datenbank die oben erzeugte SQLite Datenbank "adressen.sqlite" an. Wechseln Sie anschließend auf den Reiter "Felder / Datentypen" und wählen die Tabelle "t_Adressen" aus, lesen das Tabellenschema über den Button „Felder einlesen" ein und beenden den Dialog mit OK.

Öffnen Sie nun den TextFile Adapter Configurator der WRITE Seite. Geben Sie einen Dateinamen an und stellen Sie die Spaltenabgrenzung vom Standard "Mit Trennzeichen" auf "Feste Breite" um. Wechseln Sie nun auf den Reiter "Felder / Datentypen" und klicken auf den Button „Feldnamen von READ Adapter kopieren". Anschließend beenden Sie den Configurator Dialog mit OK. Jetzt müssen wir noch die Felder der SQLite Datenbank (READ Seite) mit den Feldern des TextFile Adapter (WRITE Seite) verbunden und können anschließend die Export starten. Speichern Sie die Definition unter den Namen "Export-SQLite-FlatFile.fhd" ab.

Automatisieren über das Batch Modul

Über das Batch Modul (FHBatch.exe) und folgenden Batch CMD Skript können Sie diese 3 Definitionen/Schritte automatisiert und ggf. auch zeitgesteuert über den Windows Aufgabenplaner ausführen.

Umsetzung auf Basis von InMemory Adapter Tabellen

Im obigen Beispiel wurde beschrieben, dass für die einzelnen Schritte jeweils eine separate FlowHeater Definition erstellt werden muss. Für die Umstellung auf InMemory Adapter Tabellen sowie Nutzung von mehreren Verarbeitungsschritten in einer Definition, wird nur noch eine FlowHeater Definition benötigt.

Erster Verarbeitungsschritt

Daten zusammenführen - Felder übernehmenLegen wir zunächst eine neue Definition an und wählen hier den TextFile Adapter auf der READ und den InMemory Adapter auf der WRITE Seite aus um die Daten die im CSV Format vorliegen in eine „temporäre“ InMemory Adapter Tabelle einzulesen. Über den TextFile Adapter Konfigurator wählen wir die CSV Datei aus, die wir einlesen möchten. Nun können wir die Felder der READ Seite entweder einzeln per Drag&Drop auf die WRITE Seite ziehen oder aber per Rechtsklick auf den InMemory Adapter der WRITE Seite und im Kontextmenü „Einstellungen übernehmen von“ verwenden um anschließend alle Felder der READ Seite in den Adapter zu übernehmen.

Beachten Sie, dass wir noch keine Einstellungen im InMemory Adapter vorgenommen haben. Das ist aktuell noch nicht notwendig.

Jetzt sollten die Felder von der READ mit der WRITE Seite verbunden sein, wir können nun zum Test die Definition starten und uns das Ergebnis ansehen. Wir sehen, dass eine Tabelle mit dem aktuellen Inhalt der CSV Textdatei angezeigt wird. Beachten Sie, dass diese Daten „nur“ im Hauptspeicher vorgehalten werden!

Damit ist natürlich noch nicht viel anzufangen, machen wir weiter.

Zweiter Verarbeitungsschritt

Daten zusammenführen - weiterer VerarbeitungsschrittNun fügen wir dieser Definition einen weiteren Verarbeitungsschritt hinzu, mit dem wir eine weitere Datenquelle (Excel) einlesen möchten bzw. die bereits vorhandenen CSV Daten anzureichern.

Gehen wir über „Menü -> Bearbeiten -> Verarbeitungsschritte“ und fügen der Definition einen weiteren Verarbeitungsschritt hinzu und wählen für die READ Seite den Excel Adapter und auf der WRITE Seite den InMemory Adapter aus.

Konfigurieren wir nun den Excel Adapter so, dass die gewünschten Daten vom passenden Excel Arbeitsblatt gelesen werden. Anschließend können wir die zusätzlichen Felder wieder per Drag&Drop auf die WRITE Seite des InMemory Adapters ziehen.

Damit der InMemory Adapter weiß, anhand welcher Felder er evtl. bereits vorhandene Datensätze aktualisieren soll müssen wir hier einige Einstellungen vornehmen. Hinweis: Mit den Standardeinstellungen würden die Daten lediglich an die vorhandene „temporärer“ Tabelle angefügt werden!

Daten zusammenführen - nur UpdatesÖffnen wir den Konfigurator des InMemory Adapter der WRITE Seite und wählen als erstes aus, dass der InMemory Adapter nur „UPDATES“ durchführen soll. Hierzu aktivieren wir die Option „Daten aktualisieren (Update)“ auf dem Reiter „Allgemein“. Damit keine Datensätze angefügt werden, die evtl. im Excel Arbeitsblatt vorhanden sind und in der CSV Datei fehlen, deaktivieren wir die Option „Daten hinzufügen (Insert)“. So werden nur bereits in der „temporären“ InMemory Tabelle vorhandene Datensätze aktualisiert, alles andere wird ignoriert.

Zusätzlich müssen wir noch ein Schlüsselfeld (oder auch mehrere) festlegen, anhand wir die „UPDATES“ durchführen möchten. Wechseln wir hierzu auf den Reiter „Felder / Datentypen“ und markieren das Feld „ID“. Hier wählen wir die Option „Als Schlüsselfeld für Aktualisierungen verwenden“ aus. So teilen wir dem InMemory Adapter mit, dass wir anhand dieses Feldes bereits vorhandene Datensätze aktualisieren möchten.

Achten Sie darauf, dass das Schlüsselfeld in diesem Verarbeitungsschritt auch von der READ mit der WRITE Seite verbunden ist. Nur so kann der InMemory Adapter die Excel Daten zu den bereits eingelesene CSV Daten zuordnen.

Daten zusammenführen - FehlersucheFühren wir nun die Definition aus und schauen uns das Ergebnis an. Es werden nun 2 Tabellen geöffnet, einmal die CSV Daten des ersten Verarbeitungsschrittes sowie die zusätzlichen Excel Daten aus dem zweiten Verarbeitungsschritt. Allerdings sind aktuell im zweiten Verarbeitungsschritt keine Daten vorhanden und der Insert bzw. Update Zähler steht auf 0 Datensätze, wie kann das sein?

Das Problem ist schnell erklärt. Konfigurieren wir dazu den InMemory Adapter so, dass er sowohl INSERTS als auch UPDATES durchführen soll und führen die Definition erneut aus. Wir sehen, dass der InMemory Adapter des zweiten Verarbeitungsschrittes ebenfalls die Daten an die Tabelle angefügt hat. Wenn wir uns die Daten des Schlüsselfeldes ansehen können wir auch den Grund erkennen. Die Daten aus der CSV Datei sind als Ganzzahl vorhanden und die Daten aus dem Excel Arbeitsblatt sind mit Nachkommastellen vorhanden. Wir können das Problem schnell lösen, indem wir den FlowHeater Datentyp für das Schlüsselfeld „ID“ im InMemory Adapter auf „Integer“ (=Ganzzahl) stellen. Wenn wir nun die Definition erneut ausführen sieht das Ergebnis schon besser aus.

Dritter Verarbeitungsschritt

Jetzt ist der Inhalt der „temporären“ InMemory Adapter Tabelle so wie wir ihn gerne hätten. Nun soll er auch wieder irgendwohin geschrieben bzw. ausgegeben werden. Der InMemory Adapter kann natürlich auch auf der READ Seite verwendet werden. Fügen wir hierzu über „Menü -> Bearbeiten -> Verarbeitungsschritte“ einen weiteren Verarbeitungsschritt hinzu und wählen für die READ Seite den InMemory Adapter sowie für die WRITE Seite den TextFile Adapter aus um die so zusammengeführten bzw. angereicherten Daten wieder in eine Textdatei auszugeben. Hier müssen nur noch die Felder von der READ Seite mit der WRITE Seite verbunden werden. Anschließend kann die Definition erneut ausgeführt werden, die am Schluss eine angereicherte Textdatei mit den Daten aus der CSV Datei sowie dem Excel Arbeitsblatt enthält.

Hinweis: Auf der READ Seite kann im InMemory Adapter die Ausgabe optional über eine WHERE Klausel eingeschränkt werden oder aber anhand spezifischer Felder anders sortiert ausgegeben werden. Wird hier nichts weiter angegeben, wird die „temporäre“ Tabelle in der Reihenfolge ausgegeben wie sie auch eingelesen wurde.

Beispiel Tutorial Video

Im Beispiel Video werden die drei Verarbeitungsschritte - Einlesen der CSV Datei, hinzufügen der Excel Daten und anschließendes ausgeben der zusammengeführten/angereicherten Textdatei veranschaulicht. Es wird auch auf die oben beschriebene Problematik mit dem INSERT / UPDATE des zweiten Verarbeitungsschrittes näher eingegangen.