October 13, 2017 at 3:13 pm

Hello,

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.

Date, Total, DailyTotal
10/10/2017, 5000
10/11/2017, 6300, 1300
10/12/2017, 8200, 1900

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!

Daryl

October 13, 2017 at 3:16 pm

What do you have so far? Show your code please.

October 13, 2017 at 4:18 pm

#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

# Export to new file
Export-csv e:\scripts\revisedoutput.csv

October 13, 2017 at 7:25 pm

# 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

October 16, 2017 at 12:23 pm

Sam,
Works Perfectly! Thanks again for your help!
Daryl