0 Einleitung
0.1 Providerprobleme
0.2 ADO.NET versus COM
0.3 Testdaten
1 SQL für Office/ Excel
1.1 Data Definition Language (DDL)
1.1.1 CREATE TABLE Statement
Beispiel 1: Hinzufügen von zwei Tabellen zu einer Arbeitsmappe
Beispiel 2: Bearbeiten der Tabellen in Excel mit dem Name Manager
1.1.2 DROP TABLE Statement
Beispiel 1: Löschen einer Tabelle
1.1.3 ALTER TABLE Statement
Beispiel 1: Hinzufügen und Entfernen von Spalten in Tabellen
1.2 Data Manipulation Language
Beispiel 1a: ausführlicher Skriptrahmen mit Alternativen
Beispiel 1b: schlankerer Skriptrahmen
1.2.1 SELECT Statement
Beispiel 1: Auswahl aller Felder
Beispiel 2: Eingrenzen auf bestimmte Felder
Beispiel 3: Auswahl der Felder mit Angabe des Tabellenblattes
Beispiel 4: Auswahl der Felder mit Angabe eines Aliasnamens des Tabellenblattes
1.2.2 FROM Clause
Beispiel 1: einfaches Auslesen von Werten aus einer Tabelle
Beispiel 2: Zugriff auf einen Bereich (Range) innerhalb eines Tabellenblattes
Beispiel 3: Zugriff auf einen Namen innerhalb einer Arbeitsmappe
1.2.3 WHERE Clause (Filtern)
Beispiel 1: Filtern nach genauen Zeichenfolgen
1.2.3.1 Filtern nach statischen Strings
Beispiel 1: Filtern nach verknüpften Strings (OR)
Beispiel 2: Filtern von Daten, die nciht auf das Suchkriterium passen (NOT)
1.2.3.2 Filtern nach Strings mit Platzhaltern (LIKE Operator)
Beispiel 1: Filtern mit Platzhaltern (%)
Beispiel 2: Filtern nach einem Buchstabenbereich
1.2.3.3 Filtern nach Zahlen
Beispiel 1: Suchen in einem Zahlenbereich (BETWEEN)
1.2.3.4 Zeit- und Datumsberechnungen
Beispiel 1: Daten eines bestimmten Zeitraums filtern
Beispiel 2: Daten, die ein bestimmtes Alter im Vergleich zu heute überschritten haben
Beispiel 3a: Filtern nach einem bestimmten Datum
1.2.4 INNER JOIN
1.2.5 SELECT...INTO Statement
2 Der Connectionstring
2.1 ExtendedProperties im ConnectionString
3 weitere nützliche Beispiele
Beispiel 1: Aufzählung der Tabellenblätter einer Excelmappe
Aufgrund seiner großen Verbreitung, der relativ einfachen Bedienung und den zahlreichen Darstellungsmöglichkeiten wird Excel gerne auch von IT-Profis als Datenablage benutzt, obwohl vielleicht Microsoft Access oder SQLServer objektiv die besseren und mit dem kostenlosen SQLServerExpress die preisgünstigeren Programme als Datenablage wären.
Da es mit ADO.NET und einem geeigneten Provider nicht besonders schwierig ist, Daten mit Excel auszutauschen, will ich auf diese Thematik hier näher eingehen.
Zur Einstimmung einige sehr gute Artikel, die beschreiben, wie man Daten mit ADO.Net aus Excel auslesen und nach Excel schreiben kann:
-
Microsoft Hilfe und Support:
-
Technet - Scripting Guy:
-
Technet - Scripting Guy:
-
Technet - Scripting Guy:
Solltet ihr beim Ausführen der Skripte dieses Kapitels folgende Fehlermeldung des Providers erhalten
"Der 'Microsoft.ACE.OLEDB.12.0'-Provider ist nicht auf dem lokalen Computer registriert."
"Der 'Microsoft.Jet.OLEDB.4.0'-Provider ist nicht auf dem lokalen Computer registriert."
Ausnahme beim Aufrufen von "Open" mit 0 Argument(en): "Der 'Microsoft.ACE.OLEDB.12.0'-Provider ist nicht auf dem lokalen Computer registriert." |
so seht bitte unter
Generell sind die Skripte dieses Kapitels immer etwas abhängig von
- der verwendeten Windowsarchitektur (32/64 bit)
- der verwendeten Office-Architektur (32/64 bit)
- der verwendeten Office-Sprachversion
- der verwendeten Office-Version (2003/ 2007/ 2010)
Excel lässt sich von der Powershell aus entweder über die ADO.NET Bibliothek oder über die COM-Automation ansprechen.
Auf die veraltete ADO-COM Schnittstelle gehe ich nicht näher ein.
ADO.NET
Die Klassen der ADO.NET Bilbliothek sind gut geeignet, wenn Excel wie eine Datenbank benutzt wird. Daten können mit SQLQueries aus Tabellenblättern abgefragt oder dort hinein geschrieben werden.
Die Vorteile von ADO.NET liegen bei größeren Datenmengen in der Performance und der standardisierten SqlSyntax.
COM-Automation
Excel bietet natürlich viel mehr als die reine Datenablage. Will man Excel über die Powershell eher administrieren oder designen (Tabellenblätter neu anlegen, Diagramme erstellen, vieles mehr), so kann man dies über die Excel-Automation erreichen.
Natürlich können auch hierüber Daten gelesen und geschrieben werden. Dies geschieht gezielt, indem die Zellen mit Angabe der Zeilen- und Spaltennunmmer genau adressiert werden.
siehe Kapitel:
Um sinnvolle Testdaten für meine Skripte zu haben, habe ich die 8 Tabellen der Nordwind.accdb aus Access in die Exceldatei "NordwindTables.xlsx" exportiert. Die Testarbeitsmappe sollte etwa so aussehen:
Die Datei "NordwindTables.xlsx" könnt ihr auch im Kapitel ADO.net am Ende unter Downloads herunterladen.
1 SQL für Office/ Excel
In diesem Kapitel möchte ich zeigen, wie man viele Aufgaben mit der SQL-Sprache im Zusammenpiel mit Excel lösen kann.
Zu beachten ist in diesem Kapitel, dass wir in Office nicht mit dem StandardSQL-Dialekt (=ANSI SQL) arbeiten, den der MSSQL-Server oder der MYSQL-Server anbieten, sondern mit einem etwas veränderten und abgespeckten Dialekt, den der OLEDB-Provider bereitstellt. Bis Office 2003 lief dieser Dialekt unter der Bezeichnung Jet-SQL, unter Office 2007 und 2010 unter der Bezeichnung "Microsoft Access SQL".
MSDN: Microsoft Jet SQL Reference (Office 2003)
MSDN: Microsoft Access SQL Reference (Office 2007)
MSDN: Microsoft Access SQL Reference (Office 2010)
Große Unterschiede sind mir zwischen den Versionen nicht aufgefallen. Ich habe die Beispiele dieses Kapitels unter Windows7 und mit Office2010 getestet. Alle Links, die in den folgenden Kapiteln auf die Referenz für Office 2010 verweisen, existieren auch für die älteren Versionen. Wenn es bei niedrigeren Office Version Probleme gibt, lohnt sich ein auf jeden Fall ein Blick in die Referenz.
Excel ist jedoch kein Datenbankprogramm(!), daher sind nicht alle Möglichkeiten, wie CREATE INDEX oder CREATE VIEW verfügbar.
Die Unterschiede von "ANSI SQL" und "Microsoft Access SQL" sind hier beschrieben:
MSDN:
In der Microsoft Access SQL Reference ist die SQL-Sprache aufgeteilt in
-
Data Definition Language
1.1 Data Definition Language -
Data Manipulation Language (=Data Query Language)
1.2 Data Manipulation Language
Office: Data Definition Language (Office 2003)
MSDN: Data Definition Language (Office 2010)
Für sämtliche Aufgaben, die mit dem Hinzufügen oder Auslesen von größeren Datenmengen nach oder aus Excel zu tun haben, ist ADO.NET mit dem OLE DB Provider meist besser geeignet, als ein Excelzugriff über OLE/ COM, vor allem wegen der Performance und den Möglichkeiten, die ein SQL-Query bietet.
Mit DDL lassen sich zusätzlich einige administrative Tätigkeiten in Excel mit erledigen. Dazu gehören
-
Tabellen anlegen
1.1.1 CREATE TABLE Statement -
Tabellen oder Spalten löschen
1.1.2 DROP TABLE Statement -
Verändern von Tabellen
1.1.3 ALTER TABLE Statement
MSDN: CREATE TABLE Statement (Microsoft Access SQL)
Man darf bei Excel in diesem Zusammenhang nicht Tabelle und Tabellenblatt verwechseln. Mit "CREATE TABLE" wird eine Tabelle angelegt, für die Excel dann automatisch -sofern nicht schon vorhanden- ein Tabellenblatt gleichen Namens mitanlegt, da in Excel ohne Tabellenblatt keine Tabelle existieren kann.
Im rechten Bild unter Beispiel 1 tauchen die angelegten Tabellen im Namenfeld (= das Auswahlfenster links oben in der Ecke eines Tabellenblattes) auf.
Beispiel 1a: Hinzufügen von zwei Tabellen zu einer Arbeitsmappe ohne Function
Set-StrictMode -Version "2.0" |
Beispiel 1b: Hinzufügen von zwei Tabellen zu einer Arbeitsmappe mit Function
Set-StrictMode -Version "2.0"
#Variablendefinition für die Excelarbeitsmappe und die Datenbank
#Function zum Zusammenbau des Connectionstrings |
Beispiel 1b hat exakt dieselbe Funktionalität und dasselbe Ergebnis wie Beispiel 1a. Durch die Verwendung einer Funktion, um den Verbindungsaufbau zu kapseln, ist das Skript leichter zu lesen.
Als Ergebnis beider Beispiele bekommt man eine Exceldatei mit zwei Arbeitsblättern und zwei Tabellen mit je zwei SpaltenAnmerkungen:
- Die für dieses Kapitel entscheidenden Codezeilen liegen in den Abschnitten #Anlage der ersten Tabelle und #Anlage der zweiten Tabelle
- Existiert die Arbeitsmappe bereits, so werden neue Arbeitsblätter in dieser Datei angelegt
- Existiert die Arbeitsmappe noch nicht, so wird diese erstellt
- die angelegten Tabellen (nicht Tabellenblätter) sind Bereiche, die in einem Excelblatt in dem Auswahlfenster links oben sichtbar sind (rechtes Bild)
- Die neu angelegten Tabellen kommen in Tabellenblätter gleichen Namens
- Um bei Feld- und Tabellennamen auch Leerzeichen verwenden zu können, muß der Wert in eckige Klammern [] eingeschlossen werden. Ohne Leerzeichen sind die Klammern nicht notwendig, schaden aber auch nicht. Excel erstezt das Leerzeichen durch einen Unterstrich.
- Möchte man in eine bestehende Exceldatei weitere Tabellen hinzufügen, so muss diese Datei geschlossen sein (sie darf nicht mit Excel geöffnet sein)
Beispiel 2: Bearbeiten der Tabellen in Excel mit dem Name Manager
Über den Namemanager im Menu "Formulas" kann man die in Beispiel 1 angelegten Tabellen innerhalb von Excel verwalten.
(Excel 2010)
MSDN:
Beispiel 1: Löschen einer Tabelle
Ich benutze hier die ExcelDatei mit den beiden Tabellen, die wir im letzten Kapitel 1.1.1 CREATE TABLE Statement in Beispiel 1a oder 1b angelegt haben, um den Löschvorgang und das Ergebnis zu zeigen
Set-StrictMode -Version "2.0"
#Function zum Zusammenbau des Connectionstrings |
Anmerkungen:
- Der entscheidende Codeabschnitt dieses Beispiels liegt im Abschnitt #Löschen der zweiten Tabelle.
Die Verbindungsherstellung mittels Provider mit der Function "CreateConnection01" ist identisch zu den Beispielen im oberen Kapitel 1.1.1
- wie im letzten Beispiel schon beschrieben, wirkt sich der Befehl auf die Tabelle (siehe Name Manager) aus, nicht auf das Tabellenblatt. Das Blatt bleibt nach dem DROP TABLE Statement bestehen, auch wenn es keine Tabelle mehr enthält.
- Die Exceldatei kann beim Ausführen dieses Skripts von Excel geöffnet sein
MSDN:
Mit diesem Statement kann eine bestehende Tabelle modifiziert werden.
In Excel beschränken sich sich Möglichkeiten von ALTER TABLE auf das Hinzufügen oder Löschen von Spalten in einer Tablelle.
Beispiel 1: Hinzufügen und Entfernen von Spalten in Tabellen
Set-StrictMode -Version "2.0" #Function zum Zusammenbau des Connectionstrings function CreateConnection01{ param ($FileName) $Provider = "Provider=Microsoft.ACE.OLEDB.12.0" #Excel 2007-2010 Workbook (.xlsx) $DataSource = "Data Source = $FileName" $Extend = "Extended Properties='Excel 12.0 XML;HDR=Yes'" $ConnectionString = "$Provider;$DataSource;$Extend" $XLConnection = New-Object System.Data.OleDb.OleDbConnection $ConnectionString $OleDbCmd = $XLConnection.CreateCommand() $XLConnection,$OleDbCmd } #Variablendefinition für die Excelarbeitsmappe und die Datenbank $FileName = "c:\temp\excel\test1.xlsx" $TableName2 = "[myTabelle2]" #Verbindung herstellen mit Funktionsaufruf $Connection = CreateConnection01 -Filename $Filename $XLConnection=$Connection[0] $OleDbCmd=$Connection[1] $XLConnection.Open() #Verändern der zweiten Tabelle $OleDbCmd.CommandText = "ALTER TABLE $TableName2 ADD COLUMN [Extra1] Char" $OleDbCmd.ExecuteNonQuery() $OleDbCmd.CommandText = "ALTER TABLE $TableName2 ADD COLUMN [Extra2] Char" $OleDbCmd.ExecuteNonQuery() #zwei Spalten hintereinander einfügen führt zum Absturz von PS2.0 #$OleDbCmd.CommandText = "ALTER TABLE $TableName2 DROP COLUMN [Spalte1]" #$OleDbCmd.ExecuteNonQuery() #PSV3.0 stürzt hier ab, siehe Anmerkungen #Schließen der Verbindung $XLConnection.Close() #getestet mit Office2010 und Win7 64bit/64bit #Beispiel 1: Hinzufügen und Entfernen von Spalten in Tabellen |
Anmerkungen:
- Der entscheidende Codeabschnitt dieses Beispiels liegt im Abschnitt #Verändern der zweiten Tabelle. In "myTabelle2" sollen die Felder [Extra1] und [Extra2] hinzugefügt und das Feld "Spalte1" gelöscht werden
- Powershell v2.0 ist bei diesem Beispiel ungewöhnlich empfindlich. Fügt man 2 Spalten innerhalb eines Skriptes hinzu, oder ist die Exceldatei nicht geöffnet, so stürzt bei mir Powershell reproduzierbar ab. In Powershell V3.0 ist das Verhalten etwas abgemildert, aber dennoch vorhanden. Powershell V3.0 stürzt beispielsweise ab, wenn die Zusatzfelder bereits vorhanden sind, oder wenn man zuerst die Felder [Extra1] und [Extra2} hinzufügt und anschließend im gleichen Skript das Feld [Spalte1] löscht.
Tipp: Führt mehrere "ALTER TABLE ..." Anweisungen in getrennten Skripten aus
MSDN: Data Manipulation Language (Office 2010)
Der Aufbau der folgenden Beispiele bleibt immer gleich. Um das Kapitel übersichtlich zu halten, zeige ich hier zu Beginn den immer gleich bleibenden Rahmen und beschränke mich danach nur noch auf die wenigen interessanten Codezeilen, die sich verändern.
Beispiel 1a: ausführlicher Skriptrahmen mit Alternativen
Zur Ausführung dieses Beispiels benötigt ihr die in Kapitel 0.3 Testdaten erstellte Beispieldatei "NordwindTables.xlsx", mit dem Tabellenblatt "Artikel". Sonst könnt ihr im Skript natürlich auch die Variablen $FileName und $TableSheetName auf Eure eigene Testdatei legen.
Das Skript liest alle Daten aus der Tabelle Artikel in Tabellenblatt "Artikel"
Set-StrictMode -Version "2.0" #### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select * from [$TableSheetName$]" ### Ende des DML Bereichs $Provider = "Provider=Microsoft.ACE.OLEDB.12.0" #$Provider = "Provider=Microsoft.Jet.OLEDB.4.0" # nur für 32-Bit OfficeVersionen $DataSource = "Data Source = $FileName" $Extend = "Extended Properties='Excel 12.0 XML;HDR=Yes'" $XLConnection = New-Object System.Data.OleDb.OleDbConnection("$Provider;$DataSource;$Extend") #Alternative1 zur Erzeugung des OleDBCommands $OleDbCmd = $XLConnection.CreateCommand() $OleDbCmd.CommandText=$Query #Alternative2 zur Erzeugung des OleDBCommands. Entfernt vorher die Kommentarflags "<#" und "#>" <#$OleDbCmd = New-Object System.Data.OleDb.OleDbCommand($Query) $OleDbCmd.Connection = $XLConnection #> $XLConnection.Open() $OleDbDataReader = $OleDbCmd.ExecuteReader() #einfache Ausgabe While($OleDbDataReader.Read()) { #"{0,-30} {1} " -f $OleDbDataReader[0],$OleDbDataReader[1] $Out="" for ($i=0;$i -lt $OleDbDataReader.FieldCount; $i++){ $Out += $OleDbDataReader[$i].tostring() + ";" } $Out } $OleDbDataReader.Close() $XLConnection.Close() #getestet mit Office2010 und Win7 64bit/64bit #Beispiel 1a: ausführlicher Skriptrahmen mit Alternativen |
#Ausgabe #siehe Beispiel 1b |
Beispiel 1b: schlankerer Skriptrahmen
Dieses Beispiel verarbeitet denselben Code wie Beispiel 1a. Ich habe nur die möglichen Alternativen entfernt, die in 1a zur Information enthalten sind.
Set-StrictMode -Version "2.0"
#Spaltenüberschriften |
#Ausgabe gekürzt
1;Chai;1;1;10 Kartons x 20 Beutel;9;39;0;10;False; |
Beispiel 1b hat exakt dieselbe Funktionalität und dasselbe Ergebnis wie Beispiel 1a. Durch die Verwendung einer Funktion, um den Verbindungsaufbau zu kapseln, ist das Skript leichter zu lesen.
Wenn ihr die folgenden Beispiele nachvollziehen wollt, nehmt das Beispiel 1b, und tauscht den Bereich zu Beginn des Skripts
#### Bereich der Data Manipulation Language (DML)
...
### Ende des DML Bereichs
aus. Ich lasse den unteren Bereich in den folgenden Skripten aus Gründen der Übersichtlichkeit hier auf der Website weg.
Als Beispieldatenquelle benutze ich, wenn nicht anders beschrieben, die in
1.2.1 SELECT Statement
MSDN: SELECT Statement (Microsoft Access SQL) "Instructs the Microsoft Jet database engine to return information from the database as a set of records."
Mit SELECT wird definiert, welche Felder (in Excel sind das Spalten) aus einer oder mehreren Exceltabellen herausgelesen werden sollen. Im einfachsten und häufigsten Fall sollen es alle verfügbaren Daten aus einer Quelle sein: SELECT * FROM
Die allgemeine Syntax des SELECT Statements lautet: "SELECT felder FROM tabelle"
Beispiel 1: Auswahl aller Felder
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select * from [$TableSheetName$]" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel
#Ausgabe gekürzt 1;Chai;1;1;10 Kartons x 20 Beutel;9;39;0;10;False; 2;Chang;1;1;24 x 12-oz-Flaschen;9,5;17;40;25;False; 3;Aniseed Syrup;1;2;12 x 550-ml-Flaschen;5;13;70;25;False; |
Beispiel 2: Eingrenzen auf bestimmte Felder
Die bequemste Abfrage ist der "*" im letzten Beispiel. Ähnlich wie bei WMI siehe
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select [Artikel-Nr],[ArtikelName],[Einzelpreis] From [$TableSheetName$]" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel
#Ausgabe gekürzt 1;Chai;9; 2;Chang;9,5; 3;Aniseed Syrup;5; |
Hier wird die Ausgabe auf die Spalten [Artikel-Nr],[ArtikelName] und [Einzelpreis] beschränkt.
Beispiel 3: Auswahl der Felder mit Angabe des Tabellenblattes
Werden mit INNER JOIN (siehe weiter unter) mehrere Tabellen verknüpft, muss man den Namen der Tabellenblätter mitangeben. In diesem Beispiel, bei dem die Daten nur aus einem Tabellenblatt gezogen werden, dient der Tabellenname [$TableSheetName] nur der Anschauung.
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select [$TableSheetName$].[Artikelname],[$TableSheetName$].[Einzelpreis] From [$TableSheetName$]" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel
#Ausgabe gekürzt Chai;9; Chang;9,5; Aniseed Syrup;5; |
Beispiel 4: Auswahl der Felder mit Angabe eines Aliasnamens des Tabellenblattes
Umfangreichere Queries über mehrere Tabellen und Felder können ziemlich lang werden. SQL bietet eine einfache Möglichkeit beispielsweise den Code aus Beispiel 3 mit Aliasen zu verkürzen.
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select A.[Artikelname],A.[Einzelpreis] From $TableSheetName A" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel
#Ausgabe identisch wie Beispiel 3 |
Technet: Textfunktionen (DAX)
Technet: FORMAT-Funktion (DAX)
"Data Analysis Expression (DAX) umfasst einen Satz von Textfunktionen, die auf der Bibliothek von Zeichenfolgenfunktionen in Excel basieren, jedoch für die Unterstützung von Tabellen und Spalten modifiziert wurden."
Anbei einige Beispiele, wie die aufbereiteten Daten noch weiter formatiert werden können.
Beispiel 1: Vordefinierte numerische Formate
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select Artikelname, Format (Einzelpreis,'Currency') From [$TableSheetName] Where Einzelpreis BETWEEN 5 AND 15" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel
#Ausgabe gekürzt
Chai;9,00 €; |
Neben 'Currency' sind die weiteren vordefinierten Argumente der FORMAT-Funktion für numerische Werte. "GeneralNumber","Currency","Fixed","Standard","Percent","Scientific","Yes/No","True/False","On/Off"
Die Bedeutung dieser Argumente findet ihr unter:
Beispiel 2: Benutzerdefinierte Datums- und Uhrzeitformate
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Bestellungen" $Query = "Select [Bestell-Nr], Format (Bestelldatum,'dddd, \der dd.mm.yyyy') From [$TableSheetName]" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel
#Ausgabe gekürzt 10887;Freitag, der 13.02.1998; 10888;Montag, der 16.02.1998; 10889;Montag, der 16.02.1998; |
In der Tabelle Technet: Benutzerdefinierte Datums- und Uhrzeitformate für die FORMAT-Funktion (DAX) sind alle Zeichen aufgeführt, mit denen benutzerdefinierte Datums- und Uhrzeitformate erstellen können.
In der Formatierung 'dddd, \der dd.mm.yyyy' steht
- dddd für den ausgeschriebenen Wochentag (Montag, Dienstag,...)
- dd.mm.yyyy für die Datumschreibweise wie beispielsweise 13.02.1998
- /der für einen eingefügten zusätzlichen String. Der Slash (/) vor dem d von der maskiert den Charakter "d", da er sonst als Zeichen für den Tag interpretiert wird.
Beispiel 3: Vordefinierte Datums- und Uhrzeitformate
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Bestellungen" $Query = "Select [Bestell-Nr], Format (Bestelldatum,'Long Date') From [$TableSheetName]" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment
#Ausgabe gekürzt 11060;Donnerstag, 30. April 1998; 11061;Donnerstag, 30. April 1998; 11062;Donnerstag, 30. April 1998; |
In der Tabelle unter Technet: Vordefinierte Datums- und Uhrzeitformate für die FORMAT-Funktion (DAX) sind alle vordefinierten Argumente aufgeführt, um Datums- und Uhrzeitformate zu erstellen.
Beispiel 4: Umwandlung der Ausgabe in Groß- oder Kleinbuchstaben
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Kunden" $Query = "SELECT FORMAT(Firma,'<') FROM [$TableSheetName]" #$Query = "SELECT FORMAT(Firma,'>') FROM [$TableSheetName]" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel
#Ausgabe gekürzt alfreds futterkiste; ana trujillo emparedados y helados; antonio moreno taquería; |
Alternativ funktionieren auch
$Query = "SELECT LCASE(Firma) ...
und
$Query = "SELECT UCASE(Firma)....
Beispiel 1: Einige Funktionen zur Textmanipulation
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "SELECT LCASE(Liefereinheit), UCASE(Liefereinheit), LEN(Liefereinheit), LEFT(Liefereinheit,3) From [$TableSheetName] Where Einzelpreis BETWEEN 5 AND 15" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment
#Ausgabe gekürzt
10 kartons x 20 beutel;10 KARTONS X 20 BEUTEL;22;10 ; |
Ich habe die Funktionen aus Technet: Textfunktionen (DAX) probiert. Einige dieser DAX-Funktionen haben problemlos funktioniert (siehe Beispiel), andere Funktionen leider nicht.
MSDN: ALL, DISTINCT, DISTINCTROW, TOP Predicates (Microsoft Access SQL)
Über Predicates können einige interessante Aktionen sehr einfach an unseren Excel Tabellen durchgeführt werden.
SELECT ALL * ist äquivalent zu SELECT *
SELECT TOP 10 [PERCENT]: Mit TOP (n) können die n-obersten Treffer aus allen Treffern gefiltert werden.
DISTINCT: Distinct erlaubt es, Datensätze, bei denen ein oder mehrere Felder identisch sind auszuschließen.
DISTINCTROW: Distinct erlaubt es, Datensätze, bei denen alle Felder identische Werte enthalten, auszuschließen
Beispiel 1a: Die ersten 3 aufsteigend sortierten Datensätze finden
Dieses Beispiel findet die drei preislich niedrigsten Produkte der Tabelle Artikel
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select TOP 3 [ArtikelName],Format([Einzelpreis],'Currency') From [$TableSheetName$] ORDER BY [Einzelpreis];" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment
#Ausgabe Geitost;1,25 €; Guaraná Fantástica;2,25 €; Konbu;3,00 €; |
Beispiel 1b: Die letzten 3 aufsteigend sortierten Datensätze finden (DESC)
Dieses Beispiel findet die drei preislich teuersten Produkte der Tabelle Artikel
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select TOP 3 [ArtikelName],Format([Einzelpreis],'Currency') From [$TableSheetName$] ORDER BY [Einzelpreis] DESC;" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment
#Ausgabe Côte de Blaye;131,75 €; Thüringer Rostbratwurst;61,90 €; Mishi Kobe Niku;48,50 €; |
Beispiel 2: Ausgabe ohne doppelte Datensätze (DISTINCTROW)
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select DISTINCTROW * From [$TableSheetName$]" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment
MSDN: FROM Clause (Microsoft Access SQL)
Das SQL-Befehlswort "FROM" definiert generell, woher die Daten kommen. Bei einer Datenabfrage gegen eine Excelarbeitsmappe kann dieses "FROM"
- eine relativ ungenaue Angabe des Tabellenblattes (=Sheetname) sein (Beispiel 1)
- wine Angabe des Tabellenblattes (=Sheetname) zusammen mit den Zellen (=Range) sein (Beispiel 2)
- die Angabe eines innerhalb der Arbeitsmappe definierten Namens sein (Beispiel 3)
Beispiel 1: einfaches Auslesen von Werten aus einer Tabelle
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select * From [$TableSheetName$]" ### Ende des DML Bereichs |
Dies ist der Block, der bereits im Defaultskript in Beispiel 1b im Kapitel
Anmerkungen:
- Excel sucht selbst einen passenden Tabellenbereich innerhalb des Tabellenblattes aus. Das Beispiel funktioniert auch, wenn die Tabelle nicht in der Zellen A1 beginnt, sondern irgendwo inmitten des Blattes aufgehängt ist. Zur Sicherheit sollte man aber besser den Ort der gewünschter Tabelle genauer spezifizieren, wie dies in den folgenden Beispielen gezeigt ist.
-
$Query = "Select * from [$SheetName$]" : Das hintere $-Zeichen bedeutet, dass $SheetName bereits existiert. In
Kapitel 2 gibt es einige Beispiele, in den Tabellenblätter erst erstellt werden. Dort fehlt das hintere $-Zeichen.
Beispiel 2: Zugriff auf einen Bereich (Range) innerhalb eines Tabellenblattes
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Range="A1:C3" $Query = "Select * from [$TableSheetName`$$Range]" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel
#Ausgabe vollständig 1;Chai;1; 2;Chang;1; |
Anmerkungen:
- Mit der zusätzlichen Angabe des Ranges können sowohl die ausgelesenen Spalten (Felder), wie auch die Zeilen (Datensätze) eingeschränkt werden
- Falls in einem Tabellenblatt mehrere Tabellen liegen, kann mit der Angabe des Ranges die gewünschte Tabelle exakt angesprochen werden
$NewRange="F14:J83"
$Query = "Select * from [$TableName`$$NewRange]"
Beispiel 3: Zugriff auf einen Namen innerhalb einer Arbeitsmappe
Innerhalb einer Excelmappe können einzelne oder mehrere Zellen mit einem Namen defniert und angesprochen werden. In Kapitel
Auf diese Namen lässt sich ganz besonders einfach zugreifen:
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" #$TableSheetName = "Artikel" #entfällt $RangeName="MyTabelle1" $Query = "Select * from $RangeName" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment
Anmerkungen:
MSDN: WHERE Clause (Microsoft Access SQL)
Mit WHERE kann man die zurückgegebenen Daten auf verschiedenen Arten vor der Ausgabe filtern. Einige Beispiele findet ihr in dem eben genannten Link.
Beispiel 1: Filtern nach verknüpften Strings (OR)
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Kunden" $Query = "Select Firma,Kontaktperson,Land From [$TableSheetName] Where (Land='Deutschland') OR (Land='Kanada') OR (LAND ='Mexiko')" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment
#Ausgabe gekürzt Alfreds Futterkiste;Maria Anders;Deutschland; Ana Trujillo Emparedados y helados;Ana Trujillo;Mexiko; Antonio Moreno Taquería;Antonio Moreno;Mexiko; Blauer See Delikatessen;Hanna Moos;Deutschland; Bottom-Dollar Markets;Elizabeth Lincoln;Kanada; |
Falls nur ein Feld als Kriterium benutzt wird, kann mann anstelle der etwas aufwändingen OR - Verknüpfung IN verwenden, was im nächsten Beispiel 2 gezeigt wird.
Beispiel 2: Filternvon Daten, die nicht auf das Suchkriterium passen (NOT)
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Kunden" $Query = "Select Firma,Kontaktperson,Land From [$TableSheetName] Where NOT(Land IN ('Deutschland','Kanada','Mexiko'))" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment
#Ausgabe gekürzt Around the Horn;Thomas Hardy;Großbritannien; Berglunds snabbköp;Christina Berglund;Schweden; Blondel père et fils;Frédérique Citeaux;Frankreich; |
Mehrere Begriffe können mit der IN-Klausel oder wie in Beispiel 2 mit OR verknüpft werden.
Office: Like Operator
Über den Like Operator unterstützt Jet-SQL mehrere Platzhalter. Ein einzelnes Zeichen wird mit dem Unterstrich "_", eine beliebige Anzahl von Zeichen mit dem Prozentzeichen "%" symbolisiert. Auch Kombinationen sind möglich
In der Tabelle am Ende des Links, ist die Syntax zusammengefasst. Allerdings habe ich das Gefühl, dass Microsoft hier die Zeichen "*" und "%", sowie "?" und "_" verwechselt hat.
"*" und "?" funktionieren jedenfalls in meinen Beispielen nicht. Tauscht man die beiden Zeichen in der Tabelle aus, hat man dennoch einen guten Überblick.
Beispiel 1: Filtern mit Platzhaltern (%)
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select * From [$TableSheetName] Where Artikelname Like 'C%' " ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein.
#Ausgabe gekürzt 1;Chai;1;1;10 Kartons x 20 Beutel;9;39;0;10;False; 2;Chang;1;1;24 x 12-oz-Flaschen;9,5;17;40;25;False; 4;Chef Anton's Cajun Seasoning;2;2;48 x 6-oz-Gläser;11;;0;0;False; |
Es werden alle Datensätze zurückgegeben, deren Artikelname mit einem "C" beginnt.
Wie erwähnt, mit dem Stern anstatt des Prozentzeichens "Where Artikelname Like 'C*' " funktioniert mein Beispiel nicht (PS 2.0 / PS 3.0)
Beispiel 2: Filtern nach einem Buchstabenbereich
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select * From [$TableSheetName] Where Artikelname Like '[C-E]%' " ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein.
#Ausgabe gekürzt 1;Chai;1;1;10 Kartons x 20 Beutel;9;39;0;10;False; 2;Chang;1;1;24 x 12-oz-Flaschen;9,5;17;40;25;False; 58;Escargots de Bourgogne;27;8;24 Stück;6,625;62;0;20;False; |
Beispiel 1: Suchen in einem Zahlenbereich (BETWEEN)
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Artikel" $Query = "Select Artikelname, Einzelpreis From [$TableSheetName] Where Einzelpreis BETWEEN 5 AND 15" $Alternative_Query = "Select Artikelname, Einzelpreis From [$TableSheetName] Where (Einzelpreis >=5) AND (Einzelpreis <=15)" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein.
#Ausgabe gekürzt Chai;9; Chang;9,5; Aniseed Syrup;5; |
Ein erster Fallstrick ist der Unterschied zwischen dem deutschen und dem US-Format. Ihr könnt in den folgenden Beispielen beide Schreibweisen wählen, beachtet aber dass
Datevalue('10.1.1998') für den 10. Januar 1998
DateValue(#10/1/1998#) für den 1. Oktober 1998
steht.
Die zweite Falle lauert im Vergleich von Datumswerten als Bedingung. Manchmal, aber manchmal eben auch nicht, enthalten Datumswerte in der Datenquelle neben dem Datum zusätzlich eine Uhrzeit, die nur nicht dargestellt wird.
Ein Vergleich von '10.1.1998 14:30' mit '10.1.1998' liefert 'false', was möglicherweise nicht das erwartete Ergebnis ist.
Diese Falle behandle ich in Beispiel 3 genauer.
Beispiel 1: Daten eines bestimmten Zeitraums filtern
Diese Query listet alle Bestellungen auf, deren Bestelldatum ab dem 1.1.1998 (inklusiv) und vor dem 10.1.1998 (exclusiv ) liegt
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Bestellungen" $Query = "Select [Bestell-Nr], Format (Bestelldatum,'dd.mm.yyyy') From [$TableSheetName] Where (Bestelldatum >= Datevalue('1.1.1998')) AND (Bestelldatum < Datevalue('10.1.1998'))" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein.
#Ausgabe gekürzt 10808;01.01.1998; 10809;01.01.1998; 10810;01.01.1998; |
Beispiel 2: Daten, die ein bestimmtes Alter im Vergleich zu heute überschritten haben
Diese Query listet alle Bestellungen auf, deren Bestelldatum älter als 15 Jahre im Vergleich zu heute (now) sind.
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Bestellungen" $Query = "Select [Bestell-Nr], Format (Bestelldatum,'dd.mm.yyyy') From [$TableSheetName] Where DateDiff('yyyy',Bestelldatum, Now) > 15" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein.
Die möglichen Werte in der Datediff-Funktion und in anderen Zeitfunktionen findet ihr in der Tabelle unter MSDN: DATEDIFF (Transact-SQL)
Beispiel 3: Filtern nach einem bestimmten Datum
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $TableSheetName = "Bestellungen" $Query = "Select [Bestell-Nr], Format (Bestelldatum,'dd.mm.yyyy') From [$TableSheetName] Where (Bestelldatum = Datevalue('1.1.1998'))" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein.
#Ausgabe gekürzt 10808;01.01.1998; 10809;01.01.1998; 10810;01.01.1998; |
Das Ergebnis besteht wie erwartet aus den 3 Datensätzen der Tabelle Bestellungen, die das Bestelldatum vom 1. Januar 1998 tragen.
Allerdings ist dieses Ergebnis trügerisch, da wir in diesem Beispiel das Glück haben, das das Bestelldatum ohne Uhrzeit gespeichert wurde. Daher gibt der Vergleich in
Where (Bestelldatum = Datevalue('1.1.1998'))" ein "True" zurück und die Datensätze werden angezeigt.
MSDN: INNER JOIN Operation (Microsoft Access SQL)
Das folgende SQL-Statement habe ich mir in Access erzeugt und konvertiere es im folgenden nach Powershell
SELECT Artikel.[Artikel-Nr], Artikel.Artikelname, Lieferanten.Kontaktperson
FROM Lieferanten INNER JOIN Artikel ON Lieferanten.[Lieferanten-Nr] = Artikel.[Lieferanten-Nr];
#### Bereich der Data Manipulation Language (DML) $FileName = "c:\temp\excel\NordwindTables.xlsx" $Sheet1 = 'Artikel' $Sheet2 = 'Lieferanten' $Query = "Select [$Sheet1].[Artikel-Nr],[$Sheet1].[Artikelname],[$Sheet2].[Kontaktperson] From [$Sheet2] INNER JOIN [$Sheet1] ON [$Sheet2].[Lieferanten-Nr] = [$Sheet1].[Lieferanten-Nr]" ### Ende des DML Bereichs |
Zum Nachvollziehen fügt diesen Block bitte in Beispiel 1b im Kapitel 1.2 Data Manipulation Language ein.
#Ausgabe gekürzt 2;Chang;Charlotte Cooper; 1;Chai;Charlotte Cooper; 3;Aniseed Syrup;Charlotte Cooper; |
Mit "INNER JOIN" lassen sich Tabellen miteinander verknüpfen.
Office: SELECT...INTO Statement
"Creates a make-table query"
Mit dem SELECT...INTO Statement können Tabellen angelegt oder Daten aus dem Recordset einer bestehenden Query in eine Tabelle geschrieben werden.
Beispiel 1: Schreiben von Werten in eine Exceltabelle
Für dieses Beispiel muss unter C:\temp\ die Exceldatei "test.xlsx" existieren. Diese Datei sollte ein Tabellenblatt mit dem Namen "Tabelle1" besitzen, sowie in dieser Tabelle die Spaltenüberschriften "Feld1" bis "Feld3".
Eine solche Arbeitsmappe kann man manuell oder über ein Skript ( siehe
Clear-Host Set-StrictMode -Version "2.0" $FileName = "C:\temp\test.xlsx" $SheetName = "Tabelle1" $Provider = "Provider=Microsoft.ACE.OLEDB.12.0" $DataSource = "Data Source ="+$FileName $Extend = "Extended Properties=Excel 12.0" $Query = "Insert into [$SheetName$] (Feld1,Feld2,Feld3,Feld4) Values (?,?,?,?)" $OleDbConnection = New-Object System.Data.OleDb.OleDbConnection("$Provider;$DataSource;$Extend") $OleDbCommand = New-Object System.Data.OleDb.OleDbCommand($Query) $OleDbCommand.Connection = $OleDbConnection $Feld1Parameter = $OleDbCommand.parameters.add("Feld1","Integer",16) $Feld2Parameter = $OleDbCommand.parameters.add("Feld2","VarChar",50) $Feld3Parameter = $OleDbCommand.parameters.add("Feld3","Varchar",50) $Feld4Parameter = $OleDbCommand.parameters.add("Feld4","Varchar",50) $OleDbConnection.open() For($i=1;$i -le 3;$i++) { $Feld1Parameter.value = $i $Feld2Parameter.value = "Karl Napf" $Feld3Parameter.value = (get-date).ToLongTimeString() $Feld4Parameter.value = (get-date).ToLongDateString() $returnValue = $OleDbCommand.ExecuteNonQuery() } $OleDbConnection.close() |
#Ausgabe
MSDN: OleDbParameter-Konstruktor (String, OleDbType, Int32)
MSDN: OleDbType Enumeration oder mit [enum]::GetValues("System.Data.OleDb.OleDbType")
Die richtigen Versionsnummern für die extenden Properities sind abhängig von der verwendeten Officeversion
File Type (extension) Extended Properties
---------------------------------------------------------------------------------------------
Excel 97-2003 Workbook (.xls) "Excel 8.0"
Excel 2007-2010 Workbook (.xlsx) "Excel 12.0 Xml"
Excel 2007-2010 Macro-enabled workbook (.xlsm) "Excel 12.0 Macro"
Excel 2007-2010 Non-XML binary workbook (.xlsb) "Excel 12.0"
Quelle: http://www.microsoft.com/en-us/download/details.aspx?id=13255
Beispiel 1: Aufzählung der Tabellenblätter einer Excelmappe
$FileName = "C:\temp\test.xlsx" $ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$($FileName)`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"; $XLConnection = New-Object System.Data.OleDb.OleDbConnection $ConnectionString $XLConnection.Open(); $Tabellen = $XLConnection.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::Tables,$null) $XLConnection.Close(); $XLConnection.Dispose(); $Tabellen | Format-List Table_Name |
#mögliche Ausgabe TABLE_NAME : Sheet1$ TABLE_NAME : Sheet2$ TABLE_NAME : Sheet3$ |
Quelle: stackoverflow.com/questions/10464386/listing-excel-tables-with-oledbconnectiongetoledbschematable
4 Attachments