PowerShell und CSV-Datei

CSV-Dateien sind ein einfaches handhabbares Format für Listen und flache Datenbanken. Wenn man etwas bezüglich Zeichensatz, Trennzeichen und Feldeinfassungen aufpasst, kann man mit CSV-Datei wunderbar z.B. Kontakte, Mailboxlisten und andere Daten vorhalten. für gewöhnlich nutze ich Hashtabellen, vor allem wenn es einen primären Schlüssel gibt, nach dem ich regelmäßig dann auf die Daten zugreifen muss wie z.B. Absoluter Dateiname, SMTP-Adresse oder Distinguishedname.

Manchmal möchte ich aber auch Daten speichern, die aus mehreren Spalten bestehen und ich möchte eine Zeile finden, in der bestimmte Spalten einen bestimmten Wert haben. In der Vergangenheit habe ich dann einfach die Spalten aneinander gehängt und als Schlüssel für eine Hashtabelle genutzt. Schön sah das im Code nicht aus und ein Export in eine CSV-Datei war auch nicht einfach.

CSV-Basics

PowerShell liefert gleich mehrere Commandlets zum Einlesen, Schreiben und Konvertieren mit, wie ein Get-Help schnell zeigt.

PS C:\> get-help *csv

Name                              Category  Module                    Synopsis
----                              --------  ------                    --------
epcsv                             Alias                               Export-Csv
ipcsv                             Alias                               Import-Csv
Export-Csv                        Cmdlet    Microsoft.PowerShell.U... Export-Csv...
Import-Csv                        Cmdlet    Microsoft.PowerShell.U... Import-Csv...
ConvertTo-Csv                     Cmdlet    Microsoft.PowerShell.U... ConvertTo-Csv...
ConvertFrom-Csv                   Cmdlet    Microsoft.PowerShell.U... ConvertFrom-Csv...

Damit ist schnell erklärt, wie man eine CSV-Datei einliest und wieder schreibt. Schauen Sie sich auf jeden Fall auch die Parameter der Commandlets an, denn einige machen die Funktionen sehr viel leistungsfähiger.

So z.B. die Angabe des Header beim "Import-CVS". Damit ist das Commandlet nicht darauf angewiesen, den Header in der ersten Zeile selbst zu erkennen. Das klappt kaum, wenn sie z.B. die Logfiles eines IIS auswerten und am Anfang die Kommentare sind.

Import-Csv `
   -path .\u_ex110915.log `
   -Delimiter " "`
   -header "date","time","s-ip","cs-method","cs-uri-stem","cs-uri-query",`
        "s-port","cs-Username","c-ip","cs(User-Agent)","sc-status","sc-substatus","sc-win32-status","time-taken"

Wie flexibel die IIS Abfragen sind zeigt das folgende Beispiel, welches alle Fehlgeschlagenen EWS-Anfragen von authentifizierten Benutzern anzeigt

Import-Csv `
   -path .\u_ex130226.log `
   -Delimiter " "`
   -header "date","time","s-ip","cs-method","cs-uri-stem","cs-uri-query",`
        "s-port","cs-Username","c-ip","cs(User-Agent)","sc-status","sc-substatus","sc-win32-status","time-taken" `
| where { ($_."cs-uri-stem" -eq "/ews/exchange.asmx") `
     -and ($_."cs-Username" -ne "-") `
     -and ($_."sc-status" -match "[45]..")`
   }

Hängt man noch "| group cs-Username | select count,name" dran, dann kann man schön sehen, wie oft welcher Benutzer das Problem hat. Sollte ein Benutzer gar nicht auftauchen, dann kann es an einer fehlerhaften Authentifizierung liegen (Proxy, Kerberos Ticket Size).

Hinweis
CSV-Dateien dürfen in den Feldern nur "normale Strings" enthalten aber z.B. keine Sonderzeichen wie "CR" oder "LF", selbst wenn das Feld mit Anführungsstrichen eingefasst ist. Solche Sonderzeichen müssen mit einem "X'" codiert werden.
Achten Sie auch auf das Encoding, damit z.B. in Deutschland häufige Umlaute bei Namen auch korrekt ankommen.

CSV mit ANSI

Per Default kann Import-CSV leider nur UNICODE-Dateien einlesen. Wer aber mit CSVDE und anderen Tools arbeitet, bekommt sehr oft nur ANSI oder gar ASCII-Dateien geliefert. Solch eine unstimmigkeit erkennen Sie daran, dass z. B. Umlaute mit einem "?" gekennzeichnet werden. Aber auch hier gibt es recht einfache Hilfe ohne die Datei zu verändern oder temporär anderswo speichern zu müssen. Ein kleiner Umweg über Get-Content hilft:

Get-Content csvdatei.csv `
    -Encoding:string `
| convertfrom-csv `
    -delimiter ","

# Erste Zeile entfernen und Header manuell vorgeben
Get-Content csvdatei.csv `
    -Encoding:string `
| select `
    -skip 1 `
| convertfrom-csv `
    -delimiter "," `
    -header "feld1","feld2","feld3"

Und schon bekommt man die CSV-Elemente auch von ANSI-Dateien problemlos importiert und natürlich weiter verarbeitet

CSV selbst erstellen

Natürlich können Sie eine leere CSV-Datei einfach mit "OUT-FILE" erstellen. In der Regel haben Sie aber ja irgendwie ein Datenmodell in Form eines "CustomObjekt", z.B. in der folgenden Form:

# Custom Objekt anlegen
$newrow = New-Object PSObject -Property @{
   Name = "Carius"
   Vorname = "Frank"
   Alter = 99
}

# Export nach CSV
$newrow | Export-Csv .\test.csv -notypeinformation

# Anzeige
PS C:\> type test.csv
"Vorname","Name","Alter"
"Frank","Carius","99"

Damit ist die CSV-Datei natürlich gleich schöner erstellt. Sie können aber auch eine Liste anlegen und diese Objekte einfach anhängen

$csvlist =@()
1..5 | %{
   $newrow = New-Object PSObject -Property @{
      Name = ("nachname"+$_)
      Vorname = ("Vorname"+$_)
      Alter = $_
   }
   $csvlist += $newrow
}

$csvlist | ft -AutoSize

Vorname Name Alter
------- ---- -----
Vorname1 nachname1 1
Vorname2 nachname2 2
Vorname3 nachname3 3
Vorname4 nachname4 4
Vorname5 nachname5 5

$csvlist | export-csv -path ".\peronen.csv" - notypeinformation

Das war noch gar nicht schwer. Wenn man aber nun eine große Datenmenge erwartet, dann sollten Sie natürlich nicht erst eine Liste im Speicher aufbauen.

(1..5 | %{
   $newrow = New-Object PSObject -Property @{
      Name = ("nachname"+$_)
      Vorname = ("Vorname"+$_)
      Alter = $_
   }
   $newrow
}) | export-csv dateiname.csv

Allerdings ist vielleicht die Lesbarkeit etwas erschwert, da die eigentliche Funktion in sich wieder "geklammert" ist.

Beachten Sie dazu auch PowerShell Pipeline

Sortieren mit Sort-Object

PowerShell kennt das Commandlet Sort-Object, zu dem es auch den Alias "sort" gibt. So können Sie eine Liste schnell nach einem Feld sortieren

#Sortierte Ausgabe auf den Bildschirm
$csvlist | sort nachname

# Sortierte Ausgabe in einer anderen Variablen speichern 
$sortlist = ($csvlist | sort nachname)

CSV als Datenbank nutzen

Interessant wird die CSV-Datei in einer Variable aber, wenn Sie ihre Eignung als Datenbank im Speicher betrachten. Sie können in der CSV-Datei nämlich auch relativ einfach mit "where" suchen.

$csvlist | where {$_.Name -eq "User2"}

Interessant wird das ganze aber, wenn Sie das Ergebnis einer Variablen zuweisen und diese ändern.

$ergebnis = $csvlist | where {$_.Name -eq "nachname4"}
$ergebnis[0]

Beachten Sie aber, dass das Ergebnis nicht immer nur genau eine Zeile sein muss, sondern auch mehrere Treffer haben kann. Aber was passiert, wenn Sie nun den Inhalt von "$ergebnis" verändern ?

# Wert einer Zeile aender
$ergebnis[0].Alter ="100"

# geaenderte Zeile ausgeben
$ergebnis

# komplette Tabelle ausgeben
$csvliste

Überrascht?. Die Suche in der CSV-Liste mit Where liefert als Ergebnis keine Kopie der Inhalte, sondern nur einen Pointer auf die gleiche Speicherstelle (ByRef statt ByVal). Eine Änderung des Ergebnis verändert auch die originale Quelle. Das ist in diesem Fall gut, denn es spart Speicher und wir haben einen einfachen Weg, eine durch die Suche ermittelten Teilmenge zu verändern. Damit haben wir in etwa das nachgebildet, was in einer echten Datenbank ein "Select" ist

Natürlich ist das hier bei weitem nicht so flexibel wie eine echte Datenbanktabelle. So gibt es kein Index, keine Typisierung der Felder, keine Prüfung der Eindeutigkeit etc., aber für kleine Datenbestände ist dies durchaus nutzbar. Allerdings müssen Sie sich immer daran erinnern, dass die Feldinhalte selbst "Strings" sein können. Wer also damit rechnen will, muss Typkonvertierungen vorsehen.

# falsch da die 1 einfach angehängt wird. Aus 4 wird 41
$ergebnis[0].Alter +=1

# besser. Hier wird aus der 4 eine [int] und mit dem +1 eine 5
[int]($ergebnis[0].Alter) +=1

CSV Import Performance

Auf der Seite PS Performance habe ich schon verschiedene andere Aspekte von PowerShell und Performance beschrieben. für die CSV-Dateien habe ich hier einen eigenen Abschnitt. Angeregt durch die Performance-Unterschiede bei einer einfachen Text-Datei interessiert mich nun natürlich auch eine CSV-Datei. Diese Dateien werden ja sehr oft als Ablage verwendet, z.B. beim IISLog, Exchange Tracking, CSVDE und als einfache Datensatztabellen. Ich habe mir von einem IIS dann einfach mal schnell eine W3SVC-Logdatei mit 50 Megabyte und knapp 350.000 Zeilen geschnappt.

Um die Ergebnisse von den Disks unabhängig zu machen, wurde der Import immer zweimal ausgeführt und der zweite Lauf gemessen. Die Datei sollte damit im Cache sein und Disk-IO die Messung nicht verfälschen. Gemessen wurde mit "Measure-Command".
Die Messungen sind eine Momentaufnahme auf einem Notebook von 2009 ohne SSD mit einem Core i5 der ersten Generation und daher nicht mit aktuellen Systemen vergleichbar.

Methode Zeit Sek Beschreibung 

$a = import-csv -path csvdatei.csv

22 Sek

Ein einfacher Import-CSV in eine Variable. Neben dem "Lesen" kostet dies auch noch Platz für das Allokieren von Speicher. Die Weiterverarbeitung und Filterung erfolgt erst danach

 

import-csv -path csvdatei.csv | out-null

20 Sek

Der gleiche Lauf aber diesmal über die Pipeline verwerfen. Hier kann schon beim Einlesen weiter gearbeitet und gefiltert werden, so dass man am Ende eventuell nur eine Teilmenge der Daten speichern muss.

 

[System.IO.File]::ReadallLines($file.FullName)  | out-null

16 Sek

Als Gegentest habe ich den schnellsten Weg zum Textdatei-Lesen genommen und auch erst mal nach NULL gesendet. Das ist zwar schneller aber wenn ich nur eine kleine Powershell-Funktion anhänge, die z.B. die Felder analysiert, wird es schon langsamer als der Import-CSV

 

$null= [System.IO.File]::ReadallLines($file.FullName)

2 Sek

Interessant ist, das das Lesen in eine Variable so viel schneller geht als die Ausgabe mit " | out-null".

Logparser.exe / Log Parser Studio

1 Sek

Logparser kann Dateien eigentlich nicht "nur" einlesen, sondern darauf filtern und dann ein Ergebnis liefern. Alle Versuche einer Abfrage haben aber bei der gleichen 50 Megabyte-Datei kaum länger als eine Sekunde gedauert. Ob es wirklich am nativen Code liegt ?

 

get-content | Convertfrom-csv

8,4

Mit diesem Commandlet können Sie String im CSV-Format konvertieren, so dass ein einfacherer Zugriff auf die Spalten möglich ist.

 

[System.IO.File]::ReadallLines($file.FullName) `
   | Convertfrom-csv

23 Sek

Es wird sogar langsamer, wenn man Get-Content durch einen direkten -NET Aufruf ersetzt.

Manuelles Trennen

 

Vergessen Sie es am besten, mit "SPLIT" oder RegEX die CSV-Datei selbst auftrennen zu wollen. Das geht selten gut, da es neben den Trennzeichen auch die Einfassungszeichen für String zu verarbeiten gibt. Ein "Komma" als Trenner kann ja auch durchaus im Feld vorkommen.

Es ist leider immer wieder zu sehen, das ein reines Lesen von Dateien mit Basismitteln sehr schnell ist und die Auswertung in Felder sehr lange dauert. Das Problem wird hier aber eher sein, dass Import-CSV als auch ConvertTo-CSV die Datei sicher schnell lesen, aber die Aufbereitung langsam ist. Für größere Datenmengen wird man dann eher zu speziellen Werkzeugen greifen.

CSV Export Performance

Bei der Gegenrichtung geht es fast immer darum, ein Objekt in eine CSV-Datei zu schreiben. Auch hier gibt es mehrere Optionen:

Methode Zeit Beschreibung

Export-CSV

nicht gemessen

Das klassische Commandlet kann genutzt werden, um eine Tabelle oder Werte einer Hashtable in eine CSV-Datei zu schreiben. Die Inhalte der Felder sollen aber schon "einfache" Typen wie String, Integer etc. sein oder von Export-CSV entsprechend konvertiert werden. Export-CSV akzeptiert auch Daten über die Pipeline. Dies ist besonders hilfreich um Speicher zu sparen.

Convertto-CSV

nicht gemessen

Mit diesem Commandlet können Sie eine Tabelle oder Ausgabe der Pipeline mit entsprechenden Properties direkt in eine CSV-Datei übertragen lassen.

CSV selbst schreiben

nicht gemessen

Wenn Sie ganz genau wissen, wie ihre Datenstruktur aussieht, dann können Sie natürlich auch selbst eine CSV-Datei schreiben. Ich mache das sogar manchmal, wenn ich große Daten verarbeite und in einem Durchlauf mehrere Daten extrahiere und diese in separate CSV-Dateien ablegen will. Dann kann ich nicht wirklich mit einer Pipeline arbeiten aber das Zwischenspeichern in einer Variable würde den Speicherbedarf sprengen. Wenn Sie schon mal mehrere Gigabyte IISLogs oder Messagetrackinglogs auswerten und unterschiedliche Statistiken erstellen dann können Sie das nicht alles im Speicher halten. Dann öffne ich klassische einfach Dateihandle und hänge weiter an.

Dabei verzichte ich dann aber auch "Append-Content",   Export-CSV -append o.ä., insbesondere, wenn ich immer einzelne Zeilen anhängen muss. Dann bricht die Schreibperformance natürlich ein.

function object2csv ($object,$header){
    [string]$result = ""
   foreach ($property in $header.split(",")){
      $result+=(",""" + $object.$property + """")
   }
   $result.substring(1)  # cut off initial separator
}

# datei oeffen
$mtrackfailfile = New-Object System.IO.StreamWriter c:\temp\test.csv
[string]$mtrackfailheader="date-time,client-ip,client-hostname,server-ip"
$mtrackfailfile.writeline($mtrackfailheader)

# diese Zeile ist dann innerhalb der Forschleife
$mtrackfailfile.writeline((object2csv $daten $mtrackfailheader))

$mtrackfailfile.close()

Allerdings muss man dabei aufpassen, dass Streamwriter immer absolute Pfade braucht. ein ".\dateiname".csv" geht nicht. Wer das Skript ungeplant abbricht, kann die Datei nicht mehr mit "Close()" schließen. Sie ist damit blockiert, bis die Powershell beendet wird. Sie sollten also Fehler sauber mit Try/Catch oder Trap abfangen und die Datei schließen

CSV Encoding

Wenn Sie eine CSV-Datei exportieren oder importieren, kann kann über den Parameter "Encoding" das Format vorgegeben werden. mögliche Werte sind:

  • Unicode
  • UTF7
  • UTF8
  • ASCII
  • UTF32
  • BigEndianUnicode
  • Default
  • OEM

Interessant dabei ist, dass der Wert "Default" eigentlich für ANSI steht. Wird der Parameter aber weg gelassen, dass versucht Import-CSV das Format zu erkennen und nutzt also nicht automatisch ANSI. Wenn Sie also sicher sein wollen, dass der Import immer per ANSI erfolgt, dann ist ein "-encoding default" erforderlich. 

Weitere Links