•  
  •  
Export-CSV mit multidimensionalen (multivalue) Daten, auch für Microsoft Excel

Export-CSV mit multidimensionalen (multivalue) Daten, auch für Microsoft Excel

Wenn man mit Export-CSV arbeitet, sollte man sich mit dem Grundlagen des CSV Format auskennen.
Das CSV Format ist eigentlich eine Tabelle, die nur 2 Dimensionen hat, um Daten in Zellen darzustellen.
Spalten und Zeilen (oder auch X,Y).
In den Internet-Foren kommt immer wieder die Frage auf, wie man Daten mit noch mehr Dimensionen in CSV exportieren kann.
Ich zeige hier, wie man diese multidimensionalen Daten in die 2 Dimensionen von CSV bringt.
(ich nenne das „flach klopfen von Daten“ ;-) )..

Das CSV Format

Das Dateiformat CSV steht für Englisch: Comma-Separated Values.
Die Regeln zu Erstellung einer CSV Datei werden im RFC 4180 von der Internet Engineering Task Force (IETF) beschrieben.
Siehe: http://tools.ietf.org/html/rfc4180
Siehe Wikipedia: http://de.wikipedia.org/wiki/CSV_%28Dateiformat%29

Der Text in einer CSV Datei muss den Regeln aus dem RFC 4180 entsprechen.

Man kann sich eine CSV Datei wie eine Excel Tabelle mit Überschriften für jede Spalte in der ersten Zeile vorstellen.

Export-CSV Excel Tabelle

Der im Bild gezeigten Excel Tabelle liegen folgende CSV Daten zugrunde:

Vorname;Nachname;UserID;Auftrag
Peter;Maffay;PetMaffa;Add
Sabine;Lisicki;SabLisic;Remove
Claudia;Schiffer;ClauSchi;Add
Herrman;Hesse;HerrHess;Add
Walter;Moers;WaltMoer;Remove

Hier passt es wunderbar, die Daten sind 2 Dimensional und es gibt für jede Zeilen- + Spalten Kombination nur einen Wert (pro Zelle). Die Daten haben eine 1:1 Beziehung zu den Zellen.

Diese Aussage gilt auch für die folgende Ausgabe von Services mit dem Cmdlet Get-Service:

Der Code erzeugt die im folgenden Bild gezeigten CSV Datei, als Microsoft Excel Tabelle:

Export-CSV Excel Tabelle

Jeder Service ist ein einzelnes Objekt.
Objekte haben in der Regel mehrere Eigenschaften (Englisch: Property) die Daten enthalten.
Der Wert von den Daten kann über den Property-Namen aus einem Objekt mit Hilfe des Punkt Operators als Pfadangabe ausgelesen oder gesetzt werden.
Objekt.Name = Wert.
Bei dem Cmdlet Select-Objekt gibt man einfach nur die Namen von den Properties an, dessen Werte man von einem Objekt haben möchte.
Alle Objekte von einem bestimmten Typ (hier Service Objekte) sind gleich aufgebaut und haben deshalb einen gemeinsamen Satz an Eigenschaften.
Der Spaltenname in einer CSV Tabelle entspricht dem Namen der Eigenschaft von einem Property des Objektes.
Von jedem Objekt wird über die Pipeline von Select-Object zu Export-CSV eine Daten-Zeile erstellt. Man kann auch sagen, dass jedes Objekt auch eine Zeile ist.
Import-CSV macht beim Einlesen von CSV Dateien aus jeder Zeile wieder ein Objekt.

Delimiter ; Export-CSV und Microsoft Excel

Für das Zeichen, das die einzelnen Datenspalten voneinander trennt, ist das Komma vorgesehen.
Deshalb heißt das Dateiformat auch Comma Separated Values.
Da in Deutschland das Komma als Dezimaltrennzeichen genutzt wird, wird in deutschen CSV Dateien häufig das Semikolon als Trennzeichen (Englisch: delimiter) benutzt.
Wenn das Trennzeichen bei dem Cmdlet Export-CSV nicht das Komma sein soll, muss das Trennzeichen mit dem Parameter –Delimiter angegeben werden oder man benutzt den Parameter -UseCulture.

Der Parameter -UseCulture von dem Cmdlet Export-CSV liest die Einstellungen für das Trennzeichen aus der Windows Kultureinstellung aus.
Das Trennzeichen der aktuell eingestellten Windows Kultur kann man mit folgendem Befehl abfragen:

Auch Microsoft Excel beachtet die Kultureinstellung und nutzt auf einem Windows mit deutscher Kultureinstellung das Semikolon als Trennzeichen!

Wenn auf einem deutschen System die CSV Datei mit Microsoft Excel bequem geöffnet werden soll, muss man den Parameter -UseCulture benutzen oder man benutzt den Parameter -Delimiter mit dem Semikolon!
Dann kann man auch in Windows PowerShell eine CSV ganz bequem über Invoke-Expression oder besser über den Ausführungsoperator & mit Excel öffnen.

 3 Dimensionale Daten

Was ist aber, wenn eine Person nicht nur eine Telefonnummer hat oder mehrere E-Mail Adressen?
Dann gehen die Daten in die 3. Dimension und ein Attribut besitzt mehr als einen Wert. Die Daten haben eine 1:n Beziehung.

Ein naiver Ansatz, der oft gedacht wird, ist es, z:B. einfach für jede zusätzliche Telefonnummer eine neue Spalte aufzumachen (siehe Bild).

Export-CSV Excel Tabelle

Der im Bild gezeigten Excel Tabelle liegen folgende CSV Daten zugrunde:

Vorname;Nachname;UserID;Auftrag;Telefon1;Telefon2;Telefon3;Telefon4
Peter;Maffay;PetMaffa;Add;12345;67890;46367834;
Sabine;Lisicki;SabLisic;Remove;23345;;;
Claudia;Schiffer;ClauSchi;Add;6787;345345;;
Herrman;Hesse;HerrHess;Add;3454;;;
Walter;Moers;WaltMoer;Remove;34534;34534;5454;6786789

Eigenschaften, die eine Liste enthalten (multivalues oder Arrays)

Wenn man solche Daten in Excel oder CSV exportieren will, muss man den Ansatz des mehrwertigen Feldes verfolgen (Englisch: multivalue field).
Ein Feld oder in Excel eine Zelle enthält also selbst wieder eine Liste (ein Array) von Werten.

Bei der Ausgabe von PowerShell Objekten erkennt man Properties, die eine Array von Werten (Objekten) enthalten, an den geschweiften Klammern {}.
Wenn man Get-Service in Kombination mit Select-Object * benutzt, dann sieht man, dass die Properties ‚RequiredServices ‘, ‚DependentServices‘ und ‚ServicesDependedOn‘, jeweils
ein Array mit vielen Werten sind ( multivalued):

Ergebnis:

Name                : WwanSvc
RequiredServices    : {NlaSvc, RpcSs, NdisUio, PlugPlay}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
DisplayName         : WWAN – automatische Konfiguration
DependentServices   : {}
MachineName         : .
ServiceName         : WwanSvc
ServicesDependedOn  : {NlaSvc, RpcSs, NdisUio, PlugPlay}
ServiceHandle       :
Status              : Stopped
ServiceType         : Win32ShareProcess
Site                :
Container           :

Auch im Active Directory gibt es sehr viele Attribute / Eigenschften (Englisch: properties), die ein Array mit mehreren Werte enthalten, also multivalue Attribute sind.

3 Dimensionale Daten in die 2. Dimension zwingen

Im Fall von der zuvor gezeigten Personen Liste heißt das, dass alle Telefonnummern von einem Personen Objekt in eine Spalte geschrieben werden. Hier benutzt man innerhalb der Zelle wieder ein Trennzeichen, das sich am besten von dem CSV Trennzeichen (Englisch delimiter) unterscheidet. Ich benutze hier das Komma als Trennzeichen der einzelnen Werte innerhalb der Zelle.

Export-CSV Excel Tabelle

Der im Bild gezeigten Excel Tabelle, liegen folgende CSV Daten zugrunde:

Vorname;Nachname;UserID;Auftrag;Telefon
Peter;Maffay;PetMaffa;Add;12345,6789,46367834
Sabine;Lisicki;SabLisic;Remove;23345
Claudia;Schiffer;ClauSchi;Add;6787,345345
Herrman;Hesse;HerrHess;Add;3454
Walter;Moers;WaltMoer;Remove;34534,3453399999,5454,6786789

Hier sieht man, dass sich der delimiter der einzelnen Werte innerhalb der Zelle von dem CSV delimiter unterscheiden MUSS, damit das CSV Format gültig bleibt.

3 Dimensionale Daten in die 2. Dimension zwingen mit Windows PowerShell

Um solche 3 Dimensionalen Daten mit der Windows PowerShell und dem Cmdlet Export-CSV auszugeben, ist meist etwas mehr Aufwand nötig.
Da das Cmdlet Export-CSV am liebsten Objekte verarbeitet, muss man sich dazu eventuell komplett neue Objekte zusammen bauen. Dies erledigt hier Select-Object in Verbindung mit einer berechneten Eigenschaft (Englisch: calculated property).

Der Code erzeugt die im folgenden Bild gezeigten CSV Datei als Microsoft Excel Tabelle:

Export-CSV Excel Tabelle

Ich hoffe, dass ich mit diesem Artikel etwas Klarheit über das Cmdlet Export-CSV in Verbindung mit Microsoft Excel und multivalue (Array) Properties bringen konnte.

Auch hier gilt:
LESEN SIE DIE KOMPLETTE DOKUMNETATION ZU DEM CMDLET EXPORT-CSV! RTFM ;-)

Oder
https://technet.microsoft.com/en-us/library/hh849932.aspx

Verwenden des Cmdlet “Export-Csv”
https://technet.microsoft.com/de-de/library/ee176825.aspx

The following two tabs change content below.
Profile photo of Peter Kriegel

Peter Kriegel

IT Professionell, SysAdmin
Ich arbeite mit Computern seit 1984 (Comodore Amiga war mein erster). Seit dem Jahr 1990 Programmiere ich in C,C++ und Basic Sprachen. Seit dem Jahr 2000 bin ich Computer Administrator. In diesem Jahr habe ich die Prüfungen für den Microsoft Certified System Administrator MCSA 2000 absolviert. Als System Administrator benutze ich VBScript ,DOS Batches, Office VBA. Seit 2005 Programmiere ich mit Visual Basic .NET (VB.NET) (.NET V2). Dort entwickle ich Datenbank-Bedienungsoberflächen für den Microsoft SQL Server (GUI Frontend). PowerShell benutze ich seit 2009. In einem sehr großen Active Directory (> 100000 Workstations) verwalte ich (und meine 9 Kollegen) ca. 3000 Windows Clients, die in 37 OUs geordnet sind.

Kommentar verfassen