In my Csv file I have a column name "SharePoint Site" that contain data like this

Include:[https://companyName-my.sharepoint.com/personal/VZWM2C_nam_corp_cm_com]
Include:[https://companyName.sharepoint.com/sites/msteams_c578f2/Shared Documents/Forms/AllItems.aspx?id=/sites/msteams_c578f2/Shared Documents/Bittner-Wil3fong - Litigation Hold/Work History&viewid=b3e993a1-e0dc-4d33-8220-5dd7788531384]

1). I'm wondering how can I clean that particular column so that it'll look like this

https://companyName-my.sharepoint.com/personal/VZWM2C_nam_corp_cm_com
https://companyName.sharepoint.com/sites/msteams_c578f2

2). Connect to SharePoint Service and then make two new columns call "Owner" and "Title" in my existing Csv file (with other columns) that contain the owner and title of each sites from "SharePoint Site" column.

# Kinda lost on how to begin the first step 


#My Step #2 Process 
$path = "C:\AuditLogSearch\New folder\modifiedColumn.csv"
$path2 = "C:\AuditLogSearch\New folder\finalFile.csv"

$AdminCenterURL = "https://companyName-admin.sharepoint.com"
Connect-SPOService -url $AdminCenterURL -Credential (Get-Credential)

#Need to import "SharePoint Site" column form "ModifiedColumn.csv" file and find away to loop to the whole list instead of passing one site
Get-SPOSite "https://companyName-my.sharepoint.com/personal/VZWM2C_nam_corp_cm_com" | Select-Object Owner, Title | Export-Csv $path

Any help or suggestion will be really appreciated.

CodePudding user response：

It's very similar to one of your previous questions, still, I would personally use System.Uri to parse the strings:

$csv = Import-Csv $path

foreach($line in $csv)
{
    $uri = [uri]($line.'SharePoint Site' -replace '^Include:\[|]$')
    
    if($uri.IsAbsoluteUri)
    {
        $line.'SharePoint Site' = ( -join (
            $uri.Scheme, '://', $uri.Host, $uri.Segments[1], $uri.Segments[2]
        )).TrimEnd('/')
        continue
    }
    $line.'SharePoint Site' = $uri.OriginalString
}

$csv | Format-Table

Should get you this result:

SharePoint Site
---------------
https://companyname-my.sharepoint.compersonal/elksn7_nam_corp_kl_com
https://companyname-my.sharepoint.compersonal/tzksn_nam_corp_kl_com
https://companyname.sharepoint.comsites/msteams_c578f2
https://companyname.sharepoint.comsites/msteams_c578f2
All

CodePudding user response：

I included the first part of the script to include what you marked as the answer to your last question, that gets the cleaned URLs and site IDs.

What I did below should work to get the SharePoint Owner and Title, and also get you the Site URL and Site ID (from your last question), all in one export file.

Let me know if this works if you have any questions:

$AdminCenterURL = "https://companyName-admin.sharepoint.com"
Connect-SPOService -url $AdminCenterURL -Credential (Get-Credential)

# Import the file with the sites and specify the export path
$Sites = Import-csv "C:\Path\to\Import.csv"
$ExportPath = "C:\Path\to\Export.csv"

# Get the SharePoint ID & SharePoint URL from the import file (Answer from your last question)
$Sites = $Sites | 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$' }
    }
  }


$AllSiteInfo = ForEach($Site in $Sites){
    # Remove "Include:[" and  "]" from the URL
    $spURL = (($Site.'SharePoint Site').replace('Include:[','').replace(']',''))
    
    # Convert from URI to URL
    $spURL = [uri]::UnescapeDataString($spURL)
    
    # Set variable for $spID
    $spID = $Site.'SharePoint ID'

    # In the example csv the '/personal/' URLs do not need to be transformed further, but the '/site/' URLs do. So I added this If statement
    If($spURL -like "*/sites/*"){
        $spURL = $spURL.Substring(0, $spURL.IndexOf($spID)) $spID
    }
    
    # Get site information needed
    $SPOSite = Get-SPOSite $spURL | Select Owner, Title 

    # Add to the export
    [pscustomobject]@{
    'Site Title' = $SPOSite.Title
    'Site URL' = $spUrl
    'Site ID' = $spID
    'Site Owner' = $SPOSite.Owner
    }

}
# Export to specified file
$AllSiteInfo | Export-Csv -NoTypeInformation $ExportPath