How can I perform arithmetic to find differences of values in two CSVs?

Question

I'm able to diff contents ("cells") of two CSVs and get output of the highest value using the following function:

Compare-Object $oldfile $latestfile -Property  "UsedSize" -PassThru |  select-Object  "VolumeName", "UsedSize" | export-csv c:\kits\_write\snapshots\voldelta.csv -NoTypeInfo

However, I wish to get the delta/difference between the values in the cells.

Is it possible to perform arithmetic to find differences of values in two CSVs?

To provide context, we're increasing our NetApp volume snapshot schedule, for example, from six hours to 15 minutes. I'd like to start collecting the delta from time N to time N+15 minutes to find out an estimated over all growth rate for data on the volume (including the snapshots, of course) before we implement the change. Yes, snap delta is the Data ONTAP method for producing this for existing snapshots.

Thanks

[update in regards to comment]

file A:

VolumeName,TotalSize,AvailableSize,UsedSize
netapp_vol1,375809648400,101430421264,274379237136

file B:

VolumeName,TotalSize,AvailableSize,UsedSize
netapp_vol1,375809638400,101430456320,274379182080

I found a "lead" by using something like the following:

$combocsv = $oldfile + $latestfile
$combocsv | group VolumeName | select name,@{Name="Totals";Expression={($_.group | Measure-Object -sum UsedSize).sum}}

Except that I need to perform a difference with measure-object not a summation. I'm having some trouble locating how to do that.

Resolved as per the selected answer below!

$oldfilecsv = @{}
$oldfile = import-csv "C:\kits\_write\snapshots\filera_version.csv" | foreach-object { $oldfilecsv[$_.VolumeName] = [long]$_.UsedSize }
$latestfilecsv = @{}
$latestfile = import-csv "C:\kits\_write\snapshots\filera_latest.csv" | foreach-object { $latestfilecsv[$_.VolumeName] = [long]$_.UsedSize }

$deltas = @{}

foreach ( $volume in $latestfilecsv.keys ) {
  $delta = $latestfilecsv[$volume] - $oldfilecsv[$volume]
  $deltas.add($volume, $delta)
}

Can you show us two data samples? ie. two sample csv's you would like to compare and calculate difference over? — Mathias R. Jessen
Thanks for your reply, Mathias. I've updated the question with a single row example. — mbrownnyc

Ansgar Wiechers Ansgar Wiechers · Accepted Answer · 2017-04-17T14:06:46

Imported CSV values are strings, so you proabably need to convert the values to an appropriate type, e.g. with a calculated property:

$csv = Import-Csv 'C:\path\to\input.csv' |
       Select-Object VolumeName, @{n='UsedSize';e={[long]$_.UsedSize}}

The above replaces the (string) property UsedSize with a property of the same name, but with the string cast to a long integer value.

However, since you want to calculate a delta between two different files, I'd recommend to import the data into a hashtable rather than doing a straight CSV import:

$csv = @{}
Import-Csv 'C:\path\to\input.csv' | ForEach-Object {
  $csv[$_.VolumeName] = [long]$_.UsedSize
}

That will give you the advantage of being able to look up the value for any given volume.

With the data of your input CSVs in different hashtables you can easily calculate differences like this:

foreach ($volume in $csv1.Keys) {
  $delta = $latestCsv[$volume] - $oldCsv[$volume]
  #
  # further processing goes here, like displaying $delta on the screen
  # or putting the value in another hashtable
  #
}

How can I perform arithmetic to find differences of values in two CSVs?

1 Answers