Add column CSV in existing CSV with powershell

Question

I have a Powershell script, which collects the size of a backup, and exports it to CSV, I would like to know if it is possible that it could be added to the next csv column, or an excel.

I've been looking at the documentation, because I think it looks better on an excel, but I can't add one more column, I always believe it from scratch.

$today = (get-date).Date
$backup = Get-VBRBackup | where {$_.info.jobname -eq "A. ProduccionInterna.Infraestructura Backup Copy"}
if ($backup) {
$backup.GetAllStorages() | where {$_.CreationTime.Date -eq $today} | select {$_.PartialPath}, {$_.Stats.BackupSize/1GB} |
export-csv -Path C:\Users\acepero\Documents\test.csv -NoTypeInformation -Delimiter ';'
}

UPDATE

I have managed to create new columns once, then it gives an error:

Select-Object : The property cannot be processed because the property "{$_.PartialPath}, {$_.Stats.BackupSize/1GB} , {$Session.BackupStats.DedupRatio} , 
{$Session.BackupStats.CompressRatio}" already exists.

The code now has this form

$today = (get-date).Date
$backup = Get-VBRBackup | where {$_.info.jobname -eq "A. ProduccionInterna.Infraestructura Backup Copy"}
if ($backup) {
$backup.GetAllStorages() | where {$_.CreationTime.Date -eq $today} | select {$_.PartialPath}, {$_.Stats.BackupSize/1GB} , {$Session.BackupStats.DedupRatio} , {$Session.BackupStats.CompressRatio} 
(Import-Csv "C:\Users\acepero\Documents\test.csv") |
    Select-Object *, {{$_.PartialPath}, {$_.Stats.BackupSize/1GB} , {$Session.BackupStats.DedupRatio} , {$Session.BackupStats.CompressRatio}} |
Export-csv -Path C:\Users\acepero\Documents\test.csv -NoTypeInformation #-Delimiter ';' 
}

For one thing, don't 'group' all the calculated properties inside {}. Furthermore, without naming these properties, you'll end up with column names like $Session.BackupStats.DedupRatio. Try creating calculated properties by giving them a name like with @{Name = 'PartialPath' ; Expression = {$_.PartialPath}}. — Theo
– Theo, Commented Feb 18, 2020 at 14:09
Also, I don't understand why the Import-Csv is needed. Are you trying to update a csv file or write out a completely new one with data from the Get-VBRBackup cmdlet? If an update is what you want, please edit your question and add some lines of the original file and also show us what the desired new file should look like. — Theo
– Theo, Commented Feb 18, 2020 at 14:09

Matthew · Accepted Answer · 2020-02-17 22:07:10Z

5

When you take output from a command and pipe it through select, you are creating an output object, which has the selected values as properties. Here is an example using the Get-ChildItem command:

$result = Get-ChildItem C:\Temp | select Name, Length

The $result array contains objects which have the "Length" and "Name" NoteProperties. When you pipe that object to Export-CSV, it creates one column for each Property/NoteProperty the object has. In order to 'add a column to the CSV', all you need to do is add a NoteProperty to the object. You can do that with the Add-Member cmdlet, like this:

$result | Add-Member -MemberType NoteProperty -Name 'ColumnName' -Value 'ColumnValue'

Be careful how you do this. If $result is a single object, this command will add the NoteProperty/Value pair to that object. If $result is an array of objects, it will add that NoteProperty/Value pair to all objects held in the array. If you need to assign different values to each object, you'll need to iterate through the array:

ForEach ($res in $result)
{
    $thisvalue = '' #Assign specific value here
    $res | Add-Member -MemberType NoteProperty -Name 'ColumnName' -Value $thisvalue
}

I hope this helps you. If it does, please don't forget to accept the answer.

answered Feb 17, 2020 at 22:07

Matthew

1,1661 gold badge7 silver badges12 bronze badges

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

1 Comment

Matthew Over a year ago

The syntax of your Select-Object is wrong. I don't understand the * selecting all properties, and then a single ScriptBlock containing 4 additional ScriptBlocks. You're also using the $_ shortcut when you aren't iterating anything. Your Select-Object should only need the parameters you want, not the *, no script blocks, and no $_.

Collectives™ on Stack Overflow

Add column CSV in existing CSV with powershell

UPDATE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related