Export-CSV column/row value as filename

Question

I've got a bunch of csv files that each look like this:

DD-L-1_test.jaun.Csv

Parameter, Version, Status
OS, 21H2, Compliant
Chrome, 107.1.1, Compliant

DD-L-2_test.smith.Csv

Parameter, Version, Status
OS, 21H2, Compliant
Chrome, 107.1.1, Non-Compliant

The output I'd like is:

PCName, username, OS, Chrome
DD-L-1, test.jaun, Compliant, Compliant
DD-L-2, test.smith, Compliant, Non-Compliant

I've attempted the following solution:

$Results = Get-ChildItem $destPath -Recurse -Include '*.csv' -Force | ForEach-Object {
    $Object = [PSCustomObject]@{
        PCName = $_.BaseName
        
    }
    Import-Csv -Path $_.FullName | ForEach-Object {
        $Object | Add-Member -MemberType NoteProperty -Name $_.Parameter -Value $_.Status -Force
    }
    $Object
}

However, this did not work because unable to create username name column with csv file name only with test.jaun

Answer 1

You can simply put everything in the PSCustomObject. Split the Filename to get the PCName and the UserName, and get the rest from the content. See the following commented code:

# Set Folder Information
$Folder = "C:\Path\To\Csv"
mkdir "$Folder\CombinedCSV" -ErrorAction SilentlyContinue > $null

# Get all CSV and iterate
Get-ChildItem $Folder -Filter *.csv | ForEach-Object {
    # Import the content as utf8
    $Content = Import-Csv $_.FullName -Encoding utf8
    # Get PCName and username by splitting the File BaseName
    $Splitted = $_.BaseName -split '_'
    # Put everything together by simply querying the Status from the Content
    [PSCustomObject]@{
        PCName = $Splitted[0]
        username = $Splitted[1]
        OS = $Content.Where({$_.Parameter -eq 'OS'}).Status
        Chrome = $Content.Where({$_.Parameter -eq 'Chrome'}).Status
    }
# Export to CSV
} | Export-Csv -Path "$Folder\CombinedCSV\CombinedCSV.csv" -Encoding utf8 -Force

Result looks like this:

"PCName","username","OS","Chrome"
"DD-L-1","test.jaun","Compliant","Compliant"
"DD-L-2","test.smith","Compliant","Non-Compliant"

Note that if you don't use PowerShell Core, that you probably want to add -NoTypeInformation to Export-Csv