Excel und MySQL verbinden: Datenimport leicht gemacht
In diesem Beitrag möchte ich dir zeigen, wie du Microsoft Excel mit einer MySQL Datenbank verbindest und Daten aus dieser ausliest und anzeigst. Im vorherigen Beitrag habe ich dir gezeigt, wie man Messdaten eines Shelly Plus 1PM Mini in eine MySQL Datenbank via PowerShell Skript speichert, an diesen möchte ich hier nun anknĂŒpfen.
Schritt-fĂŒr-Schritt-Anleitung
Es folgt nun eine kleine Schritt-fĂŒr-Schritt-Anleitung, in welcher ich dich mitnehmen werden und dir aufzeige wie du:
- den benötigten ODBC-Datenbanktreiber installierst,
- die Datenquelle unter Microsoft Windows 10 einrichtest,
- die Datenbank in Microsoft Excel einrichtest und
- die Daten aus der Tabelle ausliest
Schritt 1 - Herunterladen & Installieren des ODBC Datenbanktreibers
FĂŒr die Verbindung zur MySQL Datenbank benötigen wir einen Treiber, diesen können wir unter https://dev.mysql.com/downloads/connector/odbc/ herunterladen.
Beachte hier, dass du den passenden Treiber 64bit oder 32bit fĂŒr deine Excel Version wĂ€hlst!
In meinem Fall wĂ€hle ich den "Windows (x86, 64-bit), MSI Installer aus", die knall 14 MB groĂe Datei ist schnell heruntergeladen und den Installer kannst du direkt aus dem Browser starten. Ggf. wirst du hier eine Meldung vor potenziell schĂ€dlicher Software erhalten, diese kannst du jedoch in diesem Fall mit einem Klick auf die SchaltflĂ€che "OK" bestĂ€tigen.
Schritt 2 - Einrichten des ODBC Treibers
Nachdem der Treiber installiert wurde, mĂŒssen wir diesen zunĂ€chst in Windows einrichten.
Dazu öffnen wir das StartmenĂŒ und geben "odbc" ein. Es sollten jetzt 2 EintrĂ€ge gefunden werden.
Hier wÀhlen wir wiederum den Eintrag, welcher zu der Excel-Version passt (64bit oder 32bit).
In dem neuen Fenster wĂ€hlen wir unter "Benutzer-DSN" die SchaltflĂ€che "HinzufĂŒgen..."
Nun wÀhlen wir den Eintrag "MySQL ODBC 8.1 Unicode Driver" aus und bestÀtigen dieses mit der SchaltflÀche "Fertig stellen".
Im nĂ€chsten Fenster mĂŒssen wir nun der Verbindung einen Namen vergeben (1) sowie die Serveradresse (2) eingeben. Auf meinem System lĂ€uft ein XAMPP mit einem lokalen MySQL Server, daher wĂ€hle ich hier "localhost". Der Benutzer "root" (3) hat per Default kein Passwort, diesen können wir nun nutzen um die Verbindung zu testen (4) wenn dieses Erfolgreich war (5) können wir diese Eingaben mit "OK" (6) bestĂ€tigen.
Der MySQL ODBC Treiber wurde nun mit dem Namen "MySQL-Driver" eingerichtet und wir können dieses Fenster mit "OK" verlassen.
Schritt 3 - Aufbauen der Datenverbindung in Microsoft Excel zur MySQL Datenbank
Nachdem wir nun den ODBC Treiber eingerichtet haben, können wir zu Microsoft Excel wechseln und dort die Datenbank einrichten.
ZunĂ€chst klicken wir auf den Reiter "Daten" (1) und dort wĂ€hlen wir unter "Daten abrufen" den Eintrag "Aus anderen Quellen" (2) auf. Aus diesem MenĂŒ wĂ€hlen wir nun den Eintrag "Aus ODBC" (3).
Nun mĂŒssen wir unseren zuvor eingerichteten ODBC Treiber fĂŒr die MySQL Datenbank auswĂ€hlen und dieses mit "OK" bestĂ€tigen.
Im nĂ€chsten Fenster mĂŒssen wir jetzt Benutzername und Passwort eingeben. Wie erwĂ€hnt hat der Benutzer "root" per Default kein Passwort, daher lasse ich diese hier frei und bestĂ€tige die Eingabe mit "Verbinden".
Wenn die Verbindung erfolgreich hergestellt wurde, dann werden die zum Benutzer berechtigten Tabellen angezeigt. Da ich in meinem Fall den Benutzer "root" gewÀhlt habe werden, hier alle Tabellen aufgelistet.
Aus dieser Liste wÀhle ich die Datenbank "shelly_db" und dort die Tabelle "messdaten" aus und bestÀtige die Auswahl mit der SchaltflÀche "Laden".
Die Daten werden in der geöffneten Arbeitsmappe geladen und so dargestellt wie in der Datenbank.
Schritt 4 - Formatieren der Daten
Damit die geladenen Daten lesbarer werden, mĂŒssen wir dies formatieren.
Schritt 4.1 - Formatieren des Zeitstempels
Das etwas kompliziertere ist der Zeitstempel, welcher die Minuten seit dem 01.01.01970 reprÀsentiert. Diesen können wir jedoch mit einer kleinen Funktion in ein Datum/Zeitformat umrechnen.
=(ZELLE/86400)+DATUM(1970;1;1)
Im nachfolgenden Video zeige ich dir wie du die Spalte "minute_ts" nutzt um eine neue Spalte zu befĂŒllen.
In meinem Fall wÀhle ich ein benutzerdefiniertes Format aus Datum & Uhrzeit.
Schritt 4.2 - Spalten formatieren
Zum Schluss werden die Spalten noch entsprechend benannt:
- apower - Leistungsaufnahme (in Watt pro Stunde),
- voltage - Spannung (in Volt),
- freq - Frequenz (in Herz),
- current - Stromaufnahme (in Ampere)