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 Indizes generiert werden können.

Installation

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

Die dazu benötigten Dateien können Sie hier runterladen:

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)

Verzeichnis (Zelle A2) :
Pfad zur SQLite-Datenbank

DB (Zelle C2) :
Name der SQLite-Datenbank

Passwort (Zelle E4) :
Passwort der SQLite-Datenbank

Tabelle (Zelle A4) :
Der Name der zu erstellenden Datenbank-Tabelle

AUTO PrimaryKey (Zelle A6) :
TRUE für einen automatisch vergebenen Primary Key, FALSE für einen nicht automatisch vergebenen Primary Key
(in diesem Fall muss in der Zeile 9 in der Spalte des Primary Keys hinter dem jeweiligen Datentypen PRIMARY KEY stehen)

AutoIncremental PrimaryKey Name (Zelle B6) :
Name des Feldes für den automatisch vergebenen Schlüssel

Index (Zelle G2 - G7) :
Indizes können in Zelle G2 bis G7 platziert werden. Diese werden beim Erstellen der Tabelle automatisch mit erzeugt.
Beispiel: CREATE INDEX 'IDX_MODEL' ON 'Haustuer' ('Modell' ASC)
Mehr Informationen finden Sie hier: Create Index

Zusätzliche Informationen:

Spaltennummer beginnt bei 1, wenn Auto Primary Key FALSE. Spaltennummer beginnt bei 2, wenn Auto Primary Key TRUE.
Nur bei AUTO PrimaryKey = FALSE: Die Schlüsselspalte muss hinter dem Datentypen noch PRIMARY Key stehen haben!
Hier können zusätzliche Informationen zur Spalte, wie z.B. NOT NULL, anagegeben werden. Kann aber auch leer bleiben.

Verwendung

Verzeichnis

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).

Primary Key

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.

Datentypen

Jede Spalte muss einem Datentypen zugeordnet werden. Hierfür muss in der Zeile 9 der jeweilige Datentyp geschrieben werden. Für CHAR kann der Datentyp 'TEXT' verwendet werden, für Zahlenwerte (Ganzzahl und Gleitkommazahl) der Datentyp 'NUMERIC'.

Der Datentyp 'NUMERIC' erkennt beim Generieren automatisch, ob für den Datensatz 'INTEGER' für Ganzzahl bzw. 'REAL' für Gleitkommazahl verwendet werden soll. Die Gleitkommazahl kann mit Punkt oder Komma geschrieben werden. (Beispiel: 9,99 / 9.99)

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/

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, …)

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.