locked
Help with pulling data from within same sheet if within a date range RRS feed

  • Question

  • I am actually working with excel using powershell. I have an Weeknum column. We need to be able to pull totals on from the row that falls within that weekend so the totals are in increments until the week is finished. then a new week starts. I am getting to far ahead.

    I have a row with a date of say (A,1) 12/22/2018 with 4 columns next to it (B,2)(C,2)(D,2)(E,2) (this would be the Week to Date) that would need the total from other columns that are updated once per day each row with their own dates, but only if they fall within the week in (A,1).

    How do I tell B-E,2 to only pull from the daily pull but only go back to the beginning of the week ending in (A,1), would then need to add each of the columns together that fall within the date range and drop them into the B-E,2.

    Is this too much to take in or is there a genius out there that might be able to guide me through this?

    Thank you!

    Paul

     
        # Set Variables for the remainder of the script
        $date = Get-Date -Format M-dd-yyyy
        $BSPath = "\\server\Accounts$\Organization_PRTG.csv"
        $FirstPull = "C:\PRTG\FirstPull"
        $FinalPull = "C:\PRTG\FinalPull"
        $Historical = "C:\PRTG\Historical"
        CD $FirstPull
        # We will need to change the C:\temp\CSVExport.exe execute location to a server that has Office installed on it.
    
    # Imports the csv file
    $GetCSV = Import-Csv $BSPath
    
    # This foreach command will take each row in the csv file above one by one and allow us
    # to work through each row one at a time
    foreach ($Site in $GetCSV)
        {
        # Extracts the Site column only
        $SiteName = $site | Select-Object -Property Site -ExpandProperty Site
    
        # Extracts the PRTG ID only
        $SiteID = $site | Select-Object -Property ID -ExpandProperty ID
    
        # Because the CSVEport command does not like to use partial variables, we needd to combine the full command into
        # a single variable
        $Full = "-s=" + "$SiteID"
        $PRTGSave = "-f=" + "$FirstPull"
        C:\Temp\CSVExport.exe -ps=https://monitoring.mysite.com:444 $Full $PRTGSave
    
        # Pulls the csv that has the same name as the site i.e. (the for each inside this command), then renames it to
        # something we can use. the above executable, gives it a crazy name.
        Get-ChildItem $FirstPull\*.csv | where {$_.name -like "*$siteID*" } | Rename-Item -NewName "$SiteName.csv" -Force
    
        # A variable we can use as we go for saving
        $SaveToo = "$FirstPull\$SiteName.csv"
    
        # Imports the file we just created, then calls aout each of the columns by number, 1=A, 2=B, etc. then selects
        # that ones we want to use (the original pull add a "RAW" column, which does not serve a purpose for us), removes
        # the no type information (windows garbage), skips the first line where this information was and exports back out.
        (Import-CSV $SaveToo -Header 1,2,3,4,5,6,7,8,9,10,11,12 | 
        Select "1","5","7","9","11" | 
        ConvertTo-Csv -NoTypeInformation | 
        Select-Object -Skip 1) -replace '"' | Set-Content $SaveToo
    
        # Here we wanted to take a little nap before we move on to allow for the above command to complete. 
        Sleep -Seconds 5
    
        # Compines the current date format that I wanted to use to the file we have been working on.
        $outfile = "$date " + "$SiteName.xlsx"
    
        # We will use the above function and call it to convert the csv file to an xlsx, so that we can add some formulas
        # and functions to use with an excel sheet
        ConvertCSV-ToExcel -inputfile "$SiteName.csv"  -output "$outfile"
    
        # Combines the initial path with the new file name
        $FinalName = "$FirstPull\$outfile"
    
    
        $excel = new-object -comobject Excel.Application
     
        # Create a new workbook
        $workbook = $excel.workbooks.Open($FinalName)
    
        # Here we are calling the excel to create a new worksheet and name it DATA
        $workbook = $Excel.Worksheets.add()
        $workbook.name = "DATA"
    
        # Save the workbook, so that the exiting of the sheet can occur
        $workbook.Save
        $workbook.close
    
        # Reopen the sheet
        $workbook = $excel.workbooks.Open($FinalName)
    
        # Make Excel visible if you wanted to watch what is happening, very cool...
            #       $excel.visible = $true
    
        # Locate the names of the sheets and assign a variable to work with each one seperately.
        $s2 = $workbook.sheets | where {$_.name -eq 'DATA'}
        $s1 = $workbook.sheets | where {$_.name -eq $SiteName}
    
        # Next update some cells in the worksheet for the site
        $date = Get-Date -Format M-d-yyyy
        $S1.cells.item(1,7).value2 ="Report Date"
        $S1.cells.item(2,7).value2 ="$date"
        $S1.cells.item(1,8).value2 = "Internet"
        $S1.cells.item(1,9).value2 = "Email"
        $S1.cells.item(1,10).value2 = "IP Phone"
        $S1.cells.item(1,10).value2 = "Wireless"
    
        # Setting a formula to tally up the "UP's"
        $s1.cells.item(2,8) ="=COUNTIF(C2:C1441,""Up"")"
        $s1.cells.item(2,9) ="=COUNTIF(D2:D1441,""Up"")"
        $s1.cells.item(2,10) ="=COUNTIF(E2:E1441,""Up"")"
        $s1.cells.item(2,11) ="=COUNTIF(F2:F1441,""Up"")"
    
        # Divide the total number from the UP's into 1440 (these are munutes in a day)
        $s1.cells.item(3,8) ="=H2/1440"
        $s1.cells.item(3,9) ="=I2/1440"
        $s1.cells.item(3,10) ="=J2/1440"
        $s1.cells.item(3,11) ="=K2/1440"
    
        # This sheet is the DATA sheet. This is where the information
        # gathered from the site sheet into information we can use, set as a percentage
        # which will be moved over to to a parent sheet that can be supplied to the client and management
        $s2.range("A1:A1").cells="Report Date"
        $s2.range("A2:A2").cells="$date"
        $S2.cells.item(1,2).value2 = "Internet"
        $S2.cells.item(1,3).value2 = "Email"
        $S2.cells.item(1,4).value2 = "IP Phone"
        $S1.cells.item(1,10).value2 = "Wireless"
    
        # THis will convert the number to a percentage
        $s2.cells.item(2,2) ="=TEXT('$SiteName'!H3,""00.000%"")"
        $s2.cells.item(2,3) ="=TEXT('$SiteName'!I3,""00.000%"")"
        $s2.cells.item(2,4) ="=TEXT('$SiteName'!J3,""00.000%"")"
        $s2.cells.item(2,5) ="=TEXT('$SiteName'!K3,""00.000%"")"
     
     
        # This will save the worksheet we just edited:
        $FinalSave = "$FinalPull\$outfile"
        $s1.saveas($FinalSave)
        $workbook.close()
        $excel.close
    }
    
    # Removing all the csv files from the original pull
    Get-ChildItem $FirstPull\*.csv | Remove-Item
    #Move-Item $FinalPull\* -Destination $Historical


    • Edited by PDGPros Saturday, December 22, 2018 5:29 AM updating with initial script
    Saturday, December 22, 2018 1:30 AM

All replies

  • What?

    Is this a CSV?  If it is Excel then you need to use the Excel object model to do this.

    Start by posting your script.  I will point you in the right direction once I can make sense out of your ramble.  For example what is "totals on" ? Dates cannot be totaled.

    A range can be summed if it is a numeric range.


    \_(ツ)_/

    Saturday, December 22, 2018 2:01 AM
  • Hi Paul and Welcome to the Powershell forum.

    If you're asking for a sophisticated Excel formula you should try it in Excel IT Pro Discussions or Microsoft Answers Office forum Excel. If you want to do it with Powershell you should post your code here using the code posting tool provided on the edit bar and explain what's not working as expected.

    If you're already working with Powershell you could take a look at the great module from Doug Finke Import-Excel. It will make a lot of task much easier I think.

    Happy Holiday!


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Saturday, December 22, 2018 2:02 AM
  • Each day this is pushed into a master sheet. The date and these 4 totals are placed in a row, then the next day another row etc.

        $s1.cells.item(3,8) ="=H2/1440"
        $s1.cells.item(3,9) ="=I2/1440"
        $s1.cells.item(3,10) ="=J2/1440"
        $s1.cells.item(3,11) ="=K2/1440"

    The yellow is where the weeks tally (week 51) based on the numbers in blue to the right. I manually put these numbers in just to have some data. If there was only 2 days in the week so far, the average needs to still show on the left.

    

    Does this help?

    I have not been successful writing any code for this at this point. I am able to get the daily data in blue as desired, but not the issue in this post.

    Thank you!

    Paul

    Saturday, December 22, 2018 5:42 AM
  • Why are you using Excel with CSV files?  Just use the CSV to access the data.

    If you want to load a CSV into Excel then design the Excel sheet to do the calculations while the data is being loaded.  That is what Excel is for and what it does best.

    You can define and cell to be the results of a formula based on any other cells.

    You are making a mistake by only using Excel as a display tool when it can more easily do everything.  Just design the worksheet to create the results as each new day in inserted.

    You are also asking for a solution and not really asking a scripting question.  This forum is not a consulting or design service. 

    I recommend rethinking what you are trying to do and using Excel to do all calculations.  You can even use Excel to obtain the data with no need to use PowerShell. 


    \_(ツ)_/

    Saturday, December 22, 2018 5:53 AM
  • Thank you for you input, I am asking how to script into excel a formula that brings only the row values over that are within the same Weeknum then averaging them out in the corresponding week.

    I feel the reason that I have chosen powershell to do this, is because, the information that is pulled from another system has to be pulled and as new clients are added, the script looks for existing data and then updates automatically every night.

    When a new client is added powershell searches the excel file and if it does not exist, it will copy from a clean sheet over and place the data in that sheet for that client.

    I have all that working as designed, just unable to script in the formulas as needed for the above cells. not sure of what type of formula I can use with powershell that would be to accomplish this

    Saturday, December 22, 2018 3:39 PM
  • It can still be done in Excel.  Just create a template sheet and copy it.  The template has the formulas.

    If you want to programmatically add the formulas just add them as formulas to the cell.


    \_(ツ)_/

    Saturday, December 22, 2018 9:09 PM
  • The biggest issue I find with users is not knowing enough about how to design an Excel workbook.  Most just learn from what they find in the web but have limited experience with WB design.  This transfers over to PowerShell with Excel because the clues to how to use Excel in PS come from knowing how to use Excel.

    Here is one bit that gets lost.  To find and use all Excel functions from PS we would use the following component:

    $xl = New-Object -ComObject Excel.Application
    $xl.WorkSheetfunction

    There are over 400 methods that can be called from PS.

    $xl.WorkSheetfunction.Sum($range)

    It is also important to set the datatypes of the columns so they work as expected.

    The other big roadblock is designing a WB to look good without considering that it is truly a data object.  Normally I create an input sheet that is designed to process new data into the correct target cells or sheets.  This input sheet would have any VBA code needed to manage the data updates.  It can even detect new data ranges or "keys" and insert a new sheet.  The data sheets would also contain all formulas needed to do horizontal and vertical aggregation such as sums, averages, percent, etc.

    For programmatic help with Excel I recommend posting in the Excel Developers forum.


    \_(ツ)_/


    Saturday, December 22, 2018 9:41 PM