Add Column to CSV Windows PowerShell

I have a fairly standard csv file with headers I want to add a new column & set all the rows to the same data.

Original:

column1, column2
1,b
2,c
3,5

After

column1, column2, column3
1,b, setvalue
2,c, setvalue
3,5, setvalue

I can't find anything on this if anybody could point me in the right direction that would be great. Sorry very new to Power Shell.

Solution 1:

Here's one way to do that using Calculated Properties:

Import-Csv file.csv | 
Select-Object *,@{Name='column3';Expression={'setvalue'}} | 
Export-Csv file.csv -NoTypeInformation

You can find more on calculated properties here: http://technet.microsoft.com/en-us/library/ff730948.aspx.

In a nutshell, you import the file, pipe the content to the Select-Object cmdlet, select all exiting properties (e.g '*') then add a new one.

Solution 2:

The ShayLevy's answer also works for me!

If you don't want to provide a value for each object yet the code is even easier...

Import-Csv file.csv | 
Select-Object *,"column3" | 
Export-Csv file.csv -NoTypeInformation

Solution 3:

None of the scripts I've seen are dynamic in nature, so they're fairly limited in their scope & what you can do with them.. that's probably because most PS Users & even Power Users aren't programmers. You very rarely see the use of arrays in Powershell. I took Shay Levy's answer & improved upon it.

Note here: The Import needs to be consistent (two columns for instance), but it would be fairly easy to modify this to dynamically count the columns & generate headers that way too. For this particular question, that wasn't asked. Or simply don't generate a header unless it's needed.

Needless to say the below will pull in as many CSV files that exist in the folder, add a header, and then later strip it. The reason I add the header is for consistency in the data, it makes manipulating the columns later down the line fairly straight forward too (if you choose to do so). You can modify this to your hearts content, feel free to use it for other purposes too. This is generally the format I stick with for just about any of my Powershell needs. The use of a counter basically allows you to manipulate individual files, so there's a lot of possibilities here.

$chargeFiles = 'C:\YOURFOLDER\BLAHBLAH\'
$existingReturns = Get-ChildItem $chargeFiles

for ($i = 0; $i -lt $existingReturns.count; $i++)
{ 

$CSV = Import-Csv -Path $existingReturns[$i].FullName -Header Header1,Header2
$csv | select *, @{Name='Header3';Expression={'Header3 Static'}}
     | select *, @{Name='Header4';Expression={'Header4 Static Tet'}}
     | select *, @{Name='Header5';Expression={'Header5 Static Text'}}|
CONVERTTO-CSV -DELIMITER "," -NoTypeInformation |
SELECT-OBJECT -SKIP 1 | % {$_ -replace '"', ""} | 
OUT-FILE -FilePath $existingReturns[$i].FullName -FORCE -ENCODING ASCII

}

Solution 4:

You could also use Add-Member:

$csv = Import-Csv 'input.csv'

foreach ($row in $csv)
{
    $data = Get-DataFromSomewhere -Id $row.Id

    $row | Add-Member -Name 'first_name' -Value $data.FirstName -MemberType NoteProperty
    $row | Add-Member -Name 'last_name' -Value $data.LastName -MemberType NoteProperty
}

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