Ein­lei­tung

Manch­mal kommt es lei­der vor, dass man Daten aus­wer­ten oder mit ande­ren Daten ver­glei­chen muss, dies aber nicht direkt auf der Daten­bank, also nicht direkt per SQL machen kann. Sei der Grund, dass die Daten auf unter­schied­li­chen Daten­ban­ken lie­gen oder dass man die Daten direkt als Excel-Datei bekommt und keine Rechte hat diese als Tabel­len auf der Daten­bank anzu­le­gen. Oft­mals wird dann noch ver­langt, dass die Aus­wer­tung auch in Form eines Excels gelie­fert wird. In solch einem Fall ist man dann sel­ber der­je­nige, der gelie­fert ist – zumin­dest, wenn man ver­sucht, die Daten mit Excel-For­meln aus­zu­wer­ten. Wer schon ein­mal eine Vier­tel­stunde lang ver­sucht hat her­aus­zu­fin­den, warum eine ein­fa­che IF-For­mel nicht funk­tio­niert, nur um am Ende fest­zu­stel­len, dass Excel beim Kun­den auf Deutsch ein­ge­stellt ist, man also WENN benut­zen muss, der weiß, wovon ich rede. Dazu kommt, dass Excel ins­be­son­dere bei gro­ßen Daten­men­gen sehr lang­sam wird. Und die ganze Zeit denkt man sich, es wäre so ein­fach, das Ganze per SQL zu machen…

Der auf­merk­same Leser – das sind die, die zumin­dest die Über­schrift gele­sen haben – wird schon wis­sen, dass es mög­lich ist, eine sol­che Auf­gabe auch in Excel per SQL zu erle­di­gen. Wie das funk­tio­niert, soll in die­sem Blog­bei­trag erläu­tert wer­den. Alles was man braucht, ist ein biss­chen VBA (Visual Basic for Appli­ca­ti­ons) und natür­lich das pas­sende SQL-State­ment. Wer VBA noch nie benutzt hat, sei einer­seits aus­drück­lich beglück­wünscht, darf ande­rer­seits auch unbe­sorgt blei­ben, da man letzt­end­lich nur den unten bereit­ge­stell­ten Code kopie­ren und das SQL nach sei­nen Wün­schen und Bedürf­nis­sen anpas­sen muss. Für die ganz Fau­len ist unter die­sem Link (yet to come) ein Excel abge­legt, in dem der unten vor­ge­stellt Code ent­hal­ten ist. 

Daten in Excel mit SQL abfragen

Quick Start

Gege­ben sei fol­gende Aus­gangs­si­tua­tion: Man hat eine Excel-Datei mit zu unter­su­chen­den Daten in einem Arbeits­blatt namens Data. Die Daten haben Über­schrif­ten und begin­nen ab Zelle A1. Man möchte die Daten per SQL abfra­gen bzw. aus­wer­ten und das Ergeb­nis in ein wei­te­res Arbeits­blatt namens Results schrei­ben. Um das tun zu kön­nen muss man ein neues Modul im VBA Edi­tor erstel­len und den unten gege­be­nen Code ein­fü­gen. Der VBA-Edi­tor lässt sich über die Tas­ten­kom­bi­na­tion Alt F11 öff­nen. Ein neues Modul kann über das Menü Insert -> Modul erstellt wer­den. In das neu erstellte Modul ist fol­gen­der 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.Connecion”)
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 aus­füh­ren. Beim Spei­chern des Excels ist dar­auf zu ach­ten, dass als Exten­sion xlsm oder bei gro­ßen Daten­men­gen bes­ser als xlsb gewählt wird, da sonst das Makro nicht mit gespei­chert wird. Im fol­gen­den Abschnitt wird näher erläu­tert, wie der Code funktioniert.

Erläu­te­rung des Codes

1. Varia­blen Deklaration 

Zu Beginn des Codes wer­den die benö­tig­ten Varia­blen defi­niert. Da ganz am Anfang die Option Expli­cit gesetzt wurde, müs­sen alle benutz­ten Varia­blen dekla­riert wer­den. Ist diese Option nicht gesetzt, wer­den alle nicht expli­zit dekla­rier­ten Varia­blen von VBA als Typ Vari­ant dekla­riert. Das ist aller­dings schlech­ter Stil, führt schnell zu dum­men Feh­lern (wenn man sich an einer Stelle bei einem Varia­blen­na­men ver­tippt, diese Varia­ble dann von VBA mit einem will­kür­li­chen Wert dekla­riert wird) und sollte daher ver­mie­den wer­den. Die bei­den Varia­blen start_row und start_col geben die Reihe und Spalte an, ab der das Ergeb­nis des SQLs im Blatt Results ein­ge­tra­gen wer­den soll und kön­nen nach Belie­ben ange­passt wer­den. Möchte man aller­dings spä­ter auch diese Daten wei­ter mit SQL aus­wer­ten, emp­fiehlt es sich, es bei Spalte und Reihe 1 zu belas­sen (siehe Ende des nächs­ten Punk­tes für die Erklärung).

2. Her­stel­len einer Ver­bin­dung zu den Daten, die im Excel gespei­chert sind 

Um eine Ver­bin­dung zu den Daten her­zu­stel­len, wird der ADODB-Trei­ber ver­wen­det. Dies ist der Trei­ber, der auch von Access für SQL Abfra­gen 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 State­ments in VBA füh­ren eine Reihe von Anwei­sun­gen aus, die wie­der­holt auf ein ein­zel­nes Objekt (hier die Con­nec­tion cn) ver­wei­sen. Der ange­ge­bene Pro­vi­der funk­tio­niert für alle Excel 2007 oder neuere Dateien. Im Con­nec­tion­String wird zum einen die Data Source defi­niert – in unse­rem Fall also die Excel Datei selbst (This­Work­book), wel­che über den Pfad und Namen ein­deu­tig defi­niert ist – zum ande­ren die Exten­ded Pro­per­ties. Diese beinhal­ten neben der Excel-Ver­si­ons­num­mer die Angabe, ob die Daten Hea­der haben (HDR=YES) oder nicht (HDR=NO). Eine wei­tere nütz­li­che Exten­ded Pro­perty ist IMAX=1. Diese führt dazu, dass Excel alle Daten als Text behan­delt. Wenn man Spal­ten mit gemisch­ten Daten­ty­pen hat, sollte man diese Option nuz­ten. Ohne diese Option ver­sucht Excel anhand der ers­ten acht Ein­träge in einer Spalte den Daten­typ die­ser Spalte zu erken­nen. Wählt man die Option mit Hea­dern, sind diese nicht direkt im spä­ter erstell­ten Record­set ent­hal­ten. Möchte man sie den­noch über den Ergeb­nis­spal­ten haben, kann man sie mit fol­gen­dem 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 Hea­der wird obi­ger Code-Block nicht benö­tigt. Die Spal­ten bekom­men in die­sem Fall von Excel die Default-Namen F1, F2 usw. zugewiesen.

Wei­tere Details zu Pro­vi­der und Con­nec­tion­String fin­det man hier (Abschnitt ‘Micro­soft ACE OLEDB 12.0’).

Damit die Ver­bin­dung zu den Daten rich­tig funk­tio­niert, ist Fol­gen­des zu beach­ten. Für Excel begin­nen die Daten bzw. Hea­der in der links-obers­ten nicht lee­ren Zelle. Begin­nen die Daten in A1, ist alles in Ord­nung, siehe 1a. Begin­nen die Daten in z.B. C5 und alle Zel­len in den ers­ten 5 Rei­hen der Spal­ten A und B sind leer ist auch noch alles in Ord­nung, siehe 1b. Sobald dann aber irgend­was in z.B. A1 steht, wer­den die Daten nicht rich­tig erkannt, siehe 1c.

3. Schrei­ben des SQLs 

Sobald eine Ver­bin­dung zu den Daten im Excel her­ge­sellt wurde, ist ein Arbeits­blatt äqui­va­lent zu einer Tabelle und kann als sol­che abge­fragt wer­den. Der Tabel­len­name eines Arbeits­blatts ist der Arbeits­blatt­name mit ange­häng­tem $ und umrahmt von ecki­gen Klam­mern [], z.B. Sheet1 -> [Sheet1$]. Wer gerne mit named Ran­ges in Excel arbei­tet, kann auch diese abfra­gen. Der Tabel­len­name einer named Range ist ein­fach der Name der named Range, z.B. SELECT * FROM MyNamedRange.

Wie oben erwähnt, wird für die Ver­bin­dung zu den Daten der glei­che Trei­ber wie von Micro­soft Access benutzt. Daher sollte es keine Über­ra­schung sein, dass es sich beim zu schrei­ben­den SQL um Access SQL han­delt. Dies führt lei­der zu (min­des­tens) zwei Unannehmlichkeiten:

a. Es gibt kei­nen full outer join. Um das glei­che Ergeb­nis zu erzie­len, muss man in Access SQL den recht umständ­li­chen Weg über einen left und einen right join mit anschlie­ßen­dem Union gehen.

b. Access SQL kann nur zwei Tabel­len auf ein­mal mit­ein­an­der joinen. Ver­sucht man also drei oder mehr Tabel­len mit­ein­an­der zu joinen, ist es wich­tig, die joins zu klam­mern, 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 wei­tere Unan­nehm­lich­keit kommt vom VBA Edi­tor selbst – neben dem SQL Edi­tor in Access, der wohl schlech­teste SQL Edi­tor aller Zei­ten. Da das SQL in dop­pel­ten Anfüh­rungs­zei­chen ste­hen muss, diese aber nicht über Zei­len gebro­chen wer­den kön­nen, muss man für Zei­len­um­brü­che auf den Line-con­ti­nua­tion (_) und den String-con­ti­nua­tion (& oder +) Cha­rac­ter zurück­grei­fen. Ein SQL kann also sche­ma­tisch so aussehen:

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

Aber Ach­tung: Die Anzahl der auf­ein­an­der­fol­gen­den line-con­ti­nua­tion cha­rac­ters ist auf 24 beschränkt. Erreicht man die­ses Limit, kann man das SQL aber ein­fach fol­gen­der­ma­ßen wei­ter­schrei­ben: SQL = SQL & "some more SQL code".

Man beachte dabei auch die Leer­zei­chen am Ende jeder Zeile, ohne die das SQL zu fol­gen­dem sinn­lo­sen String wer­den würde: “…,field3FROM [table1$]WHERE...".

4. Aus­füh­ren des SQLs 

Wenn man sein SQL fer­tig geschrie­ben hat, kann man es über den exe­cute-Befehl auf der erstell­ten Con­nec­tion aus­füh­ren. Das Ergeb­nis des SQL wird fol­gen­der­ma­ßen einem Record­set zugeordnet.

Set rs = cn.Execute(sql)

5. Ergeb­nis­da­ten des SQLs in ein Arbeits­blatt schreiben 

Die ein­fachste Methode, die Daten aus dem Record­set zu bekom­men, ist CopyFromRecordset.

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

Alter­na­tiv kann man auch über das Record­set loo­pen. Wäh­rend­des­sen könnte man dabei noch wei­tere Dinge mit den Daten anstel­len, aber es emp­fiehlt sich, alle nöti­gen Bereinigungen/Verarbeitungen der Daten im SQL zu machen, da dies in der Regel deut­lich schnel­ler 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-Funk­tion, die den Wert des hin­ter­ste­hen­den Argu­ments im Direkt-Fens­ter des VBA Edi­tors anzeigt. Das Direkt-Fens­ter kann über Strg G ein- bzw. aus­ge­blen­det werden.

6. Spei­cher­platz freigeben 

Am Ende wird das Record­set und die Con­nec­tion geschlos­sen (.close) und der Spei­cher­platz frei­ge­ge­ben (set ... = Nothing). Wenn man viele Record­sets ver­wen­det, emp­fiehlt es sich diese zu schlie­ßen und frei­zu­ge­ben, sobald man sie nicht mehr braucht, da der Code sonst sehr lang­sam wer­den kann.

Zusam­men­fas­sung

Wer viel mit SQL und wenig mit Excel arbei­tet, hat über obi­gen Code die Mög­lich­keit auch bei Daten­aus­wer­tun­gen in Excel auf bekann­tes Ter­rain zu bauen, ohne dabei tief­grei­fende VBA oder all­ge­meine Excel-Kennt­nisse haben zu müs­sen. Aber auch wer gut mit Excel umge­hen kann, wird diese Methode wegen ihrer Schnel­lig­keit zu schät­zen lernen