Powershell CSV Clarity…

January 18, 2022

If you are having trouble working with CSV files in Powershell, all you need to know is that it’s just an array full of objects. This is the most straight forward and least confusing way to work with them.

This working code example will create and save a CSV. It then reads the newly created CSV and makes changes then saves it back out with the updates. Make sure to change the input and output paths if you need to, or make sure you have a temp folder under C:\.

CLS

# Proper way to create a CSV in simplist terms. This would typically be done in a loop.

$Contents = @()

$Row = [PsCustomObject]@{Name="fred";Age=35;Description="husband.dumb"}
$Contents += $Row

$Row = [PsCustomObject]@{Name="Barney";Age=34;Description="husband.dumber"}
$Contents += $Row

$Row = [PsCustomObject]@{Name="Wilma";Age=34;Description="wife.smart"}
$Contents += $Row

$Row = [PsCustomObject]@{Name="betty";Age=34;Description="wife.dumb"}
$Contents += $Row

$Row = [PsCustomObject]@{Name="Dino";Age=6;Description="dog.smart"}
$Contents += $Row

$Row = [PsCustomObject]@{Name="Pebbles";Age=2;Description="baby.smart"}
$Contents += $Row

$Row = [PsCustomObject]@{Name="Bambam";Age=2;Description="baby.strong"}
$Contents += $Row

$Contents | Export-Csv "c:\temp\Flintstones1.csv" -NoTypeInformation


# Changing and adding column data on the fly:

$TextInfo = (Get-Culture).TextInfo

Import-Csv "c:\temp\Flintstones1.csv" | ForEach-Object {
    if ($_.Description.Length -eq 10) {
        Write-host $_.Description.Length
    }

    if ($_.Description.Contains('.')) {
        $DSC = $_.Description.Split('.')[0]
        $_.Description = $DSC
    }
    $_.Name = $TextInfo.ToTitleCase($_.Name)

    $Row = [PsCustomObject]@{Name=$_.Name;Age=$_.Age;Description=$_.Description;NEW_COUNT=$_.Description.Length}
    $Row

} | Export-Csv "c:\temp\Flintstones2.csv" -NoTypeInformation


# Another way that can replace the input file - This will overwrite Flintstones1.csv we created earlier.

$Contents = Import-Csv "c:\temp\Flintstones1.csv"
$TmpArr = @()

ForEach ($_ in $Contents) {
    if ($_.Description.Length -eq 10) {
        Write-host $_.Description.Length
    }

    if ($_.Description.Contains('.')) {
        $DSC = $_.Description.Split('.')[0]
        $_.Description = $DSC
    }
    $_.Name = $TextInfo.ToTitleCase($_.Name)

    $Row = [PsCustomObject]@{Name=$_.Name;Age=$_.Age;Description=$_.Description;NEW_COUNT=$_.Description.Length}
    $TmpArr += $Row

}
$TmpArr | Export-Csv "c:\temp\Flintstones1.csv" -NoTypeInformation
Tags: , ,