How to convert text file with specific formatting to CSV-CodePudding

I have extracted some drive file permissions into a .txt and would like to get it formatted correctly to convert to a csv. Sample bit of the text file is below. A drive is listed and then the users with permissions follow. And one drive may have one user and another may have 10-20 users.

 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

I am still a bit new to Powershell, so not too sure what I am doing. I have tried converting to a hashtable to work with a bit more, but no luck there. It seems like the single entry of the drive is screwing me

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

Ideally I would like the final csv to be something similar as to below

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

CodePudding user response：

Assuming your data structure is consistent and there is absolutely no way to get the data structured prior to this point, you may use a switch statement to walk each line. First a new hash table is created each time a new drive is located and the role/permission gets updated for each set. After each "Role" a complete PSCustomObject is output. The sum of these objects is collected in $output where you can simply export it to 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 path\to\file.csv -NoTypeInformation

Please not the temp file was just shown as example for your text file.

Sample output

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

CodePudding user response：

Reached out in the MSP discord and they were able to assist with this.

$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