如何将具有特定格式的文本文件转换为CSV

Question

我已经将一些驱动器文件权限提取到.txt中，并希望将其正确格式化以转换为csv。文本文件的示例位如下。会列出一个驱动器，然后具有权限的用户会紧随其后。并且一个驱动器可以具有一个用户，而另一个驱动器可能具有10-20个用户。

ACL permissions for drive= 'drive'
displayName= name
role= organizer
displayName= name
role= fileOrganizer
displayName= name
role= organizer
displayName= name
role= fileOrganizer
ACL permissions for drive= 'drive'
role= commenter
displayName= name
role= fileOrganizer
displayName= name
role= commenter
displayName= name
role= fileOrganizer
displayName= name
role= organizer
displayName= name
role= fileOrganizer
displayName= name

我对Powershell还是有点陌生，所以不太确定我在做什么。我试着转换成一个哈希表来处理更多的内容，但没有成功。似乎驱动器的单个条目正在折磨我

$txt = Get-Content .teamdriveACL.txt
$txtTrim = $txt.TrimStart()
$splitTxt = $txtTrim.Split("ACL permissions for ")
$hash = $splitTxt | ConvertFrom-StringData

理想情况下，我希望最终的csv与下面的类似

Drive,DisplayName,Role
Drive1,name1,organizer
Drive1,name2,fileOrganizer
Drive1,name3,organizer
Drive2,name1,organizer
Drive2,name2,fileorganizer
Drive2,name3,fileorganizer
Drive2,name4,organizer

Answer 1

在MSP纠纷中伸出援手，他们能够提供帮助。

$output = @"
ACL permissions for drive= 'drive'
displayName= name1
role= organizer
displayName= name2
role= fileOrganizer
displayName= name1
role= organizer
displayName= name4
role= fileOrganizer
ACL permissions for drive= 'drive2'
role= commenter
displayName= name
role= fileOrganizer
displayName= name
role= commenter
displayName= name
role= fileOrganizer
displayName= name
role= organizer
displayName= name
role= fileOrganizer
displayName= name
"@
class GoogleAcl {
[string]$Drive
[string]$DisplayName
[string]$Role
}
function GetAclValue {
param(
[string]$Line
)
return ($Line -split "=")[1].Trim()
}
$GoogleAcls = New-Object -TypeName "System.Collections.ArrayList"
$CurrentDrive = ""
$Lines = $output -split [System.Environment]::NewLine
for ($i = 0; $i -lt $Lines.Count; $i++) {
if ($Lines[$i] -like "*ACL*") {
$CurrentDrive = GetAclValue $Lines[$i]
} else {
if ($Lines[$i] -like "*role*") {
$Role = GetAclValue $Lines[$i]
$DisplayName = GetAclValue $Lines[$i+1]
} else {
$DisplayName = GetAclValue $Lines[$i]
$Role = GetAclValue $Lines[$i+1]
}

$GoogleAcl = New-Object GoogleAcl
$GoogleAcl.Drive = $CurrentDrive
$GoogleAcl.DisplayName = $DisplayName
$GoogleAcl.Role = $Role
$GoogleAcls.Add($GoogleAcl)
$i += 1
}
}
$GoogleAcls | fl

Answer 2

假设您的数据结构是一致的，并且在此之前绝对没有办法获得结构化的数据，那么您可以使用switch语句遍历每一行。首先，每次找到新驱动器时都会创建一个新的哈希表，并更新每个集合的角色/权限。在每个"；角色"输出完整的PSCustomObject。这些对象的总和收集在$output中，您可以简单地将其导出到csv

$tempfile = New-TemporaryFile
@'
ACL permissions for drive= 'drive1'
displayName= name1
role= organizer
displayName= name2
role= fileOrganizer
displayName= name3
role= organizer
displayName= name4
role= fileOrganizer
ACL permissions for drive= 'drive2'
displayName= name1
role= commenter
displayName= name2
role= fileOrganizer
displayName= name3
role= commenter
displayName= name4
role= fileOrganizer
displayName= name5
role= organizer
displayName= name6
role= fileOrganizer
'@ | Set-Content $tempfile

$output = switch -Regex -File $tempfile1 {
'ACL perm.+=s?(S+)$' {
$drive = [Ordered]@{
Drive       = $Matches.1 -replace "'"
DisplayName = ''
Role        = ''
}
}
'displayname=s?(S+)$' {
$drive.DisplayName = $Matches.1
}
'role=s?(S+)$' {
$drive.Role = $Matches.1
[PSCustomObject]$drive
}
}
# only output for user running the script
$output | Format-Table -Auto
# export to csv
$output | Export-Csv pathtofile.csv -NoTypeInformation

请不要将临时文件显示为文本文件的示例。

样本输出

Drive  DisplayName Role         
-----  ----------- ----         
drive1 name1       organizer    
drive1 name2       fileOrganizer
drive1 name3       organizer    
drive1 name4       fileOrganizer
drive2 name1       commenter    
drive2 name2       fileOrganizer
drive2 name3       commenter    
drive2 name4       fileOrganizer
drive2 name5       organizer    
drive2 name6       fileOrganizer