How to split through the whole list using PowerShell-CodePudding

In my CSV file I have "SharePoint Site" column and a few other columns. I'm trying to split the ID from "SharePoint Site" columns and put it to the new column call "SharePoint ID" but not sure how to do it so I'll be really appreciated If I can get any help or suggestion.

$downloadFile = Import-Csv "C:\AuditLogSearch\New folder\Modified-Audit-Log-Records.csv"

(($downloadFile -split "/") -split "_") | Select-Object -Index 5

CSV file

SharePoint Site
Include:[https://companyname-my.sharepoint.com/personal/elksn7_nam_corp_kl_com]
Include:[https://companyname-my.sharepoint.com/personal/tzksn_nam_corp_kl_com]
Include:[https://companyname.sharepoint.com/sites/msteams_c578f2/Shared Documents/Forms/AllItems.aspx?id=%2Fsites%2Fmsteams_c578f2%2FShared Documents%2FBittner%2DWilfong%20%2D Litigation Hold%2FWork History&viewid=b3e993a1-e0dc-4d33-8220-5dd778853184]
Include:[https://companyname.sharepoint.com/sites/msteams_c578f2/Shared Documents/Forms/AllItems.aspx?id=%2Fsites%2Fmsteams_c578f2%2FShared Documents%2FBittner%2DWilfong%20%2D Litigation Hold%2FWork History&viewid=b3e993a1-e0dc-4d33-8220-5dd778853184]
Include:[All]

After spliting this will show it under new Column call "SharePoint ID"

SharePoint ID
2. elksn
3. tzksn
4. msteams_c578f2
5. msteams_c578f2
6. All

CodePudding user response：

Try this:

# Import csv into an array
$Sites = (Import-Csv C:\temp\Modified-Audit-Log-Records.csv).'SharePoint Site'

# Create Export variable
$Export = @()

# ForEach loop that goes through the SharePoint sites one at a time
ForEach($Site in $Sites){
    
    # Clean up the input to leave only the hyperlink
    $Site = $Site.replace('Include:[','')
    $Site = $Site.replace(']','')

    # Split the hyperlink at the fifth slash (Split uses binary, so 0 would be the first slash)

    $SiteID = $Site.split('/')[4]

    # The 'SharePoint Site' Include:[All] entry will be empty after doing the split, because it has no 4th slash.
    # This If statement will detect if the $Site is 'All' and set the $SiteID as that.

    if($Site -eq 'All'){
        $SiteID = $Site
    }

    # Create variable to export Site ID
    $SiteExport = @()
    $SiteExport = [pscustomobject]@{
        'SharePoint ID' = $SiteID
    }
    
    # Add each SiteExport to the Export array
    $Export  = $SiteExport
}

# Write out the export
$Export

CodePudding user response：

A concise solution that appends a Sharepoint ID column to the existing columns by way of a calculated property:

Import-Csv 'C:\AuditLogSearch\New folder\Modified-Audit-Log-Records.csv' | 
  Select-Object *, @{
    Name = 'SharePoint ID'
    Expression = {
      $tokens = $_.'SharePoint Site' -split '[][/]'
      if ($tokens.Count -eq 3) { $tokens[1] } # matches 'Include:[All]'
      else                     { $tokens[5] -replace '_nam_corp_kl_com$' }
    }
  }

Note:

To see all resulting column values, pipe the above to Format-List.
To re-export the results to a CSV file, pipe to Export-Csv

CodePudding user response：

You have 3 distinct patterns you are trying to extract data from. I believe regex would be an appropriate tool.

If you are wanting the new csv to just have the single ID column.

$file = "C:\AuditLogSearch\New folder\Modified-Audit-Log-Records.csv"

$IdList = switch -Regex -File ($file){
    'Include:. (?=/(\w ?)_)(?<=personal)' {$matches.1}
    'Include:(?=\[(\w )\])' {$matches.1}
    'Include:. (?=/(\w ?)/)(?<=sites)' {$matches.1}
}

$IdList |
    ConvertFrom-Csv -Header "Sharepoint ID" |
        Export-Csv -Path $newfile -NoTypeInformation

If you want to add a column to your existing CSV

$file = "C:\AuditLogSearch\New folder\Modified-Audit-Log-Records.csv"

$properties = ‘*’,@{
    Name = 'Sharepoint ID'
    Expression = {
        switch -Regex ($_.'sharepoint Site'){
            'Include:. (?=/(\w ?)_)(?<=personal)' {$matches.1}
            'Include:(?=\[(\w )\])' {$matches.1}
            'Include:. (?=/(\w ?)/)(?<=sites)' {$matches.1}
        }
    }
}

Import-Csv -Path $file |
    Select-Object $properties |
        Export-Csv -Path $newfile -NoTypeInformation

Regex details

. Match any amount of any character
(?=...) Positive look ahead
(...) Capture group
\w Match one or more word characters
? Lazy quantifier
(?<=...) Positive look behind

CodePudding user response：

This would require more testing to see if it works well, but with the input we have it works, the main concept is to use System.Uri to parse the strings. From what I'm seeing, the segment you are looking for is always the third one [2] and depending on the previous segments, perform a split on _ or trim the trailing / or leave the string as is if IsAbsoluteUri is $false.

$csv = Import-Csv path/to/test.csv

$result = foreach($line in $csv)
{
    $uri = [uri]($line.'SharePoint Site' -replace '^Include:\[|]$')
    
    $id = switch($uri)
    {
        {-not $_.IsAbsoluteUri} {
            $_
            break
        }
        { $_.Segments[1] -eq 'personal/' } {
            $_.Segments[2].Split('_')[0]
            break
        }
        { $_.Segments[1] -eq 'sites/' } {
            $_.Segments[2].TrimEnd('/')
        }
    }

    [pscustomobject]@{
        'SharePoint Site' = $line.'SharePoint Site'
        'SharePoint ID'   = $id 
    }
}

$result | Format-List