Author Posts

April 30, 2015 at 7:24 am

I'm trying to read a csv file, split some columns into multiple columns, and output that to a new csv file.

My input csv looks like this(there could be many values within the Owner & Delegate columns) –

App,Environment,Group,Owner,Delegates
Abc,def,efg,"lastname1, firstname1 (UserID1), lastname2, firstname2 (UserID2)","lastname3, firstname3 (UserID3), lastname4, firstname4 (USerID4)"

I'm looking to extract the UserID value found between the brackets within (UserID*), and write each value found there out to a new column. The new csv file created should look something like this –

App,Environment,Group,Owner1,Owner2,Delegate1,Delegate2
Abc,def,efg,UserID1,UserID2,UserID3,UserID4

Here is what I have so far –

$data = import-csv $InputFile 

# Columns from the input file that contain delegate ID's we need to query for
$Columns = @("Owner","Delegates")

# Define array for output records
$Merged = New-Object System.Collections.ArrayList

ForEach ($Row in $Data)
{
    # Build output record consisting of Project sizes
    $Output = New-Object -Type PSCustomObject
    
    #$Output | Add-Member -type NoteProperty -Name "APP"         -Value $Row.APP 
    #$Output | Add-Member -type NoteProperty -Name "Environment" -Value $Row.Environment 
    #$Output | Add-Member -type NoteProperty -Name "Group"       -Value $Row.Group 
    
    ForEach ($Column in $Columns)
    {
        $UserID = $Row.$Column.Split('()')
        $i=1
        $ID=1        
        While ($UserID[$i])
        {
            $Name = $Column + $ID 
            $Output | Add-Member -type NoteProperty -Name $Name -Value $UserID[$i] 
            $i = $i + 2
            $ID++
        }
    }
    # Combine into one array        
    $Merged.Add($Output)
}

$Merged
 

My issue is, with all the $Output | Add-Member statements uncommented, my $Merged array only contains the values of the first 3 columns.

However, if I comment out those 3 $Output | Add-Member lines which add those values, then my $Merged array does contain the Owner/Delegate values I'm after.

I'm confused as to why having the first 3 Output | Add-Member lines active seems to interfere with the last one.

Thanks

April 30, 2015 at 8:02 am

Please ignore.

Looks to be more an issue with my input file.

Thanks

April 30, 2015 at 8:08 am

No problem! I was actually looking for an alternate method to handle this. Thanks for the puzzle! 🙂