How to specify column position when adding new column to a csv file using PowerShell

Question

How can I specify the column position when adding a new column to an existing csv file?

I want to add the new column as second column (Not at the end what the default is).
The first column is always the same, but the other columns can differ per file (so it is not known on beforehand which columns (names and order) there are (with the exception of the first column, which always contains the name)).

As far as I know there is no position parameter for Add-Member.

Sample code:

$csv = Import-Csv -Path "attendees.csv" -Delimiter ';' 

foreach ($row in $csv)
{
    $row | Add-Member -'GUID' -Value (New-Guid).Guid -MemberType NoteProperty 
}

$csv | Export-Csv new_attendees.csv' -NoTypeInformation

I would use [`Select-Object`](https://learn.microsoft.com/powershell/module/microsoft.powershell.utility/select-object) with a [calculated property](https://learn.microsoft.com/powershell/module/microsoft.powershell.core/about/about_calculated_properties) for this: `Import-Csv -Path "attendees.csv |Select-Object -Property , @{ n='Name'; e={ (New-Guid).Guid } }, ` | Export-Csv ... — iRon, Oct 04 '22 at 12:06
There are a lot of looping in the answers given, but the best way is to use a number of pipes. That would conserve both memory and probably be much faster than all the looping. Here's the main trick: `$csv | ConvertFrom-Csv -Header $csv_header_old | Select-Object -Property $csv_header_new | ForEach-Object {...} | ConvertTo-Csv ...`. — not2qubit, Dec 11 '22 at 05:32

iRon · Accepted Answer · 2022-12-11T15:22:24.793

In case you do not know the column names at forehand.
Using Select-Object with a calculated property for this:

$csv = Import-Csv -Path "attendees.csv" -Delimiter ';' 
$Properties = [Collections.Generic.List[Object]]$csv[0].psobject.properties.name
$Properties.Insert(1, @{ n='Guid'; e={ New-Guid } }) # insert at column #1
$csv |Select-Object -Property $Properties |Export-Csv new_attendees.csv' -NoTypeInformation

Explanation: (Updated 2022-11-12)

Each object PowerShell has een hidden PSObject property where you can dynamically access information about the property as e.g. its name.
Using the PowerShell Member-Access enumeration feature will list all the psobject.properties.name as an array of scalar strings.
- I am using just the first object $csv[0] to determine the property (column) names as I do not want to choke the PowerShell pipeline and continue to support one-at-a-time processing. In other words, I presume that the following objects have unified property names. Any well written PowerShell cmdlet follows the strongly encouraged development guideline to implement for the middle of a pipeline
Thanks to the impliciet .Net conversion, it is easy to type cast the PowerShell Array (of property names) to the Collections.Generic.List[Object] type
Which happens to have a List<T>.Insert(Int32, T) Method. This lets you insert a item (in this case an object) at a certain position (in this case: 1)
- Note that this method is The zero-based
The -Property parameter of the Select-Object cmdlet, doesn't just support an ordered list of property names but also calculated properties which is used here to create complete property along with its name, value (expression) in the form of:
@{ n='Guid'; e={ New-Guid } }

Interesting, but would be more helpful if you could also explain what the `$Properties` line(s) does. — not2qubit, Dec 11 '22 at 05:17

mclayton · Answer 2 · 2022-10-04T12:32:53.220

I don't think there's an easy way to insert a property into an object in-place at a specific index, but here's a quick proof-of-concept I knocked out to create a new object based on the original...

It could do with some error handling and perhaps some parameter attributes to support the pipeline, but it basically works...

function Insert-NoteProperty
{

    param(
        [pscustomobject] $InputObject,
        [string] $Name,
        [object] $Value,
        [int] $Index
    )

    $properties = @($InputObject.psobject.Properties);

    $result = [ordered] @{};

    # append the properties before the index
    for( $i = 0; $i -lt $Index; $i++ )
    {
        $result.Add($properties[$i].Name, $properties[$i].Value);
    }

    # append the new property
    $result.Add($Name, $Value);

    # append the properties after the index
    for( $i = $Index; $i -lt $properties.Length; $i++ )
    {
        $result.Add($properties[$i].Name, $properties[$i].Value);
    }

    return [pscustomobject] $result;

}

Example:

$original = [pscustomobject] [ordered] @{ "aaa"="bbb"; "ccc"="ddd" }

$original
# aaa ccc
# --- ---
# bbb ddd

$updated = Insert-NoteProperty `
    -InputObject $original `
    -Name        "new" `
    -Value       "value" `
    -Index       1;

$updated
# aaa new   ccc
# --- ---   ---
# bbb value ddd

You can use this with a csv file as follows:

$csv = @"
aaa,ccc
bbb,ddd
"@

$data = $csv | ConvertFrom-Csv;

$newdata = $data | foreach-object {
    Insert-NoteProperty `
        -InputObject $_ `
        -Name        "new" `
        -Value       "value" `
        -Index       1
}

$newcsv = $newdata | ConvertTo-Csv

$newcsv
# "aaa","new","ccc"
# "bbb","value","ddd"

@mclaton How can I create a custom object using 'Import-CSV' — John Doe, Oct 04 '22 at 12:28
```Inport-CSV``` returns pscustomobjects for you - e.g. ```("aaa, ccc`r`nbbb, ddd" | convertfrom-csv)[0].gettype().fullname``` gives ```System.Management.Automation.PSCustomObject``` — mclayton, Oct 04 '22 at 12:29
Added an example with ```ConvertFrom-Csv```, but should work the same with ```Import-Csv```. Integrating it with the pipeline would let it be a bit more succinct - left as an exercise for the reader ;-). — mclayton, Oct 04 '22 at 12:33

Toni · Answer 3 · 2022-10-04T12:31:35.430

Add-Member does not let you specify at which position to add a property to an object. So you have to build a new object where you can control the position of the property, e.g.:

$newObjects = @(
    foreach ($row in $csv){
        $attrsHt = [ordered]@{
            [propertyName]=[propertyValue]
            Guid=([guid]::NewGuid()).guid
            [propertyName]=[propertyValue]
            [propertyName]=[propertyValue]
        }
        New-Object -TypeName psobject -Property $attrsht
    }
)

Or you can use select-object to change the order of the output:

foreach ($row in $csv)
{
    $row | Add-Member -Name 'GUID' -Value (New-Guid).Guid -MemberType NoteProperty 
}

$csv | select-object [propertyName],Guid,[propertyName],[propertyName] | export-csv new_attendees.csv' -NoTypeInformation

Ok as the propertyNames are unknown/dynamic you could do:

#Get the csv header and split by delimiter to get array of property names
[System.Collections.ArrayList]$csvHeader = (get-content [path])[0] -split ","

#Insert Guid at 2nd Position
$csvHeader.insert(1,'GUID')

$csv | select-object $csvHeader | export-csv new_attendees.csv -NoTypeInformation

indeed missed the at... but what is the benefit of the CAST vs. new-object? — Toni, Oct 04 '22 at 12:17
@Toni It is not known on beforehand which columns (names and order) there are. — John Doe, Oct 04 '22 at 12:18

How to specify column position when adding new column to a csv file using PowerShell

3 Answers3