如何使用PowerShell拆分整個串列

在我的 CSV 檔案中，我有“SharePoint Site”列和其他一些列。我正在嘗試從“SharePoint 站點”列中拆分 ID，并將其放入名為“SharePoint ID”的新列中，但不確定如何操作，因此如果我能得到任何幫助或建議，我將不勝感激。

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

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

CSV 檔案

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]

拆分后，這將顯示在新列呼叫“SharePoint ID”下

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

uj5u.com熱心網友回復：

嘗試這個：

# 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

uj5u.com熱心網友回復：

Sharepoint ID通過計算屬性將列附加到現有列的簡潔解決方案：

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$' }
    }
  }

筆記：

• 要查看所有結果列值，請將上述內容通過管道傳輸到Format-List.

• 要將結果重新匯出到 CSV 檔案，請通過管道連接到 Export-Csv

uj5u.com熱心網友回復：

您有 3 種不同的模式試圖從中提取資料。我相信正則運算式將是一個合適的工具。

如果您希望新的 csv 只有單個 ID 列。

$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

如果您想在現有的 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

正則運算式詳情

• . 匹配任意數量的任意字符
• (?=...) 積極展望
• (...) 捕獲組
• \w 匹配一個或多個單詞字符
• ? 惰性量詞
• (?<=...) 正面看背后

uj5u.com熱心網友回復：

這將需要更多的測驗來查看它是否運行良好，但是對于我們擁有的輸入，它的主要概念是System.Uri用于決議字串。從我所看到的，你正在尋找的段始終是第三個[2]，并根據前面路段上進行分割_或剪裁尾隨/或離開該字串為是，如果IsAbsoluteUri是$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

標籤：电源外壳 文件 共享点

上一篇：如何在終端中執行C:\...\executable命令？

下一篇：PowerShell-從字串路徑中洗掉檔案夾