Loop through CSV write output as calculation

Welcome Forums General PowerShell Q&A Loop through CSV write output as calculation

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
1 year, 4 months ago.

  • Author
    Posts
  • #82000

    Participant
    Points: 0
    Rank: 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

  • #82001

    Participant
    Points: 727
    Helping Hand
    Rank: Major Contributor

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

  • #82018

    Participant
    Points: 0
    Rank: 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

  • #82040

    Participant
    Points: 167
    Helping Hand
    Rank: 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

      Participant
      Points: 0
      Rank: Member

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

The topic ‘Loop through CSV write output as calculation’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort