0 Einleitung

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: How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
Technet - Scripting Guy: How Can I Read from Excel Without Using Excel?
Technet - Scripting Guy: How Can I Write to Excel Without Using Excel?
Technet - Scripting Guy: Event 2 *Solutions* from Expert Commentators (Beginner and Advanced; the long jump) Die PS-Ausführungen von Ed Wilson

0.1 Providerprobleme

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."
Bei Zeile:19 Zeichen:19
+ $XLConnection.Open <<<< ()
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
.....

so seht bitte unter Access -> 0.1 fehlende Provider / Installation und Registrierung von Providern nach der Lösung

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)

0.2 ADO.NET versus COM

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: Excel (COM Automation)

0.3 Testdaten

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: Comparison of Microsoft Access SQL and ANSI SQL (Office 2010)

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

1.1 Data Definition Language (DDL)

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

1.1.1 CREATE 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"

#Variablendefinition für die Excelarbeitsmappe und die Datenbank
$FileName = "c:\temp\excel\test120.xlsx"
$TableName1 = "[myTabelle1]"
$TableName2 = "[myTabelle2]"
$FieldName1 = "[Spalte1]"
$FieldName2 = "[Spalte2]"

#Zusammenbau des ConnectionStrings
$Provider = "Provider=Microsoft.ACE.OLEDB.12.0" #Excel 2007-2010 Workbook (.xlsx)
#$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'"
#$Extend = "Extended Properties=Excel 8.0" #Excel 97-2003 Workbook (.xls)
$ConnectionString = "$Provider;$DataSource;$Extend"

#Herstellen der Verbindung
$XLConnection = New-Object System.Data.OleDb.OleDbConnection $ConnectionString
$OleDbCmd = $XLConnection.CreateCommand()
$XLConnection.Open()

#Anlage der ersten Tabelle
$OleDbCmd.CommandText = "CREATE TABLE $TableName1 ($FieldName1 Char,$FieldName2 Char)"
$OleDbCmd.ExecuteNonQuery()

#Anlage der zweiten Tabelle
$OleDbCmd.CommandText = "CREATE TABLE $TableName2 ($FieldName1 Char,$FieldName2 Char)"
$OleDbCmd.ExecuteNonQuery()

#Schließen der Verbindung
$XLConnection.Close()

#getestet mit Office2010 und Win7 64bit/64bit
#Beispiel 1a: Hinzufügen von zwei Tabellen zu einer Arbeitsmappe ohne Function

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
$FileName = "c:\temp\excel\test305.xlsx"
$TableName1 = "[myTabelle1]"
$TableName2 = "[myTabelle2]"
$FieldName1 = "[Spalte1]"
$FieldName2 = "[Spalte2]"

#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
}

#Verbindung herstellen mit Funktionsaufruf
$Connection = CreateConnection01 -Filename $Filename
$XLConnection=$Connection[0]
$OleDbCmd=$Connection[1]
$XLConnection.Open()

#Anlage der ersten Tabelle
$OleDbCmd.CommandText = "CREATE TABLE $TableName1 ($FieldName1 Char,$FieldName2 Char)"
$OleDbCmd.ExecuteNonQuery()

#Anlage der zweiten Tabelle
$OleDbCmd.CommandText = "CREATE TABLE $TableName2 ($FieldName1 Char,$FieldName2 Char)"
$OleDbCmd.ExecuteNonQuery()

#Schließen der Verbindung
$XLConnection.Close()

#getestet mit Office2010 und Win7 64bit/64bit
#Beispiel 1b: Hinzufügen von zwei Tabellen zu einer Arbeitsmappe mit Function

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)

1.1.2 DROP TABLE Statement

MSDN: DROP Statement (Microsoft Access SQL)

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"

#Variablendefinition für die Excelarbeitsmappe und die Datenbank
$FileName = "c:\temp\excel\test01.xlsx"
$TableName2 = "[myTabelle2]"

#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
}

#Verbindung herstellen
$Connection = CreateConnection01 -Filename $Filename
$XLConnection=$Connection[0]
$OleDbCmd=$Connection[1]
$XLConnection.Open()

#Löschen der zweiten Tabelle
$OleDbCmd.CommandText = "DROP TABLE $TableName2"
$OleDbCmd.ExecuteNonQuery()

#Schließen der Verbindugn
$XLConnection.Close()

#getestet mit Office2010 und Win7 64bit/64bit
#Beispiel 1: Löschen einer Tabelle

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

1.1.3 ALTER TABLE Statement

MSDN: ALTER TABLE Statement (Microsoft Access SQL)

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

1.2 Data Manipulation Language

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"

#### Bereich der Data Manipulation Language (DML)
$FileName = "c:\temp\excel\NordwindTables.xlsx"
$TableSheetName = "Artikel"
$Query = "Select * from [$TableSheetName$]"
### Ende des DML Bereichs

#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
}

#Verbindung herstellen mit Funktionsaufruf
$Connection = CreateConnection01 -Filename $Filename
$XLConnection=$Connection[0]
$OleDbCmd=$Connection[1]
$XLConnection.Open()

$OleDbCmd.CommandText=$Query
$OleDbDataReader = $OleDbCmd.ExecuteReader()

#einfache Ausgabe

#Spaltenüberschriften
<#
$Headers=""
for($i=0;$i -lt $OleDbDataReader.FieldCount;$i++){
   $Headers+=$OleDbDataReader.GetName($i) + ";"
   }
   $Headers
#>

While($OleDbDataReader.Read())
{
$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 1b: schlankerer Skriptrahmen

#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;
4;Chef Anton's Cajun Seasoning;2;2;48 x 6-oz-Gläser;11;0;0;0;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 Kapitel 0.3 Testdaten beschriebene Beispieldatei mit den Daten aus der Nordwinddatenbank.

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 1.2 Data Manipulation Language ein, wobei dieses Beispiel schon im Skript oben enthalten ist

#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 WMI -> 2.3 WMI-Abfragen optimieren, lassen sich Abfragen im Bezug auf Performance oder Netzwerklast optimieren, wenn nur die tatsächlich benötigten Daten aus der Quelle abgezogen werden.

#### 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 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels

#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 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels

#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 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels

#Ausgabe identisch wie Beispiel 3

1.2.1.1 FORMAT-Funktion (DAX)

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 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels

#Ausgabe gekürzt

Chai;9,00 €;
Chang;9,50 €;
Aniseed Syrup;5,00 €;
Chef Anton's Cajun Seasoning;11,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: Technet: Vordefinierte numerische Formate für die FORMAT-Funktion (DAX)

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 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels

#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 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment

#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)....

1.2.1.2 Funktionen zur Textmanipulation

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 ;
24 x 12-oz-flaschen;24 X 12-OZ-FLASCHEN;19;24 ;
12 x 550-ml-flaschen;12 X 550-ML-FLASCHEN;20;12 ;

Ich habe die Funktionen aus Technet: Textfunktionen (DAX) probiert. Einige dieser DAX-Funktionen haben problemlos funktioniert (siehe Beispiel), andere Funktionen leider nicht.

1.2.1.3 ALL, DISTINCT, DISTINCTROW, TOP Predicates

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

1.2.2 FROM Clause

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 1.2 Data Manipulation Language enthalten ist. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment

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 1.2 Data Manipulation Language ein. Das vollständige Beispiel gibt es am Ende dieses Kapitels als Attachment

#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 1.1.1 CREATE TABLE Statement habe ich bereits gezeigt, wie solche Namen mit dem CREATE TABLE Statement oder dem Namemanager angelegt werden können. Zusätzlich kann man auch einfach eine oder mehrere Zellen markieren und in das Dropdownfenster links oben in Excel einfach einen beliebigen Namen (hineinschreiben.

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:

Der Rangename "MyTabelle1" muss entweder über ein Skript oder über den Namemanager definiert sein. Siehe Kapitel 1.1.1 und 1.1.2
Die Namen sind in der gesamten Excelmappe gültig, daher braucht man keine Tabellenblattnamen mehr angeben.

1.2.3 WHERE Clause (Filtern)

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.

1.2.3.1 Filtern nach statischen Strings

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.

1.2.3.2 Filtern nach Strings mit Platzhalten (LIKE Operator)

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;

1.2.3.3 Filtern nach Zahlen

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;

1.2.3.4 Zeit- und Datumsberechnungen

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.

1.2.4 INNER JOIN

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.

1.2.5 SELECT...INTO Statement

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 1.1.1 CREATE TABLE Statement) erstellen

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")

2 Der Connectionstring

2.1 ExtendedProperties im ConnectionString

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

3 weitere nützliche Beispiele

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

Anhänge:
Kap_1.2.1-Beispiel_2.ps1.txt	[ ]	1 kB
Kap_1.2.1-Beispiel_3.ps1.txt	[ ]	1 kB
Kap_1.2.1-Beispiel_4.ps1.txt	[ ]	1 kB
Kap_1.2.1.1-Beispiel_1.ps1.txt	[ ]	1 kB
Kap_1.2.1.1-Beispiel_2.ps1.txt	[ ]	1 kB
Kap_1.2.1.1-Beispiel_3.ps1.txt	[ ]	1 kB
Kap_1.2.1.1-Beispiel_4.ps1.txt	[ ]	1 kB
Kap_1.2.1.2-Beispiel_1.ps1.txt	[ ]	1 kB
Kap_1.2.1.3-Beispiel_1a.ps1.txt	[ ]	1 kB
Kap_1.2.1.3-Beispiel_1b.ps1.txt	[ ]	1 kB
Kap_1.2.1.3-Beispiel_2.ps1.txt	[ ]	1 kB
Kap_1.2.2-Beispiel_2.ps1.txt	[ ]	1 kB
Kap_1.2.2-Beispiel_3.ps1.txt	[ ]	1 kB
Kap_1.2.3.1-Beispiel_1.ps1.txt	[ ]	1 kB
Kap_1.2.3.1-Beispiel_2.ps1.txt	[ ]	1 kB

ADO .Net

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

1.1.2 DROP TABLE Statement

1.1.3 ALTER TABLE Statement

1.2 Data Manipulation Language

1.2.1 SELECT Statement

1.2.1.1 FORMAT-Funktion (DAX)

1.2.1.2 Funktionen zur Textmanipulation

1.2.1.3 ALL, DISTINCT, DISTINCTROW, TOP Predicates

1.2.2 FROM Clause

1.2.3 WHERE Clause (Filtern)

1.2.3.1 Filtern nach statischen Strings

1.2.3.2 Filtern nach Strings mit Platzhalten (LIKE Operator)

1.2.3.3 Filtern nach Zahlen

1.2.3.4 Zeit- und Datumsberechnungen

1.2.4 INNER JOIN

1.2.5 SELECT...INTO Statement

2 Der Connectionstring

2.1 ExtendedProperties im ConnectionString

3 weitere nützliche Beispiele

4 Attachments