PowerShell und Excel
Viele Informationen werden immer noch von Administratoren als "Excel-Datei" verwaltet. Es geht schnell, erlaubt filtern und sortieren und als OneDrive/SharePoint-Library kann sogar per Browser direkte darin editiert werden und eine Versionierung gibt es kostenfrei. Pragmatisch ist so eine Excel-Tabelle eine einfache Datenbank oder CSV-Datei. Wie kann die die Daten aber zur Automatisieren nutzen?
Auslöser
Meine Recherchen und Skripte basieren auf den ein oder anderen Teams Projekten mit Telefonie. Die für die Vergabe von internen Rufnummern zuständigen Personen pflegen wohl schon seit Jahrzehnten eine Excel-Liste, in der alle Durchwahlen mit den Endgeräten, Patchfeldern, Konfiguration, Inhabern, Kostenstellen etc. gepflegt werden. Ich bin sicher, dass die Mehrzahl der Leser hier zustimmen werden. Mit Teams als Telefonie kommt im Grunde nur ein neues "Endgerät", nämlich ein PC hinzu. An der Verwaltung der Rufnummern ändert sich aber erst einmal nicht, solange es die "alte Technik" noch gibt und verwaltet werden muss. Und das kann lange dauern.
Ich habe aber die Anforderung, die Rufnummern nun mal auch in Teams und ggfls. einem AD LDS / ADAM zu verwalten, damit ein SBC basierend darauf routen kann. Natürlich gibt es viel mehr Nebenstellen und nicht für jede Nebenstelle gibt es ein AD-Objekt. Kaum jemand wird einen AD-Konten anlegen, um Türsprechstellen, Modem-Anschlüsse, Alarmanlage, Telematik-Anschlüsse etc. zu verwalten und die XLS/XLSX-Datei abzulösen. Bisher ist jeder Versuch dahingehend verzögert oder abgelehnt worden. So vollständig kann kein AD sein.
Automatisierung mit Excel
Da aber die Handarbeit nervig ist, muss ich mir eine Automatisierung überlegen, an die Daten einer XLS/XLSX-Datei möglichst per Skript oder API heranzukommen oder die Daten so zu extrahieren, dass ich sie weiter verarbeiten kann. Idealerweise natürlich immer "aktuell" oder vielleicht sogar "Realtime" und getriggert. Als habe ich recherchiert und bin auf folgende Varianten gekommen.
Method | Eignung | Beschreibung |
---|---|---|
CSV-Export |
- |
Die Verarbeitung von CSV-Dateien ist mit PowerShell und "Import-CSV" sehr einfach und Excel kann sehr einfach solche Dateien erstellen. z.B.
Allerdings ist die CSV-Datei natürlich nur ein Zwischenschritt. |
XLSX2PSObject |
++ |
Daher habe ich weiter gesucht und bin auf das Modul "ImportExcel"-Modul von Doug Finke (MVP) hervorzuheben. Das Module gib es schon viele Jahre und dürfte das Werkzeug für Excel-Automatisierung sein. Auch wenn der Namen "ImportExcel" suggeriert, dass man damit eine XLSX-Datei in ein PowerShell-Objekt importieren und dann weiter verwenden kann, kann das Modul mittlerweile auch Excel schreiben, ändern u.a.
PowerShell Excel Module -
ImportExcel Nach dem ich damit etwas gearbeitet habe, habe ich nicht mehr überlegt eine eigene XLSX-Entpack und XML-Parse-Routine zu schreiben. Es gibt noch eine "Excellibrary.dll", welche auf https://code.google.com/p/excellibrary/ liegt und z.B.: von https://gist.github.com/PolarbearDK/d164012480445eb55278 genutzt wird. Allerdinge wird der Code wohl nicht mehr gepflegt (Letzte Änderungen 2011). |
COM-Objekt |
O |
Natürlich können Sie auch weiterhin eine Excel-Datei über ein installiertes Excel als COM-Objekt öffnen und quasi Excel "fernsteuern". Aufgrund der COM-Funktion ist dies aber ein eher auslaufender Weg und mit PowerShell Code PS 6.0 und höher) sowieso nicht nutzbar. Auf diesen Weg würde ich nur im Notfall zurückgreifen. Daher nur die Grundfunktion um die Nutzung zu erläutern. # Excel instanzieren $ExcelApp = New-Object -ComObject "Excel.Application" # Datei in Excel öffnen $ExcelArbeitsblatt = $ExcelApp.Workbooks.Open("C:\users\fc\test\rufnummer.xlsx") # Datei als CSV speichern $ExcelArbeitsblatt.SaveAs("C:\users\fc\test\rufnummer.csv", 6) # Aufraeumen $ExcelArbeitsblatt.Close() $ExcelApp.Quit() |
XLSX als ODBC/OLEDB-Datenbank |
O |
Auf den Weg kommt man nicht sofort aber Windows hellt ODBC bzw. OLEDB-Treiber und über diese Schnittstelle können sie auch Tabellen in Excel-Dateien wie eine Datenbank ansprechen. This download will install a
set of components that facilitate the transfer
of data between existing Microsoft Office files
such as Microsoft Office Access (*.mdb and
*.accdb) files Allerdings platziert Microsoft dieses Modul eher als Migrationswerkzeug zur Überführung von solchen als Datenbank genutzten Dateien in einen SQL-Server und führt einige Einschränkungen auf. Wir bewegen uns hier dann in einem Randbereich. Microsoft Access Database
Engine 2016 Redistributable |
MSGraph |
++ |
Interessant wird es, wenn die XLSX-Datei nicht auf dem lokalen PC des Anwenders oder einen einfachen Dateiserver sondern in Microsoft 365 abgelegt ist. Über die Microsoft Graph API können Sie über HTTPS-Aufrufe direkt die Datei und einzelne Zellen lesen und sogar ändern.
Dieser Weg ist natürlich interessant, wenn der Zugriff auf die Daten ansonsten nicht möglich wäre, z.B. bei Cloud-Anwendungen. |
Power Automate |
+ |
Mit der passenden Lizenz kann das aber auch die Cloud komplett alleine. Eine Suche in Power Automate nach "Start a script, if excel row changes" liegt folgendes Ablaufdiagramm:
Sobald sich eine Zelle ändert, und eine Bedingung zutrifft, wird für jede Änderung ein Skript gestartet. Das sieht schon fast zu einfach aus.
|
Die Herausforderung per Skript solche Informationen abzurufen ist natürlich die Authentifizierung. Wenn ich z.B. eine Excel-Tabelle in OneDrive/SharePoint liegen habe und automatisiert per Skripte zugreifen will, dann möchte ich keinen "Dienstuser" nehmen, der kein MFA machen muss. Ein Zugriff per "Delegate" erfordert auch die Mithilfe des Anwender während eine AppPermisions sehr umfangreiche Rechte hat. Ich habe noch nicht herausgefunden, wie ich in so einem Fall einen "anonymen OneDrive-Link" mit Graph nutzen kann.
Testserie
Zur Beschreibung der Funktion ist ein Beispiel angebracht. Mit Excel habe ich folgende Tabelle angelegt:
Auf dem ersten Arbeitsblatt ist eine Tabelle mit Überschrift, Spaltenkennzeichnungen, Werten und einer Ergebniszeile samt Berechnung. Eine direktes Einlesen mit Import-Excel konnte damit natürlich nicht funktionieren, denn Überschriften kennt es nicht.
Über den Parameter "StartRow" habe ich Import-Excel dann angewiesen die erste Zeile zu überlesen. Damit sahen die Daten schon viel besser aus.
Technisch ist die Ausgabe ein Array mit eine "PSCustomObject" pro Zeile mit den entsprechenden Properties.
$daten= Import-Excel "c:\Users\fcarius\ps_excel.xlsx" -StartRow 2 $daten.gettype() IsPublic IsSerial Name BaseType -------- -------- ---- -------- True True Object[] System.Array $daten | Get-Member TypeName: System.Management.Automation.PSCustomObject Name MemberType Definition ---- ---------- ---------- Equals Method bool Equals(System.Object obj) GetHashCode Method int GetHashCode() GetType Method type GetType() ToString Method string ToString() GB Disk NoteProperty double GB Disk=100 IP-Adresse NoteProperty string IP-Adresse=192.168.178.1 PCName NoteProperty string PCName=Router
Die Werte sind einfache "NoteProperties" und enthalten auch nur die Werte wie sie in Excel angezeigt werden. Ich habe keinen Zugriff auf Farben, Formatierungen, Notizen, Kommentaren, Berechnungen o.ä.
Import-Excel liest immer das komplette Worksheet aus aber überspringt leere Spalten, wenn die erste Zeile in der Spalte nicht gefüllt ist. Leere Zeilen werden mit gelesen. Hier ein Beispiel:
Der Eintrag "PC9" in der Zeile 11 wird mit erfasst und auch die Spalten D und E aber der Wert ""PC5" in Spalte F wird nicht erfasst, da das Feld F2 leer ist. Das errechnete Ergebnis "2100" wird auch einfach als numerischer Wert ohne die dahinterliegende Formel ausgegeben.
Beispiel: Telefonie-Provisioning
Wenn ich nun nicht jede Änderung direkt per PowerAutomate an ein Skript geben möchte, dann läuft das Provisioning doch wieder darauf hinaus, dass entweder ein Admin oder ein geplanter Task ein Skript startet. Dabei stellt sich dann die Frage, wie dieses Skript die verschiedenen Änderungen erkennt. Es gibt ja durchaus drei Szenarien:
- Add - Eine Zeile mit neuer Rufnummer wird addiert
- Change - Eine bestehende Zeile wird geändert
- Remove - Ein Eintrag wieder gelöscht
Solche Daten aus einer Datei zu lesen und in ein andere System zu importieren ist immer eine Art Verzeichnisabgleich, wie ich ihn auf CSV2EX, MiniSync, CSVSync - Framework und anderen Seiten immer wieder beschrieben habe und auch ADSync / AADConnect arbeitet nicht anders: Beim Import aus der Quelle werden die Daten mit dem vorherigen Stand verglichen um die Änderungen zuverlässig zu erkennen. Alternativ könnte das Skript die Einstellungen auch direkt mit dem Ziel vergleichen. Denken Sie aber daran, das sie die Reihenfolge einhalten: Wer zuerst neue Elemente addiert oder geänderte Elemente schreiben möchte, ehe er alte Einträge im Ziel entfernt hat, könnte Konflikte erhalten, z.B. wenn eine Rufnummer zu einem anderen Anwender umgezogen wurde. Daher sollten Sie immer die Lösch-Aktionen vorziehen. Der Vergleich mit einer lokalen Kopie hat den Vorteil, dass bei einer Replikation in nur eine Richtung erst dann eine Verbindung zum Ziel aufgebaut werden muss, wenn es Änderungen zu schreiben gibt. Das bringt Geschwindigkeit und erlaubt vorab die Anzeige der geplanten Änderungen.
Mittels PowerShell und "ImportExcel" kann ich die Excel-Datei einmal pro Stunde einlesen und mir die Objekte holen, die für Teams relevant sind, z.B. mit:
Install-Module importexcel Import-Module ImportExcel $telefonliste = Import-Excel \\server\share\telefonie\nebenstellen.xlsx | where {$_.Endgeraet -eq "Teams" } | select rufnummer,upn
Natürlich sollten Sie prüfen, dass wirklich die Nummern eindeutig sind, aus dem erwarteten Nummernkreis kommen und nicht doppelt verwendet werden. Wenn Sie nicht jedes Mal einen kompletten Import fahren wollen und zudem Löschungen erkennen müssen, dann ist ein Vergleich mit dem vorherigen Stand z.B. mit einer Hashtable ratsam, die dazu geladen wird
[hashtable]$alteliste =@{} ` foreach ($Entry in (Import-CSV .\alteliste .csv)) { $alteliste .add($_.rufnummer,$_.upn) }
Dann startet der Vergleich, um die Änderungen zu erkennen. Dabei werden "erledigte" Einträge aus der alten Liste gestrichen und was am Ende übrigbleibt, muss im Ziel noch entfernt werden. Als Extra Validierung könnte vorab die Rufnummern gegen eine Liste von gültigen Stammnummern und auf doppelte Einträge und andere Fehler geprüft werden. Stichunkt "Input-Validation".
foreach ($entry in $telefonliste) { # hier noch ggfls. eine Eingabevalidierung und Prüfung der Nummern einbauen # if ($alteliste.containskey($entry.rufnummer) { Write-Host "Bestehende Rufnummer. Vergleiche" if ($alteliste.item($entry.rufnummer) -eq $entry.rufnummer) { Write-Host "Keine Aenderung, entferne aus der Hashtable" $alteliste.remove($entry.rufnummer) } else { Write-Host "Neue Nummernzuordnung" #Suche erst den User, der die Nummer bisher hatte und nimmt sie dort weg #Konfiguriere dann die neue Nummer am neuen Ziel #Anhand der Stammnummer kann man auch die Location und z.B. damit Dialplan und Routingpolicies bestimmen <hier muss ihr code rein> } } else { Write-Host "neue Rufnummer erkannt" # hier dann der Code um den User mit der neuen Rufnummer einzurichten <hier muss ihr code rein> #Am Ende die neue Nummer in die Hashtabelle übernehmen $alteliste .add($entry.rufnummer,$entry.teilnehmer) } } Foreach ($entry in $alteliste.keys()){ Write-Host "Removing unassigned number $entry.rufnummer # hier muss dann die vorher noch vergebene Rufnummer gesucht und vom User entfernt werden <hier muss ihr code rein> }
Nach all denn Änderungen muss ich die aktualisierte "Liste" natürlich wieder für den nächsten Lauf exportieren.
$Telefonliste | export-csv -path .\alteliste .csv -force
Das Beispiel ist absichtlich einfach und geht auch nicht damit um, wenn jemand im Ziel an dem Skript vorbei die Rufnummern anpasst. Dann kommen wir ganz schnell zu einem echten DirSync, bei dem am Anfang von beiden Seiten die Daten importiert, verglichen und gemachted werden, ehe dann die Änderungen wieder exportiert werden. Wie das geht, können Sie bei ADSync auf ADSync Funktion anschauen. Das hat aber dann nichts mehr mit Excel und PowerShell zu tun.
Einschätzung
Ich nutze, wie vermutlich viele Administratoren auch, Excel zur Verwaltung von Listen, einlesen von CSV-Dateien, formatieren, filtern und visualisieren. Damit stecken wichtige und nützliche Daten in XLSX-Dateien, die mit dem "ImportExcel"-Modul auch per PowerShell angezapft und konsumiert werden können.
Ich nutze Import-Excel dabei nur, um die Feldinhalte von Listen und Tabellen zur weiteren Verarbeitung zu lesen. Excel kann natürlich mehr, z.B. Kommentare und Notizen an Zellen anheften, Daten sortieren, filtern und abhängig von den Werten auch schick formatieren.
Ich habe noch nicht herausgefunden, ob Import-Excel vielleicht auch all diese Informationen mit auslesen und erreichbar machen kann. Aber darauf lege ich es gar nicht an, denn in der PowerShell reichen mir die "Raw-Daten".
Weitere Links
- PS CSV-Datei
- ADSync Funktion
- Graph API
- CSV2EX
- MiniSync
- CSVSync - Framework
- ADSync / AADConnect
- Verzeichnisabgleich für Exchange
- How to Convert Excel (XLSX) File to CSV
using PowerShell?
https://www.sharepointdiary.com/2021/03/convert-xlsx-to-csv-in-powershell.html