How to split csv and add additional column using PowerShell-CodePudding

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

    }

CodePudding user response：

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

CodePudding user response：

You could use this nice regex adapted from this answer to capture the Guid on that column and create a new column using a calculated property with Select-Object:

$re = [regex] '(?i)[0-9a-f]{8}-(?:[0-9a-f]{4}-){3}[0-9a-f]{12}'

Import-CSV path\to\myCsv.csv | Select-Object *, @{
    Name       = 'Modified_Id'
    Expression = { $re.Match($_.Exported_Id).Value }
} | Export-Csv path\to\theNewCsv.csv -NoTypeInformation