Wir wurden beauftragt, die vielen miteinander verbundenen Excel-Tabellen einer britischen Wirtschaftsprüfungsgesellschaft in ein robustes SQL Server basiertes Datenbankprojekt mit Excel als Frontend umzuwandeln. Teil des Projekts war das Lesen und Speichern von Daten aus Excel in SQL Server.
Der Komplexitätsgrad war immens.
Die Londoner Wirtschaftsprüfungsgesellschaft
Die Wirtschaftsprüfungsgesellschaft ist ein mittelgroßes, angesehenes Unternehmen mit Sitz in London. Sie übernehmen die Buchhaltung von Tausenden unabhängiger Einzelunternehmer in ganz Großbritannien und erledigen alles von Steuererklärungen bis hin zu Banküberweisungen für sie.
Sie verwalten ein unglaubliche Menge an Daten.
Die Verwendung von Excel als Datenbank war keine Option mehr
Verständlicherweise war das Unternehmen seinen internen Excel-Tabellen und -Systemen entwachsen.
Wir hatten bereits eine ziemlich ausgefeilte Excel-Datenbanklösung für sie erstellt, die die eingehenden und ausgehenden Daten effizient verarbeitete. Das Unternehmen hatte jedoch einen Expansionsgrad erreicht, bei dem es einfach zu viele Daten gab, als dass Excel sie schnell und schnell verarbeiten konnte.
Und es gab auch andere Bedenken, wie zum Beispiel:
- Portabilität der Daten, falls das Unternehmen in Zukunft auf eine andere Plattform wechseln möchte.
- Fortgeschrittene Berichterstattung, die angesichts der Datenmenge, mit der sie zu tun hatten, immer schwieriger wurde.
- Einfache Datensicherung.
- Wahrung der Integrität der Daten während des Wachstums des Unternehmens und mehr Mitarbeiter erhielten Zugang zu Rohdaten.
- Datensicherheit.
Excel hatte großartige Arbeit geleistet, bis die Zahlen mehrere Millionen Datensätze pro Tabelle erreichten.
Wir hatten bereits eine Datenbank in Excel erstellt. Jetzt mussten wir diese “Datenbank” nehmen und in eine tatsächliche Datenbank einfügen – so etwas wie MySQL oder SQL Server oder Oracle
Die Entscheidung, von Excel zu SQL Server zu wechseln
Excel tendiert dazu, mit Microsoft-Produkten “besser zusammen zu spielen”, daher wollten wir MySQL nicht verwenden.
Eppert Consulting hatte zuvor ein gigantisches Prognoseprojekt für ein deutsches Unternehmen abgeschlossen, dessen bevorzugte Datenbank Oracle war. Die Verbindung von Excel mit Oracle ist mit Komplexitäten verbunden, mehr als mit SQL Server.
Die Wirtschaftsprüfungsgesellschaft war mit Microsoft-Produkten vertraut, und so wurde beschlossen, Excel mit SQL Server zu verbinden
Dann begann die Arbeit.
Wenn ich für Sie jeden Schritt des Projektlebenszyklus detailliert beschreiben würde, wäre dieser Artikel ein Buch. Der gesamte Prozess war sowohl faszinierend als auch lohnend
Excel ist ein leistungsstarkes Tool, aber in Kombination mit SQL Server als Back-End-Datenprovider ist es zweifellos das effektivste Tool, das ein Unternehmen in seinem Arsenal haben kann. Es kann alles tun, von der Erstellung von Angeboten bis zur Ausführung einer hoch entwickelten Prognosesoftware, die den Umsatz steigert.
Rückblickend fühlt sich das Projekt in seinem Umfang fast legendär an, obwohl es sich nicht bei jedem Schritt des Prozesses so anfühlte.
Hier sind einige Beispiele von dem, was wir getan haben.
Der Datenimport von Excel nach SQL Server
Obwohl sowohl Excel als auch SQL Server integrierte Tools zum Importieren und Exportieren von Daten haben, würde ich nicht empfehlen, diese für ein Projekt mit so vielen Daten zu verwenden.
Diese Aufgabe wird am besten mit der VBA-Programmierung erledigt.
Sobald die Daten importiert wurden, muss der Programmierer so viel Zeit wie nötig aufwenden, um die Richtigkeit dieser importierten Daten zu überprüfen.
Dieser Schritt kann nicht unterbewertet werden. Es gibt so viel Potenzial für kleinere Importfehler, die später Schneeballeffekte haben könnten. Sie sparen viel Debugging-Zeit, wenn Sie die Daten zu diesem Zeitpunkt voll und ganz überprüfen. Ein frischer Satz Augen hilft. Excel-Formeln und einige direkte VBA-Programmierprüfungen sind ebenfalls nützlich.
Täglicher Import und Export von Excel nach SQL Server und umgekehrt
Sobald die Daten in Excel importiert wurden, müssen sie zurück in Excel gezogen werden, um damit arbeiten zu können.
Nichts geht über die unzähligen Funktionen von Excel für die Arbeit mit Daten. Die Wirtschaftsprüfungsgesellschaft wollte die Daten unbedingt auf SQL Server verschieben, aber das Eliminieren von Excel als Buchhaltungswerkzeug kam nie in Frage
Es gibt verschiedene Möglichkeiten, regelmäßig von SQL nach Excel zu exportieren und zu importieren und umgekehrt. Die häufigste (obwohl wir dies nicht empfehlen) besteht darin, eine Verbindung zum SQL Server mithilfe von VBA-Code herzustellen, die Daten abzurufen, die Verbindung zu schließen und dies für jede Transaktion fortzusetzen
Dies ist ineffizient.
Die Geschwindigkeit von Excel zu erhöhen war unerlässlich
Die Geschwindigkeit war ein Schlüsselfaktor für die Entscheidung, von Excel auf SQL Server umzusteigen, und das ständige Öffnen und Schließen von Verbindungen würde die Software aufgrund der enormen Anzahl von Transaktionen, die dieses Unternehmen pro Minute durchführte, verlangsamen.
Wir empfehlen, die Daten mithilfe der VBA-Programmierung von SQL Server in eine versteckte Arbeitsmappe in Excel zu ziehen. Führen Sie dies aus, wenn das Programm zum ersten Mal geöffnet wird, und aktualisieren Sie die Daten regelmäßig, um die Konsistenz zu gewährleisten.
Es gibt da noch einiges zu sagen über die Vermeidung von Datenkonflikten, aber das würde den Rahmen dieses Artikels sprengen.
Der Schlüssel ist, die Datenstruktur zu “verflachen”. Excel lässt sich am einfachsten bearbeiten, wenn sich alle Daten auf einem einzigen Blatt befinden. SQL Server ist genau das Gegenteil. Durch die Definition einer relationalen Datenbank schreiben die Best Practices in SQL Server vor, dass die Daten in Tabellen unterteilt und miteinander verknüpft werden müssen. (Der Fachbegriff hierfür ist “Normalisierung” der Daten.)
Diese beiden Grundsätze stehen in direktem Widerspruch zueinander.
Schnittstelle zwischen Excel und SQL Server
Wenn Sie Excel mit SQL Server als Back-End verwenden möchten, müssen Sie eine Schnittstelle zwischen den beiden Systemen bereitstellen, die die Daten so “glättet”, dass sie sich in Excel meist in einem Blatt befinden. Um Daten an SQL Server zu senden, benötigen Sie ein umgekehrtes System, das sie auf dem Rückweg in separate Tabellen “normalisiert”.
Dies scheint eine ziemlich aufwendige Programmierung zu sein, und das kann auch tatsächlich so sein. Sobald die ersten Elemente vorhanden sind, kann der Code für alle Datenübertragungen zwischen Excel und SQL Server dann allerdings wiederverwendet werden.
Excel ist in diesem Projekt immer vorherrschend. Es ist unwahrscheinlich, dass Sie am Ende auch nur ein Zehntel der vielen Funktionen von SQL Server verwenden. Wenn Sie Excel mit SQL Server verbinden, wird die Programmierung auf lange Sicht einfacher, wenn ein Großteil der Arbeit in der VBA-Programmiersprache von Excel ausgeführt wird. Dies erleichtert das zukünftige Debuggen.
Weitere Excel-Integrationen
Aber es hörte hier nicht auf. Bis wir den Großteil der Integration von Excel in SQL Server abgeschlossen hatten, hatte das Tool ein Stadium von solchem Wert erreicht, dass das Unternehmen Erweiterungen anforderte.
Jede der Modifikationen war klein und nahm vergleichsweise wenig Zeit in Anspruch, um sie umzusetzen. Wenn ich jedoch auf das fertige Produkt zurückblicke, bin ich immer noch erstaunt, wie viel wir erreicht haben. Ich bin auch erstaunt darüber, wie unglaublich flexibel Microsoft Excel sein kann, wenn es darum geht, es mit anderen Tools und Software wie Sage, Adobe Acrobat, Amazon Web Services usw. zu verbinden und zu integrieren.
Hier sind nur einige der vielen Integrationen, die Eppert Consulting in diese riesige und unschätzbare Excel-Buchhaltungssoftware programmiert hat.
Excel und Outlook integrieren
Eine der ersten Anforderungen bestand darin, Excel in Outlook zu integrieren – beispielsweise die Steuererklärung eines Kunden vorzubereiten und die Steuererklärung dann im PDF-Format mit Outlook an ihn zu senden.
Wir haben dies natürlich in der Programmiersprache Visual Basic für Applikationen getan. Erweiterte Aufgaben wie diese können nur mit den Programmierfunktionen von Excel ausgeführt werden.
Am Ende haben wir viele Integrationen zwischen Excel und Outlook programmiert. Hier sind einige der Funktionen, die für jede oder alle dieser Integrationen erforderlich waren:
- Korrektes Formatierung des vorbereiteten Excel-Dokuments, damit es beim Export in PDF professionell aussieht.
- Automatisches E-Mail Senden an eine dem Kunden zugeordnete E-Mail-Adresse (in der zugrunde liegenden SQL Server-Datenbank gespeichert).
- Verschiedene E-Mail-Vorlagen, die vom Benutzer bearbeitet werden können.
- E-Mail-Vorlagen werden automatisch ausgewählt, wenn Sie auf die Schaltfläche “Senden” klicken, basierend auf den ausgefüllten Optionen in der Excel-Tabelle.
- Massen-E-Mail von angehängten Finanzzahlen (Dateien entweder in Excel oder im PDF-Format) unter Verwendung der gespeicherten Daten.
- Ein ausgeklügelter Prozess zur Genehmigung von Dokumenten, der dazu führt, dass ein fertiges Dokument schließlich per E-Mail versendet wird, nachdem es an verschiedenen Stellen überprüft und visiert wurde
Verbinden von Excel mit Massen-E-Mail- und SMS-Software
Das Unternehmen verwendet einen Massen-E-Mail-Dienst, der von Microsoft Outlook unabhängig ist. Außerdem wird ein Massen-SMS-Dienst verwendet, bei dem Nachrichten in einem bestimmten Format auf die Website eines Drittanbieters hochgeladen werden müssen.
Wir haben eine Funktion programmiert, um diese Nachrichten mit einem Klick zu erstellen und die Upload-Datei in Excel selbst mit VBA-Programmcode zu formatieren.
Importieren von Bankdaten in Excel – Integration mit HSBC Connect, RBS und anderen Finanzinstituten
Eine der ehrgeizigsten Aufgaben war es, Finanztransaktionen von HSBC, der Royal Bank of Scotland und anderen Finanzinstituten in Excel zu importieren.
Excel musste auch Zahlungsdaten direkt an diese Finanzinstitute exportieren.
Jede Institution hatte ihre eigene Art, Daten zu akzeptieren, sowie ihre eigenen Sicherheitspraktiken. Wir haben verschiedene Funktionen so programmiert, dass Excel Daten für Tausende von Bankkonten empfangen und senden kann, für die diese Buchhaltungsfirma ein Mandat hatte.
Programmieren von Excel für die Arbeit mit Amazon Web Services
Das Unternehmen hat im Laufe der Jahre mehrere hunderttausend Dokumente erstellt. Amazon Web Services wurde als sicherer Speicherort für diese Dokumente ausgewählt.
Das Excel-Tool benötigte eine Funktion, mit der generierte Dokumente direkt auf die Simple Storage-Server von Amazon Web Services hochgeladen werden konnten.
Um dies zu erreichen, mussten wir ein externes Tool in der Programmiersprache .NET von Microsoft programmieren, da Amazon keine Schnittstelle für Excel bereitstellte.
Nun, wir haben es geschafft und die Daten für die hochgeladenen Dokumente mit unserer lokalen SQL Server-Datenbank synchronisiert, sodass wir immer einen lokalen Datensatz hatten, mit dem wir die Daten von Amazon mit unseren vergleichen konnten.
Wir führen auch separate Sicherungen aller Dokumente durch. Die Excel-Software sorgt dafür, dass jede Kopie des Dokuments – Backup und Original – dort platziert wird, wo sie hingehört.
Verbinden von Excel mit PHP-, MySQL- und Microsoft .Net-Websites
In der Zwischenzeit entwickelte sich die Web-Technologie mit erstaunlicher Geschwindigkeit weiter. Die Kunden der Wirtschaftsprüfungsgesellschaft forderten immer mehr, dass ihre Dokumente online heruntergeladen werden können.
Zu diesem Zweck musste eine zweite Online-Datenbank erstellt werden, in der Informationen zu den Dokumenten der einzelnen Benutzer gespeichert werden. Wir mussten Funktionen programmieren, um:
- Benachrichtigen der Kunden über ihre generierten Dokumente.
- Sicherstellen, dass nur autorisierte Benutzer Dokumente herunterladen können.
- Hochladen der Dokumente an einen sicheren Ort, an dem sie von authentifizierten Benutzern heruntergeladen werden können.
Wir haben auch eine Funktion programmiert, mit der Kunden Finanztransaktionen über eine Online-Plattform genehmigen oder ablehnen können.
Wir haben eine MySQL-Datenbank zum Speichern von Daten verwendet, da die Firma bereits eine PHP-Website eingerichtet hatte.
Dies führte schließlich zu einer Microsoft .Net-Website, einer zusätzlichen SQL-Datenbank, etc., etc., etc. …
Das Projekt wuchs weiter, jedes Mal wurde ein Mehrwert erzielt und die Rentabilität gesteigert.
Excel kann sich mit allem verbinden – mit allem!
Es gab nie einen Mangel an Ideen, und die Software wuchs weiter. Jedes neue angeforderte Feature war eine Herausforderung, und ich freue mich sagen zu können, dass Eppert Consulting sich diesen Herausforderungen gestellt hat.
Unabhängig von der Anfrage haben wir einen Weg gefunden, dies zu erreichen. Dies spricht mehr für die Vielseitigkeit von Excel als für unsere Programmierkenntnisse. Obwohl eine gründliche Kenntnis von Excel und all seinen Potenzialen unbedingt erforderlich ist, um Lösungen für ungewöhnliche Probleme zu finden, es gehört außerdem dazu – “über den Tellerrand hinaus” zu denken.