Loop through CSV write output as calculation

This topic contains 4 replies, has 3 voices, and was last updated by  Daryl 15 hours, 59 minutes ago.

  • Author
    Posts
  • #82000

    Daryl
    Participant

    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

  • #82001

    Olaf Soyk
    Participant

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

  • #82018

    Daryl
    Participant

    #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

  • #82040

    Sam Boutros
    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
    
    • #82192

      Daryl
      Participant

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

You must be logged in to reply to this topic.