Excel-Arbeitsblätter per SQL abfragen und auswerten

Einleitung

Manchmal kommt es leider vor, dass man Daten auswerten oder mit anderen Daten vergleichen muss, dies aber nicht direkt auf der Datenbank, also nicht direkt per SQL machen kann. Sei der Grund, dass die Daten auf unterschiedlichen Datenbanken liegen oder dass man die Daten direkt als Excel-Datei bekommt und keine Rechte hat diese als Tabellen auf der Datenbank anzulegen. Oftmals wird dann noch verlangt, dass die Auswertung auch in Form eines Excels geliefert wird. In solch einem Fall ist man dann selber derjenige, der geliefert ist – zumindest, wenn man versucht, die Daten mit Excel-Formeln auszuwerten. Wer schon einmal eine Viertelstunde lang versucht hat herauszufinden, warum eine einfache IF-Formel nicht funktioniert, nur um am Ende festzustellen, dass Excel beim Kunden auf Deutsch eingestellt ist, man also WENN benutzen muss, der weiß, wovon ich rede. Dazu kommt, dass Excel insbesondere bei großen Datenmengen sehr langsam wird. Und die ganze Zeit denkt man sich, es wäre so einfach, das Ganze per SQL zu machen…

Der aufmerksame Leser – das sind die, die zumindest die Überschrift gelesen haben – wird schon wissen, dass es möglich ist, eine solche Aufgabe auch in Excel per SQL zu erledigen. Wie das funktioniert, soll in diesem Blogbeitrag erläutert werden. Alles was man braucht, ist ein bisschen VBA (Visual Basic for Applications) und natürlich das passende SQL-Statement. Wer VBA noch nie benutzt hat, sei einerseits ausdrücklich beglückwünscht, darf andererseits auch unbesorgt bleiben, da man letztendlich nur den unten bereitgestellten Code kopieren und das SQL nach seinen Wünschen und Bedürfnissen anpassen muss. Für die ganz Faulen ist unter diesem Link (yet to come) ein Excel abgelegt, in dem der unten vorgestellt Code enthalten ist. 

Daten in Excel mit SQL abfragen

Quick Start

Gegeben sei folgende Ausgangssituation: Man hat eine Excel-Datei mit zu untersuchenden Daten in einem Arbeitsblatt namens Data. Die Daten haben Überschriften und beginnen ab Zelle A1. Man möchte die Daten per SQL abfragen bzw. auswerten und das Ergebnis in ein weiteres Arbeitsblatt namens Results schreiben. Um das tun zu können muss man ein neues Modul im VBA Editor erstellen und den unten gegebenen Code einfügen. Der VBA-Editor lässt sich über die Tastenkombination Alt F11 öffnen. Ein neues Modul kann über das Menü Insert -> Modul erstellt werden. In das neu erstellte Modul ist folgender Code einzufügen.

Sub run_sql_in_excel()
'variable declaration
Dim cn As Object 'Connection
Dim rs As Object 'RecordSet
Dim sql As String
Dim start_row as Integer: start_row = 1
Dim start_col as Integer: start_col = 1
Dim iCol as Integer
'Connect to the Data Source
Set cn = CreateObject(“ADODB.Connection”)
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & _
ThisWorkbook.Name & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'write SQL
sql = "Select * from [Data$]"
'run SQL
Set rs = cn.Execute(sql)
'delete data from previous runs in worksheet "Results"
Worksheets("Results").Cells(start_row, start_col).CurrentRegion.Clear
'insert header row worksheet "Results" (only if HDR=YES)
For iCols = 0 To rs.Fields.Count - 1
Worksheets("Results").Cells(start_row, start_col + iCols).Value = _
rs.Fields(iCols).Name
Next
'paste result of sql to worksheet "Results"
Worksheets("Results").Cells(start_row + 1, start_col).CopyFromRecordset rs
'clean up and free memory
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Über die Taste F5 lässt sich der Code ausführen. Beim Speichern des Excels ist darauf zu achten, dass als Extension xlsm oder bei großen Datenmengen besser als xlsb gewählt wird, da sonst das Makro nicht mit gespeichert wird. Im folgenden Abschnitt wird näher erläutert, wie der Code funktioniert.

Erläuterung des Codes

  1. Variablen Deklaration Zu Beginn des Codes werden die benötigten Variablen definiert. Da ganz am Anfang die Option Explicit gesetzt wurde, müssen alle benutzten Variablen deklariert werden. Ist diese Option nicht gesetzt, werden alle nicht explizit deklarierten Variablen von VBA als Typ Variant deklariert. Das ist allerdings schlechter Stil, führt schnell zu dummen Fehlern (wenn man sich an einer Stelle bei einem Variablennamen vertippt, diese Variable dann von VBA mit einem willkürlichen Wert deklariert wird) und sollte daher vermieden werden. Die beiden Variablen start_row und start_col geben die Reihe und Spalte an, ab der das Ergebnis des SQLs im Blatt Results eingetragen werden soll und können nach Belieben angepasst werden. Möchte man allerdings später auch diese Daten weiter mit SQL auswerten, empfiehlt es sich, es bei Spalte und Reihe 1 zu belassen (siehe Ende des nächsten Punktes für die Erklärung).
  2. Herstellen einer Verbindung zu den Daten, die im Excel gespeichert sind Um eine Verbindung zu den Daten herzustellen, wird der ADODB-Treiber verwendet. Dies ist der Treiber, der auch von Access für SQL Abfragen benutzt wird.

'---Connecting to the Data Source---
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & _
ThisWorkbook.Name & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With

With...End With Statements in VBA führen eine Reihe von Anweisungen aus, die wiederholt auf ein einzelnes Objekt (hier die Connection cn) verweisen. Der angegebene Provider funktioniert für alle Excel 2007 oder neuere Dateien. Im ConnectionString wird zum einen die Data Source definiert – in unserem Fall also die Excel Datei selbst (ThisWorkbook), welche über den Pfad und Namen eindeutig definiert ist – zum anderen die Extended Properties. Diese beinhalten neben der Excel-Versionsnummer die Angabe, ob die Daten Header haben (HDR=YES) oder nicht (HDR=NO). Eine weitere nützliche Extended Property ist IMAX=1. Diese führt dazu, dass Excel alle Daten als Text behandelt. Wenn man Spalten mit gemischten Datentypen hat, sollte man diese Option nuzten. Ohne diese Option versucht Excel anhand der ersten acht Einträge in einer Spalte den Datentyp dieser Spalte zu erkennen. Wählt man die Option mit Headern, sind diese nicht direkt im später erstellten Recordset enthalten. Möchte man sie dennoch über den Ergebnisspalten haben, kann man sie mit folgendem Code-Block einfügen:

'insert header row worksheet "Results" (only if HDR=YES)
For iCols = 0 To rs.Fields.Count - 1
Worksheets("Results").Cells(start_row, start_col + iCols).Value = _
rs.Fields(iCols).Name
Next

Bei der Option ohne Header wird obiger Code-Block nicht benötigt. Die Spalten bekommen in diesem Fall von Excel die Default-Namen F1, F2 usw. zugewiesen.

Weitere Details zu Provider und ConnectionString findet man hier (Abschnitt ‘Microsoft ACE OLEDB 12.0’).

(a) Perfekt 🙂                                                      (b) OK, aber gefährlich :/                  (c) Funktioniert nicht 🙁

Abbildung 1: Positionierung der Daten in einem Excel Arbeitsblatt

Damit die Verbindung zu den Daten richtig funktioniert, ist Folgendes zu beachten. Für Excel beginnen die Daten bzw. Header in der links-obersten nicht leeren Zelle. Beginnen die Daten in A1, ist alles in Ordnung, siehe 1a. Beginnen die Daten in z.B. C5 und alle Zellen in den ersten 5 Reihen der Spalten A und B sind leer ist auch noch alles in Ordnung, siehe 1b. Sobald dann aber irgendwas in z.B. A1 steht, werden die Daten nicht richtig erkannt, siehe 1c.

3. Schreiben des SQLs Sobald eine Verbindung zu den Daten im Excel hergesellt wurde, ist ein Arbeitsblatt äquivalent zu einer Tabelle und kann als solche abgefragt werden. Der Tabellenname eines Arbeitsblatts ist der Arbeitsblattname mit angehängtem $ und umrahmt von eckigen Klammern [], z.B. Sheet1 -> [Sheet1$]. Wer gerne mit named Ranges in Excel arbeitet, kann auch diese abfragen. Der Tabellenname einer named Range ist einfach der Name der named Range, z.B. SELECT * FROM MyNamedRange.

Wie oben erwähnt, wird für die Verbindung zu den Daten der gleiche Treiber wie von Microsoft Access benutzt. Daher sollte es keine Überraschung sein, dass es sich beim zu schreibenden SQL um Access SQL handelt. Dies führt leider zu (mindestens) zwei Unannehmlichkeiten:

    1. Es gibt keinen full outer join. Um das gleiche Ergebnis zu erzielen, muss man in Access SQL den recht umständlichen Weg über einen left und einen right join mit anschließendem Union gehen.
    2. Access SQL kann nur zwei Tabellen auf einmal miteinander joinen. Versucht man also drei oder mehr Tabellen miteinander zu joinen, ist es wichtig, die joins zu klammern, z.B. (siehe hier)

select
 t1.c1
, t2.c2
, t3.c3
, t4.c4
from (([table1$] t1
inner join [table2$] t2 on t1.something = t2.something)
inner join [table3$] t3 on t2.something = t3.something)
inner join [table4$] t4 on t3.something = t4.something

Eine weitere Unannehmlichkeit kommt vom VBA Editor selbst – neben dem SQL Editor in Access, der wohl schlechteste SQL Editor aller Zeiten. Da das SQL in doppelten Anführungszeichen stehen muss, diese aber nicht über Zeilen gebrochen werden können, muss man für Zeilenumbrüche auf den Line-continuation (_) und den String-continuation (& oder +) Character zurückgreifen. Ein SQL kann also schematisch so aussehen:

SQL = "SELECT field1 " & _
  ", field2 " & _
  ", field3 " & _
  "FROM [table1$] " & _
  "WHERE ( " & _
  "   condition1 " & _
  "   AND condition2 " & _
  ")"


Aber Achtung: Die Anzahl der aufeinanderfolgenden line-continuation characters ist auf 24 beschränkt. Erreicht man dieses Limit, kann man das SQL aber einfach folgendermaßen weiterschreiben: SQL = SQL & "some more SQL code".

Man beachte dabei auch die Leerzeichen am Ende jeder Zeile, ohne die das SQL zu folgendem sinnlosen String werden würde: “…,field3FROM [table1$]WHERE...".

4. Ausführen des SQLs Wenn man sein SQL fertig geschrieben hat, kann man es über den execute-Befehl auf der erstellten Connection ausführen. Das Ergebnis des SQL wird folgendermaßen einem Recordset zugeordnet.

Set rs = cn.Execute(sql)

5. Ergebnisdaten des SQLs in ein Arbeitsblatt schreiben Die einfachste Methode, die Daten aus dem Recordset zu bekommen, ist CopyFromRecordset.

Worksheets("Results").Cells(start_row + 1, start_col).CopyFromRecordset rs

Alternativ kann man auch über das Recordset loopen. Währenddessen könnte man dabei noch weitere Dinge mit den Daten anstellen, aber es empfiehlt sich, alle nötigen Bereinigungen/Verarbeitungen der Daten im SQL zu machen, da dies in der Regel deutlich schneller ist.

Do
  debug.print rs(0) 'first column
  debug.print 2*rs(1) 'two times value of second column
  debug.print LEFT(rs(2),2) 'first two characters of third column
  '...
  rs.Movenext 'Move to next record
Loop Until rs.EOF 'End of recordset


debug.print ist eine VBA-Funktion, die den Wert des hinterstehenden Arguments im Direkt-Fenster des VBA Editors anzeigt. Das Direkt-Fenster kann über Strg G ein- bzw. ausgeblendet werden.

6. Speicherplatz freigeben Am Ende wird das Recordset und die Connection geschlossen (.close) und der Speicherplatz freigegeben (set ... = Nothing). Wenn man viele Recordsets verwendet, empfiehlt es sich diese zu schließen und freizugeben, sobald man sie nicht mehr braucht, da der Code sonst sehr langsam werden kann.

Zusammenfassung

Wer viel mit SQL und wenig mit Excel arbeitet, hat über obigen Code die Möglichkeit auch bei Datenauswertungen in Excel auf bekanntes Terrain zu bauen, ohne dabei tiefgreifende VBA oder allgemeine Excel-Kenntnisse haben zu müssen. Aber auch wer gut mit Excel umgehen kann, wird diese Methode wegen ihrer Schnelligkeit zu schätzen lernen