Using Add-Member to build a new csv file

This topic contains 2 replies, has 2 voices, and was last updated by  Will Anderson 2 years, 5 months ago.

  • Author
    Posts
  • #24869

    Gerald
    Participant

    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

  • #24872

    Gerald
    Participant

    Please ignore.

    Looks to be more an issue with my input file.

    Thanks

  • #24873

    Will Anderson
    Keymaster

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

You must be logged in to reply to this topic.