☰

导出 - CSV 列/行值作为文件名

tag-icon • tag-icon powershell powershell-5.0 powershell-2.0 powershell-3.0 powershell-4.0
导出 - CSV 列/行值作为文件名

我有一堆 csv 文件，每个文件看起来都像这样：

DD-L-1_测试.jaun.Csv

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

DD-L-2_测试.smith.Csv

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

我想要的输出是：

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

我尝试了以下解决方案：

$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
}

但是，这不起作用，因为无法仅使用 test.jaun 创建带有 csv 文件名的用户名列

答案1

您可以简单地将所有内容放入 中PSCustomObject。拆分文件名以获取 PCName 和 UserName，然后从内容中获取其余部分。请参阅以下注释代码：

# 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

结果如下：

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

请注意，如果你不使用 PowerShell Core，你可能需要添加-NoTypeInformation到Export-Csv

相关内容