如何将大型垂直文本文件转置和解析为带标头的CSV文件

Question

我有一个大型文本文件（*.txt）以以下格式：

; KEY 123456
; Any Company LLC
; 123 Main St, Anytown, USA
SEC1 = xxxxxxxxxxxxxxxxxxxxx
SEC2 = xxxxxxxxxxxxxxxxxxxxx
SEC3 = xxxxxxxxxxxxxxxxxxxxx
SEC4 = xxxxxxxxxxxxxxxxxxxxx
SEC5 = xxxxxxxxxxxxxxxxxxxxx
SEC6 = xxxxxxxxxxxxxxxxxxxxx

重复大约350-400键。这些是HASP键和与之相关的SEC代码。我试图将此文件用键和sec1 -sec6作为标题将其解析到CSV文件中，并将行填充。这是我试图提到的格式：

KEY,SEC1,SEC2,SEC3,SEC4,SEC5,SEC6
123456,xxxxxxxxxx,xxxxxxxxxxx,xxxxxxxxxx,xxxxxxxxxx,xxxxxxxxxx,xxxxxxxxxx
456789,xxxxxxxxxx,xxxxxxxxxx,xxxxxxxxxx,xxxxxxxxxx,xxxxxxxxxx,xxxxxxxxxx

我已经能够获得一个脚本以导出到文本文件中的一个键（我的测试文件）中的一个键，但是当我尝试在完整列表上运行它时，它只导出最后一个密钥和sec代码。

$keysheet = '.AllKeys.txt'
$holdarr = @{}
Get-Content $keysheet | ForEach-Object {
if ($_ -match "KEY") {
    $key, $value = $_.TrimStart("; ") -split " "
    $holdarr[$key] = $value }
elseif ($_ -match "SEC") {
    $key, $value = $_ -split " = "
    $holdarr[$key] = $value }
}
$hash = New-Object PSObject -Property $holdarr
$hash | Export-Csv -Path '.allsec.csv' -NoTypeInformation

当我在完整列表上运行它时，它还添加了一些额外的列，其中包含属性而不是值。

将不胜感激。

谢谢。

Answer 1

这是我建议的方法：

$output = switch -Regex -File './AllKeys.txt' {
    '^; KEY (?<key>d+)' {
        if ($o) {
            [pscustomobject]$o
        }
        $o = @{
            KEY = $Matches['key']
        }
    }
    '^(?<sec>SEC.*?)s' {
        $o[$Matches['sec']] = ($_ | ConvertFrom-StringData)[$Matches['sec']]
    }
    default {
        Write-Warning -Message "No match found: $_"
    }
}
# catch the last object
$output += [pscustomobject]$o
$output | Export-Csv -Path './some.csv' -NoTypeInformation

Answer 2

这将是一种方法。

& {
    $entry = $null
    switch -Regex -File '.AllKeys.txt' {
        "KEY" {
            if ($entry ) {
                [PSCustomObject]$entry
            }
            $entry = @{}
            $key, $value = $_.TrimStart("; ") -split " "
            $entry[$key] = [int]$value
        }
         "SEC" {
            $key, $value = $_ -split " = "
            $entry[$key] = $value 
        }
    }
    [PSCustomObject]$entry
} | sort KEY | select KEY,SEC1,SEC2,SEC3,SEC4,SEC5,SEC6 |
Export-Csv -Path '.allsec.csv' -NoTypeInformation

Answer 3

让我们利用

的ConvertFrom-StringData的强度

将包含一个或多个键和值对的字符串转换为哈希表。

所以我们要做的是

1. 分为文本块
2. 编辑";键"行
3. 卸下空白线或半隆线。
4. 传递到ConvertFrom-StringData创建一个标签
5. 将其转换为powershell对象

---

$path = "c:tempkeys.txt"
# Split the file into its key/sec collections. Drop any black entries created in the split
(Get-Content -Raw $path) -split ";s+KEYs+" | Where-Object{-not [string]::IsNullOrWhiteSpace($_)} | ForEach-Object{
    # Split the block into lines again
    $lines = $_ -split "`r`n" | Where-Object{$_ -notmatch "^;" -and -not [string]::IsNullOrWhiteSpace($_)}
    # Edit the first line so we have a full block of key=value pairs.
    $lines[0] = "key=$($lines[0])"
    # Use ConvertFrom-StringData to do the leg work after we join the lines back as a single string.
    [pscustomobject](($lines -join "`r`n") | ConvertFrom-StringData)
} | 
    # Cannot guarentee column order so we force it with this select statement.
    Select-Object KEY,SEC1,SEC2,SEC3,SEC4,SEC5,SEC6

现在使用Export-CSV对您的心脏满足。