Loop through CSV write output as calculation

[Daryl Participant Points: 0Rank: Member]

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

[Olaf Participant Points: 727Rank: Major Contributor]

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

[Daryl Participant Points: 0Rank: Member]

#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

[Sam Boutros Participant Points: 167Rank: Participant]

# 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

[Daryl Participant Points: 0Rank: Member]

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

[Author]

Posts