DB aus Excel erzeugen

Um auch auf größere Datenmengen mittels Datenbank zugreifen zu können, wurde ein Datenbankzugriff realisiert. Im Hintergrund wird eine SingleFile Datenbank angesprochen (SQLite). Dadurch können aus den Makros DB-Statements ausgeführt werden.

Um die Datenbank zu füllen, wurde zusätzlich ein Excel Plugin entwickelt, mit welchem Datenbank, Tabellen und Daten generiert werden können.

Installation

Um aus Excel eine Datenbank zu erzeugen, muss vorerst das Plugin installiert werden.

Die dazu benötigten Dateien befinden sich hier am Sharepoint:

Je nachdem, ob das Betriebssystem ein 32 oder ein 64 Bit System ist, muss man nun entweder den Ordner x64_2013 (64 Bit) oder x86_2013 (32 Bit) verwenden.

Darin befinden sich die Dateien setup.exe und SQLite.Interop.dll:

Setup.exe muss ausgeführt werden, um das Plugin zu installieren. Anschließend muss SQLite.Interop.dll in das Excel-Installationsverzeichnis kopiert werden.

Das Excel-Installationsverzeichnis befindet sich vermutlich hier: C:\Program Files\Microsoft Office\root\Office16

Wenn die Installation erfolgreich war, sollte das in Excel unter Datei/Optionen/Menüband anpassen in etwa so ausschauen:

Nach erfolgreicher Installation erscheinen nun in Excel im Menü Add-Ins folgende Icons:

Wenn das nicht der Fall ist, sollte man kontrollieren, ob das Menüband “Add-Ins“ gesetzt ist. (Datei - Optionen - Menüband anpassen)

DB erzeugen

Um das Plugin verwenden zu können, muss der vorgefertigte Header DB_Vorlage.xlsx, Tabellenblatt "Tabelle mit Autoincrement PK" oder "Tabelle ohne Autoincrement PK") verwendet werden.

Excel-Vorlage

Diese Excel-Mappe enthält 3 Tabellenblätter:

• Main

• Tabelle mit Autoincrement PK (Vorlage DB-Header für eine DB-Tabelle mit automatisch vergebenen Primary Key)

• Tabelle ohne Autoincrement PK (Vorlage DB-Header für eine DB-Tabelle mit selbst vergebenen Primary Key)

Tabellenblatt Main

Dieses Tabellenblatt kann dazu verwendet werden, um die immer gleichbleibenden notwendigen Informationen zum Erstellen einer Datenbank zentral abzuspeichern. Dies sind der Pfad zur Datenbank-Datei, Datenbank-Name und Passwort.

Diese Informationen werden im Datenbank-Header (siehe dazu die anderen Tabellenblätter der Vorlage) verlinkt. Natürlich können Sie dort auch hartkodiert eingegeben werden, was aber für ALLE Tabellen der Datenbank gemacht werden müsste. Durch Verwenden von "Main" kann man das mit nur einer Eingabe zentral erledigen.

Standardmäßig wird die SQLite-Datenbank auf "\Masterdata\<ISSVersion>\SQLDB\" abgespeichert.

In diesem Fall müssen nur die eingefärbten Felder im Tabellenblatt Main eingegeben werden (Version, Projektname, Name der Datenbank und das Passwort). Aus Version und Projektnamen wird automatisch der Pfad erstellt. Sollte man einen anderen Pfad benötigen, so kann dieser hartkodiert in Zelle B3 eingegeben werden. Die hier eingegebenen Daten werden im DB-Header (siehe dazu die anderen Tabellenblätter der Vorlage) verknüpft.

Tabelle mit Autoincrement PK

Diese Tabelle beinhaltet eine Vorlage für den DB-Header für eine Tabelle mit automatisch vergebenen Primary Key. Siehe dazu Kapitel DB Header.

Tabelle ohne Autoincrement PK

Diese Tabelle beinhaltet eine Vorlage für den DB-Header für eine Tabelle mit selbst vergebenen Primary Key. Siehe dazu Kapitel DB Header.

DB Header

DB-Header mit automatisch vergebenem Schlüssel (AUTO PrimaryKey = TRUE)

DB-Header mit selbst vergebenem Schlüssel (AUTO PrimaryKey = FALSE)

Um eine Datenbank zu erstellen, muss das Verzeichnis (Zelle A2) und das DB File (C2) angegeben werden. Es muss ein Folder SQLDB in entsprechender Version angelegt werden(für dieses Beispiel oben: L:\eigner-mu\951_ZK_Haustueren\MasterData\iss_95101. Nach Eingabe des Tabellennamens (A4) muss entschieden werden, ob es für die Tabelle einen automatischen Primary Key geben soll (A6).

Soll ein automatischer Primary Key erstellt werden so muss im Feld A6 TRUE gesetzt werden und ggf. der Standardname (Id) abgeändert werden.

Zeile 8 beinhaltet eine fortlaufende Nummerierung der Spalten. Bei einem automatisch vergebenen Primary Key beginnt die Nummerierung mit 2, sonst mit 1. Dies hat keine Bedeutung für die Datenbank-Struktur, sondern dient lediglich der besseren Orientierung. Denn der Zugriff auf die Daten via Makros erfolgt u.a. über die Spaltennummer.

Nun müssen die Spalten definiert werden. Hierfür muss in der Zeile 9 der jeweilige Datentyp definiert werden. Die grundsätzlich möglichen Datentypen für SQLite-Datenbanken können hier nachgelesen werden: http://www.sqlite.org/datatype3.html Oder auch hier in einem allgemeinen SQLite Tutorial: http://www.tutorialspoint.com/sqlite/

Für ISS+ können nur die Datentypen INT und VARCHAR verwendet werden!

Deshalb wurde für die Kommazahl der Spalte UdWert in unserem Beispiel VARCHAR und für die Boolean-Spalten AussenOeffnend und AussenOeffnend_Variante2 VARCHAR bzw. INT verwendet.

Zeile 10 ist für zusätzliche Eigenschaften, wie z.B. NOT NULL angedacht. Diese kann jedoch auch ausgelassen werden.

Schlussendlich gibt man die Namen für die Spalten ein. Hierbei müssen die Richtlinien von SQLite beachten werden (keine Punkte, keine Leerzeichen, …)

Besonderheiten im ISS+ bezüglich der Wahl des Datentypen in der Excel-DB

Das ISS+ kann nur Ganzzahlen (INT) und Text (VARCHAR) aus der Datenbank auslesen.

• Für Boolean den Datentyp INT mit den Werten 0 für FALSE und 1 für TRUE verwenden oder VARCHAR(5) mit den Werten false und true als Text.

• Für Kommastellen den Datentyp VARCHAR(x) verwenden. Als Kommastelle muss statt einem Beistrich ein Punkt verwendet werden. <x> gibt die Anzahl der Vorkommastellen + Anzahl der Nachkommastellen + Kommazeichen an. Im ISS+ kann der Wert dann mit VAL() in eine Zahl umgewandelt werden.

SQLite Datenbank erzeugen

Durch Klick auf die Schaltfläche Create DB kann nun die so erstellte Tabellen-Struktur samt Daten in die SQLite-Datenbank überführt werden.

Die Datenbank muss zu diesem Zeitpunkt verschlüsselt sein, was sie standardmäßig ist. Siehe dazu Kapitel Datenbank ver- bzw. entschlüsseln (Passwort ein / aus)

Sollte die SQlite-Datenbank zu diesem Zeitpunkt noch nicht existieren, wird sie angelegt. Die Tabelle mit dem Namen aus der Zelle A4 wird angelegt und mit den Datensätzen ab Zeile 12 gefüllt. Sollte diese Tabelle bereits existieren, wird sie überschrieben. Sollten noch weitere Tabellen in der SQlite-Datenbank vorhanden sein, so bleiben diese unangetastet.

Für jede Tabelle der SQlite-Datenbank, muss eine Excel-Tabelle im oben beschriebenen Format angelegt und mit Create DB erzeugt werden. Create DB erzeugt also eher eine Tabelle als die gesamte Datenbank. Die Datenbank selbst wird nur beim ersten Mal angelegt. Alle weiteren Male wird nur jeweils die entsprechende Tabelle des aktiven Tabellenblattes erzeugt.

Datenbank ver- bzw. entschlüsseln (Passwort ein / aus)

Per Mausklick auf die Schaltfläche Passwort ein/aus kann die Datenbank ent- bzw. verschlüsselt werden.

Normalerweise ist die Datenbank verschlüsselt. Ein Klick führt in diesem Fall zur Entschlüsselung der Datenbank und der Erfolg der Aktion wird mit einem Dialogfenster bestätigt

Für den Datenbankzugriff aus dem ISS+ muss die Datenbank verschlüsselt sein!

Wenn die Datenbank entschlüsselt ist, wirft ISS+ folgenden Makrofehler:

Wenn man sich den Inhalt der Datenbank mit einer Software wie Microsoft Access oder dem DB Browser für SQLite ansehen möchte, muss die Datenbank entschlüsselt sein!

Der SQLite DBBrowser kann aus dem Internet downgeloadet werden (https://sqlitebrowser.org). Für den Zugriff der SQLite-Datenbank aus Microsoft Access heraus benötigt man einen ODBC-Treiber. Diesen gibt es ebenfalls im Internet zu finden.

Weitere Informationen zu diesem Thema finden Sie hier.