Adding columns to existing CSV in PowerShell

Question

I'm attempting to take data from a CSV file that already exists and put it into a new CSV with additional columns added through PS.

Existing CSV (firstlast.csv)

First Last
John Doe
Jane Doe

I want to add 2-4 more columns to the CSV with headings that require spaces in them. All the data in each column will be the same though. For example:

NewData.csv

One Street	First Last	New Address
X Street	John Doe	Mary St
X Street	Jane Doe	Mary St

Here is what I've tried:

$Name = Import-Csv C:\firstlast.csv | Select-Object -Property *

$test = @{'One Street' = 'X street' ; 'First Last' = $Name ; 'New Address' = 'Mary Street'} | Select-Object -Property One Street, New Address
$test | ConvertTo-Csv -NoTypeInformation | Export-Csv C:\NewData.csv

See the conceptual about_Calculated_Properties help topic. Also, use either ConvertTo-Csv (for in-memory CSV data generation) or Export-Csv (for writing to a file). — mklement0
– mklement0, Commented Jun 11, 2024 at 17:45

Santiago Squarzon · Accepted Answer · 2024-06-11 17:33:03Z

3

You're kinda close, what you're missing is a loop to enumerate each row from firstlast.csv and then you need to change the syntax in Select-Object, for the new rows and their value you can use what is known as calculated properties:

Import-Csv C:\firstlast.csv | Select-Object @(
    @{ N= 'One Street'; E={ 'X street' }}
    'First Last'
    @{ N='New Address'; E={ 'Mary Street' }}) |
    Export-Csv C:\NewData.csv -NoTypeInformation

If this syntax is hard to understand, what I prefer here is to create custom objects instead of using Select-Object:

Import-Csv C:\firstlast.csv | ForEach-Object {
    [pscustomobject]@{
        'One Street'  = 'X street'
        'First Last'  = $_.'First Last'
        'New Address' = 'Mary Street'
    }
} | Export-Csv C:\NewData.csv -NoTypeInformation

answered Jun 11, 2024 at 17:33

Santiago Squarzon

65.6k5 gold badges26 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Someone · Accepted Answer · 2024-06-11 17:35:31Z

3

Import-Csv "C:\firstlast.csv" |
Add-Member -NotePropertyMembers @{'One Street' = 'X street' ; 'New Address' = 'Mary Street'} -PassThru |
Export-Csv "C:\NewData.csv" -NoTypeInformation

answered Jun 11, 2024 at 17:35

Someone

3501 silver badge6 bronze badges

1 Comment

wp78de Over a year ago

Nice. We could add a | Select to adjust the column order if needed:

Import-Csv "C:\firstlast.csv" | Add-Member -NotePropertyMembers @{'One Street' = 'X street' ; 'New Address' = 'Mary Street'} -PassThru | Select 'One Street','FirstLast','New Address' | Export-Csv "C:\NewData.csv" -NoTypeInformation

Collectives™ on Stack Overflow

Adding columns to existing CSV in PowerShell

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related