Werken met CSV bestanden
CSV is een handige, veel gebruikte standaard om gegevens uit te wisselen. In deze blog kijken we naar de mogelijkheden in Powershell bij het gebruik van CSV bestanden.
CSV bestanden zijn erg handig bij het uitwisselen van gegevens. Binnen CERT-WM worden CSV bestanden bijvoorbeeld gebruikt bij de uitvraag van de gebruikte hard- en software bij de achterban, maar ook bij het versturen van extra berichten op basis van contactgegevens.
Veel van die gebruikte informatie zit in eigen systemen. Om dat handmatig om te zetten naar Excel bestanden (zoals dat bijvoorbeeld gebeurt met de uitvraag van de gebruikte hard- en software) is ongelooflijk veel werk en erg foutgevoelig.
Gelukkig kunnen we hier goed gebruik maken van PowerShell.
Een mooi commando voor het werken met CSV bestanden is Import-CSV. Het mooie ervan is dat het automatisch de ingelezen data converteert naar een array met waarden, overeenkomstig met de kolommen in het CSV bestand.
Zo had ik voor de test een CSV bestandje gemaakt met waarden, geëxporteerd uit SQLite, met ongeveer deze inhoud:
target;type;result;reportdate
aldoen.de;ASN;OK;2021-04-01
aldoen.de;DANE;CRITICAL;2021-04-01
aldoen.de;DMARC;CRITICAL;2021-04-01
aldoen.de;DNSSEC;OK;2021-04-01
aldoen.de;DOMAINS;OK;2021-04-01
aldoen.de;HASDNS;OK;2021-04-01
aldoen.de;HOSTINGPAGE;OK;2021-04-01
Mooie CSV. Maar met Import-CSV leverde het geen mooie array op: Alles werd als een kolom gezien:
PS C:\temp> $ALDO = Import-CSV .\alfdoende.csv
PS C:\temp> $ALDO[0]
target;type;result;reportdate
-----------------------------
aldoen.de;ASN;OK;2021-04-01
Het probleem zit erin dat Import-CSV standaard de komma als scheidingsteken wil gebruiken. Dit is te ondervangen door de parameter -Delimiter te gebruiken, of de -UseCulture parameter. Bij deze laatste parameter kijkt Import-CSV zelf naar de standaard van het systeem. (Die kun je ook zelf checken met (Get-Culture).TextInfo.ListSeparator)
PS C:\temp> $ALDO = Import-CSV -Delimiter ';' .\alfdoende.csv
PS C:\temp> $ALDO[0]
target type result reportdate
------ ---- ------ ----------
aldoen.de ASN OK 2021-04-01
PS C:\temp>
Je ziet nu dat de $ALDO variabele 4 kolommen heeft, waar je de nodige logica op los kunt laten.
Voor het voorbeeld te geven van de mogelijke logica, maak ik even gebruik van een publiek CSV bestand, in dit geval snakes_count_10000.csv, 10000 records met Snakes & Ladders. (Kan iedereen die het wil onderstaand ook nadoen).
PS C:\temp> $ALL = Import-Csv .\snakes_count_10000.csv
PS C:\temp> $ALL[0]
Game Number Game Length
----------- -----------
1 30
Stel ik wil weten hoeveel records in het bestand zitten en de totaalscore van de games. De eerste is uiteraard gemakkelijk: Het staat immers in de bestandsnaam, maar even checken kan geen kwaad. Voor de tweede moeten we alle rijen langs en deze bij elkaar optellen:
PS C:\temp> ($ALL.'Game Length').Count
10000
PS C:\temp> $total=0
PS C:\temp> foreach($game in $ALL.'Game Length') {
>> $total += $game
>> }
PS C:\temp> $total
362544
Mooi! Het zijn er dus inderdaag 10000. En dat de som klopt had ik in Excel ook gemakkelijk kunnen checken. (Data import + autosom, kloar). Het wordt nog mooier: Je kunt in de lussen ook logica op de waarden loslaten en bijvoorbeeld alleen de waarden van de even games bij elkaar optellen:
PS C:\temp> $total=0
PS C:\temp> foreach($game in $ALL) {
>> if ($game.'Game Number' % 2 -eq 0) { $total +=$game.'Game Length'}
>> }
PS C:\temp> $total
182808
Dat kan vast ook in Excel, maar wordt al een stuk moeilijker om te maken :P. Waarom dan niet zelf doen?
We hebben de array met data, we hebben de logica om het te pompen en we hebben Powershell, dus toegang tot Excel.
Met onderstaande code wordt het Excel bestand aangemaakt, gevuld en opgeslagen:
$EXCEL = New-Object -ComObject Excel.Application
$EXCEL.DisplayAlerts = $false
$dummy = $EXCEL.Workbooks.Add()
$idx=1
$EXCEL.Workbooks[$EXCEL.Workbooks.count()].Sheets[$idx].Name = "SNAKES"
$EXCEL.Workbooks[$EXCEL.Workbooks.count()].Sheets[$idx].Cells.Item(1,1) = "GAME"
$EXCEL.Workbooks[$EXCEL.Workbooks.count()].Sheets[$idx].Cells.Item(1,2) = "SCORE"
$row=2
foreach($game in $ALL) {
if ($game.'Game Number' % 2 -eq 0) {
$EXCEL.Workbooks[$EXCEL.Workbooks.count()].Sheets[$idx].Cells.Item(1+$row,1) = $game.'Game Number'
$EXCEL.Workbooks[$EXCEL.Workbooks.count()].Sheets[$idx].Cells.Item(1+$row,2) = $game.'Game Length'
$row++
}
}
$EXCEL.Workbooks[$EXCEL.Workbooks.count()].SaveAs("C:\Temp\snakes.xlsx")
$EXCEL.Workbooks[$EXCEL.Workbooks.count()].Close()
$Excel.Quit()
Duurt overigens wel even om het Excel bestand te vullen. Misschien een Write-Host -NoNewLine "." in de lus opnemen zodat je in elk geval ziet dat er iets bezig is.. Al duurt de lus dan ook flink langer. (Ik had achteraf beter de snakes_count_1000.csv kunnen nemen).Anyway. Een autosom in dit bestand levert keurig 182808, conform verwachting.
Harm ter Veer