I have a script that creates a dailyexport.csv that looks like this:
Date, Total
10/10/2017, 5000
10/11/2017, 6300
10/12/2017, 8200
I would like to import the csv file above, then add another column called “DailyTotal” and then script the calculation for “DailyTotal”. Then output the .csv to a new.csv would look like this.
I’ve searched for answers but can’t seem to find anything where a calculation is performed on a cell value from the previous line. Any help would be greatly appreciated!
#Import the CSV, Select all Columns and write new column DailyTotal
Import-csv e:\scripts\dailyoutput.csv | Select*, DailyTotal #Need to calculate DailyTotal of last row in file by subtracting current Total from previous Total
# Import the CSV
$myInput = Import-Csv .\dailyoutput.csv | sort Date # sorting is critical for calculation of daily total
# Add 3rd column
$myInput | Add-Member -MemberType NoteProperty -Name DailyTotal -Value $null
# Calculate DailyTotal as the delta between current day total and prior day total
0..($myInput.Count-1) | % {
if ($_ -gt 0) {
$myInput[$_].DailyTotal = $myInput[$_].Total - $myInput[$_-1].Total
}
}
# Show result on console
$myInput | FT -a
# Export to new file
$myInput | Export-Csv .\revisedoutput.csv -NoTypeInformation