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
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)
PSObject
property where you can dynamically access information about the property as e.g. its name.psobject.properties.name
as an array of scalar strings.
$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 pipelineCollections.Generic.List[Object]
typeList<T>.Insert(Int32, T)
Method. This lets you insert a item (in this case an object
) at a certain position (in this case: 1
)
-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 } }