How to split csv and add additional column using PowerShell

October 4, 2022

I have a csv file and it have multiple columns. I have a specific column name called "Exported_Id" and it look something like this

f36b9decc73e40bfb9a6250198a83fc0/SharePoint/source_28D4CE4DFC53AABB34B41E17BE9DEBBC/item__6761c90b-c1e0-4a91-b72c-e044e5b8cc56_1.0

I’m just wondering how can I split to only this (Spliting before and after _ )

6761c90b-c1e0-4a91-b72c-e044e5b8cc56

and then add it to a new column call "Modified_Id" and export it with existing columns into new csv file.

I’m kinda stuck so any help or suggestion would be really appreciated.

    $CSVImport = Import-CSV $Global:Advance_CSV
    foreach($CSVLine in $CSVImport){
         $CSVExportedID = $CSVLine.Exported_Id

         $Modified_Id= $CSVExportedID -split "__" 

         Select-Object $Modified_Id, "Modified_Id" | Export-Csv $ModifiedFile

    }

>Solution :

You can use a calculated property to extend your existing data set

$Result=
foreach($CSVLine in $CSVImport){
    $CSVLine | 
        Select-Object -Property *,
            @{
                Name = 'Modified_Id'; 
                Expression = {($_.Exported_Id -split "_")[-2]}
            }
}
$Result |
    Format-Table -AutoSize