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.

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

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