„Let’s Meet-Datenbank“ Beispielumsetzung  Teil 2: Import

Import der Exceldaten des “Let’s Meet”-Datenbankprojekts (LS Beispiellösung Teil 2)

https://oer-informatik.de/lets_meet-beispielloesung_import

https://bildung.social/@oerinformatik/110207682728246503

tl/dr; Dies ist der zweite Teil (Datenimport) einer Beispiellösung für die “Let’s Meet” Datenbank-Lernsituation. Aus der Excel-Datei wird ein SQL-Importscript erstellt, das in einen neuen Docker-Container importiert wird. Dieser Artikel basiert auf Teil 1 der Beispiellösung hier (Modellierung). (Zuletzt geändert am 23.05.2023)

Dieser Artikel ist Teil einer mehrteiligen Serie, in der

• die eigentliche Aufgabestellung das Ausgangsszenario vorstellt und den Datenbank-Export als Tabelle verlinkt,
• im ersten Teil der Beispiellösung das Zielsystem modelliert wird (ER-Modell, Relationenmodell, physisches Modell / DDL),
• im zweiten Teil der Beispiellösung SQL-Befehle für den Import aus der Tabellenkalkulation erstellt werden und
• im dritten Teil der Beispiellösung die Ausgangsdaten über Zeichenkettenfunktionen, Datumsfunktionen, JOINS und Vereinigung von Abfragen schließlich Daten in das neue Zielsystem übernommen werden.

Allen Artikel enden mit einem SQL-Skript, mit dem Daten als Ausgangspunkt für die nächste Etappe genutzt werden können, falls es zu Problemen bei der Eigenentwicklung kam.

Ausgangslage

Die eigentliche Aufgabestellung der “Let’s Meet” Datenbank-Lernsituation kann hier nachgelesen werden. Voraussetzung für den in diesem Teil beschriebenen Import ist eine fertig modellierte Datenbank, die als SQL-DDL-Befehl vorliegt wie es in dem Vorgängerartikel (Teil 1: Modellierung) beschrieben ist. Sämtliche Tabellen lassen sich beispielsweise über die fertigen SQL-Skripte für PostgreSQL (hier) oder MariaDB (hier) erzeugen.

Vorbereitung des DB-Containers

Um den Datenbank-Container zu starten und direkt mit den richtigen Daten zu füllen benötigen wir ein leeres Verzeichnis, in dem lediglich das zuvor erstellte SQL-Skript (siehe Gitlab-Links oben) liegt. In der Bash und Powershell könnte das z.B. so aussehen:

cd ~/Documents
mkdir mariadbdocker
cd mariadbdocker
wget https://gitlab.com/oer-informatik/db-sql/lets-meet-db/-/blob/main/beispielloesung/mariadb/letsmeet_1_struktur_mariadb.sql

docker run --name=letsmeetmaria -d -p 3307:3306 -e MARIADB_USER=dbuser -e MARIADB_PASSWORD=_hier%1auchGutesPWnu+2en -e MARIADB_ROOT_PASSWORD=_hier%1auchGutesPWnu+2en -v ${PWD}:/docker-entrypoint-initdb.d mariadb:latest

docker exec -it letsmeetmaria mariadb -uroot -p

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.9.3-MariaDB-1:10.9.3+maria~ubu2204 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

SHOW DATABASES;
USE letsMeetMigrationScheme;
SHOW TABLES;

SELECT * FROM letsMeetMigrationScheme.tblUrsprung;

cd ~/Documents
mkdir mssqlserverdocker
cd mssqlserverdocker
wget https://gitlab.com/oer-informatik/db-sql/dbms/-/raw/main/mssql/mssql-custom-dockerfile/Dockerfile -OutFile "Dockerfile"
wget https://gitlab.com/oer-informatik/db-sql/dbms/-/raw/main/mssql/mssql-custom-dockerfile/configure-db.sh -OutFile "configure-db.sh"
wget https://gitlab.com/oer-informatik/db-sql/dbms/-/raw/main/mssql/mssql-custom-dockerfile/entrypoint.sh -OutFile "entrypoint.sh"
wget https://gitlab.com/oer-informatik/db-sql/lets-meet-db/-/raw/main/beispielloesung/mssqlserver/letsmeet_1_struktur_mssqlserver.sql -OutFile "letsmeet_1_struktur_mssqlserver.sql"

PS> docker build -t mssql-custom .

PS> docker run --name=letsmeetmssql --hostname=letsmeetmssql -p 3309:1433 -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=_hier%1GutesPWnu+2en -d mssql-custom

docker exec -it letsmeetmssql /opt/mssql-tools/bin/sqlcmd -S localhost -U SA

SELECT * FROM letsmeetdb.letsMeetMigrationScheme.tblUrsprung;
GO

cd ~/Documents
mkdir psqldocker
cd psqldocker
wget https://gitlab.com/oer-informatik/db-sql/lets-meet-db/-/blob/main/beispielloesung/postgresql/letsmeet_1_struktur_ddl_postgre.sql

docker run --name=letsmeetpsql -d -p 3312:5432 -e POSTGRES_PASSWORD=_hier%1GutesPWnu+2en -v ${PWD}:/docker-entrypoint-initdb.d postgres:latest

docker exec -it letsmeetpsql psql -U postgres postgres

psql (15.0 (Debian 15.0-1.pgdg110+1))
Type "help" for help.

postgres=# 

SELECT * FROM letsMeetMigrationScheme.tblUrsprung;

Import in bestehende Struktur

Um die Ausgangsdaten in die SQL-Datenbank importieren zu können, müssen wir die einzelnen Werte aus dieser Excel-Datei importieren. Es gibt eine ganze Reihe Tools, die Excel-Tabellen in alle möglichen DBMS übernehmen - oft scheitern diese jedoch an Umlauten, Whitespaces, der Interpretation von Datumsfeldern, leeren Feldern usw.

Daher muss in jedem Fall am Ende des Imports überprüft werden, ob alle Werte übernommen wurden.

Um sicherzugehen, was importiert wird, kann das SQL-INSERT-Statement per Excel-Formel generiert werden. Excel Formeln können den Inhalt anderer Zellen wiedergeben. Gibt man in der Excel-Zelle I2 beispielsweise die Formel =A2 ein, so wird der Inhalt der Zelle A2 in I2 angezeigt. Zeichenketten werden in Excel mit & verknüpft, zudem können in Anführungszeichen weitere Zeichenketten eingegeben werden: ="1: "&A2&", 2:"&B2 fügt den Inhalt zweier Zellen aneinander.

Ziel soll es sein, aus einer Excel-Zeile ein SQL-INSERT-Statement zu erstellen nach dem Muster:

INSERT INTO tblNamen (vorname, nachname) VALUES("Max","Mustermann");

Besonderes Augenmerk sollte hierbei auf den Anführungszeichen liegen. Ich hatte oben doppelte Anführungszeichen zur Trennung der Werte verwendet ("Max"). In Excel trennen diese jedoch die Zeichenketten. Wir nutzen also für das SQL-Statement neben den doppelten Anführungszeichen zur Abtrennung der Excel-Zeichenketten zusätzlich die einfachen Anführungszeichen zur Abtrennung der Werte des Import-Befehls:

="INSERT INTO tblNamen (vorname, nachname) VALUES('"&A2&"','"&B2&"');"

Natürlich muss vorher überprüft werden, ob in den eingefügten Werten Anführungszeichen vorhanden sind, da sonst das SQL-Statement die Zeichenketten falsch interpretiert.

Damit ist die Grundstruktur der Formel gelegt, um aus einer Excel-Tabelle SQL-Befehle zu generieren. Eine Formel nach diesem Muster müsste also in eine freie Spalte (z.B. I2 in der Beispieltabelle) erstellt werden und dort die Werte und Attributnamen aller Spalten enthalten. Diese Formel müsste dann in alle Zeilen mit Werte kopiert werden. Die so erstellten SQL-Befehle können wiederum kopiert und in einem SQL-Frontend ausgeführt werden.

Prinzipiell gibt es die Möglichkeit, aus jeder Zeile einen einzelnen SQL-Befehl zu erstellen, etwa so:

INSERT INTO tblNamen (vorname, nachname) VALUES("Max","Mustermann");
INSERT INTO tblNamen (vorname, nachname) VALUES("Martina","Mustermann");

Oder alle Werte in einem gemeinsamen Statement als Einzeiler zusammenzufassen:

INSERT INTO tblNamen VALUES("Max","Mustermann"),("Martina","Mustermann");

Während die erste Variante den Vorteil hat, dass wir bei auftretenden Fehlern genau sehen, welches SQL-Statement einen Fehler verursacht, haben wir andererseits das Problem, dass ggf. nicht alle Befehle ausgeführt werden, ohne dass wir das merken.

Ich erstelle daher mehrzeilige SQL-Befehle zum Debuggen, den Import selbst führe ich aber mit einem Einzeiler durch, um sicherzugehen, dass alle Zeilen importiert werden (oder im Fehlerfall eben keine, was ich schnell merke).

Meine Mehrzeiler-Formel für die erste Wertzeile (Excel-Zeile 2), die ich in Zelle J2 eingefügt und nach unten kopiert habe, lautet:

="INSERT INTO letsMeetMigrationScheme.tblUrsprung (namen, adresse, telefon, hobbytext, email, geschlecht, interesse, geburtstag) VALUES('"&A2&"', '"&B2&"', '"&C2&"', '"&D2&"', '"&E2&"', '"&F2&"', '"&G2&"', '"&H2&"');"

Für den Einzeiler-Befehl benötige ich nur die Klammer mit dem Werten aus der obigen Formel, wobei die einzelnen Klammern mit Kommata getrennt werden:

="('"&A2&"', '"&B2&"', '"&C2&"', '"&D2&"', '"&E2&"', '"&F2&"', '"&G2&"', '"&H2&"'),"

Diese Wert-Zeilen werden dann später zu einem SQL-Statement ergänzt:

INSERT INTO letsMeetMigrationScheme.tblUrsprung (namen, adresse, telefon, hobbytext, email, geschlecht, interesse, geburtstag) VALUES
/*HIER DAS ERGEBNIS DER FORMEL EINFÜGEN UND DAS LETZTE KOMMA LÖSCHEN*/
;

Wichtig ist, dass das letzte Komma gelöscht (bzw. durch einen Semikolon ersetzt) werden muss, da sonst der SQL-Befehl nicht durchläuft.

INSERT INTO tabelle (attr1, attr2) 
VALUES 
 (wert1, wert2);
 (wert3, wert4);

INSERT INTO tabelle (attr1, attr2) 
  SELECT * FROM (
    VALUES 
     (wert1, wert2), 
     (wert3, wert4)
  ),

INSERT INTO letsMeetMigrationScheme.tblUrsprung (namen, adresse, telefon, hobbytext, email, geschlecht, interesse, geburtstag)
SELECT * FROM (
  VALUES
  ('Forster, Martin', 'Minslebener Str. 0, 46286, Dorsten', '02372 8020', 'Fremdsprachenkenntnisse erweitern %78%; Im Wasser waten %80%; Schwierige Probleme klären %61%; Morgens Früh aufstehen %17%; ', 'martin.forster@web.ork', 'm', 'w', '07.03.1959'),
  ('Elina, Tsanaklidou', 'Gartenweg 13, 69126, Heidelberg', '06221 / 98689', 'Jemanden massieren %57%; Mir die Probleme von anderen anhören %21%; Abends seinem Partner Ereignisse des Tages erzählen %53%; Für einen guten Zweck spenden %25%; ', 'tsanaklidou.elina@1mal1.te', 'w', 'm', '28.02.1958')
) AS temp (namen, adresse, telefon, hobbytext, email, geschlecht, interesse, geburtstag);

Testen des Imports

Um sicherzustellen, dass alle Daten importiert wurden, sollten wir zumindest rudimentäre Plausibilitätskontrollen vornehmen. Wurden alle Zeilen importiert? Wurden alle Zeichen importiert? Wir zählen in der Excel-Tabelle nach.

Spaltenweise lassen sich die Zeichen zählen z.B. über die Matrixfunktion (in Excel Formel eingeben, dann Strg-Shift-Return drücken):

=SUMME(LÄNGE(Tabelle1!A2:A1577))

(Für die anderen Spalten entsprechend)

Es ergeben sich folgende Testfälle, die sich leider durch unterschiedliche Abfragen und unterschiedliche Zählweise der DBMS in den Ergebnissen unterscheiden.

Fazit des Imports

Damit wären die Daten der Ausgangstabelle in tblUrsprung importiert. Ein fertiges SQL-Skript für

• MariaDB findet sich hier als Mehrzeiler bzw. hier als Einzeiler.

• MS SQL Server findet sich hier als Mehrzeiler bzw. hier als Einzeiler.

• PostgreSQL findet sich hier als Mehrzeiler bzw. hier als Einzeiler

Natürlich muss vorher die Struktur angelegt werden, z.B. mit dem entsprechenden Skript aus Teil 1 der Beispiellösung (siehe oben oder in Teil 1).

Es fehlt noch die eigentliche Normalisierung im Bestand - das wird in einem weiteren Artikel beschrieben.

Alle Artikel der Beispiellösung

Dieser Artikel ist Teil einer mehrteiligen Serie, in der

• die eigentliche Aufgabestellung das Ausgangsszenario vorstellt und den Datenbank-Export als Tabelle verlinkt,

• im ersten Teil der Beispiellösung das Zielsystem modelliert wird (ER-Modell, Relationenmodell, physisches Modell / DDL),

• im zweiten Teil der Beispiellösung SQL-Befehle für den Import aus der Tabellenkalkulation erstellt werden und

• im dritten Teil der Beispiellösung die Ausgangsdaten über Zeichenkettenfunktionen, Datumsfunktionen, JOINS und Vereinigung von Abfragen schließlich Daten in das neue Zielsystem übernommen werden.

Links und weitere Informationen

• oer-Informatik Repositories auf GitLab

Hinweis zur Nachnutzung als Open Educational Resource (OER)

Dieser Artikel und seine Texte, Bilder, Grafiken, Code und sonstiger Inhalt sind - sofern nicht anders angegeben - lizenziert unter CC BY 4.0. Nennung gemäß TULLU-Regel bitte wie folgt: “Import der Exceldaten des "Let’s Meet"-Datenbankprojekts (LS Beispiellösung Teil 2)” von oer-informatik.de (H. Stein), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/lets_meet-beispielloesung_import veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-sql/lets-meet-db. Stand: 23.05.2023.

[Kommentare zum Artikel lesen, schreiben] / [Artikel teilen] / [gitlab-Issue zum Artikel schreiben]