counts totals in multiple .csv files

Lazy Win Admin has posted a PowerShell script here that you can use to add up totals given in column A for values in column B in multiple .csv values. The script will compile the counts given in column A even when there are duplicate entries in column B.

So in this example I have two .csv files which show the number of home runs each major league baseball player hit each season (Batting.csv) and how many home runs each player hit in each post season series (BattingPost.csv).

Enter the script below into PowerShell ISE (x86), and be sure to edit it so it includes the path to the directory with your .csv files and matches in the headings in the files.

The script will generate a new window adding up the numbers in the first column for each unique entry in column B.

Get-ChildItem -Path C:\FooFolder\powershell\*.csv | # Get each CSV files ForEach-Object -Process { Import-Csv -Path $PSItem.FullName # Import CSV data } | Group-Object -Property Name | # Group per Domain Name Select-Object -Unique -Property Name, @{ Label = "Sum"; Expression = { # Sum all the counts for each domain ($PSItem.group | Measure-Object -Property Count -sum).Sum } } | Sort-Object -Property Sum -Descending | Out-GridView -Title "Top Domains"

Counting the number of rows in multiple Excel files

Tonight's Tip of the Night is for Riley who contacted me a few days ago asking for a way to count the number of pages in multiple Excel files, in a way similar to the method demonstrated in the Tip of the Night for April 11, 2017 for counting the number of pages in multiple PDF files. That method used Visual Basic code. Tonight's tip involves a Powershell script, which was posted to Experts' Exchange by Subsun. The tip counts the number of rows, not pages. The number of pages in an Excel file can vary widely depending on how it is formatted.

Open PowerShell ISE (x86) and paste it in the script pane at the top. Enter the path to the folder containing your Excel files at the beginning, and also enter a path for the directory you want to contain the report that will be generated. Then simply press play on the toolbar to run the script. Each Excel file will be opened, and the number of rows will be listed in the blue console at the bottom, as well as being listed in the .csv file script generates.

Clear-Host $Report = "C:\FooFolder\excels\report.csv" $path = "C:\FooFolder\excels" [Array]$Results = $null $excelSheets = Get-Childitem -Path $path -Include *.xls,*.xlsx -Recurse $excel = New-Object -comobject Excel.Application $excel.visible = $false

foreach($excelSheet in $excelSheets) { $workbook = $excel.Workbooks.Open($excelSheet) $rowCount = $null For ($i = 1 ; $i -le $workbook.Sheets.count ; $i++) { $worksheet = $workbook.sheets.item($i) $rowMax = ($worksheet.usedRange.rows).count $rowCount += $rowMax } $Results += New-Object Psobject -Property @{ "File Name"=$excelSheet.Name "Sheet Count"=$workbook.Sheets.count "Row Count"=$rowCount} $excelSheet.Name $workbook.Sheets.count $rowCount } $excel.quit() $Results | select "File Name","Sheet Count","Row Count" | Export-Csv $Report -NoTypeInformation

Counting the Number of Slides in Multiple Presentations

Mike Halpin has posted some very useful PowerShell code to his site which can be used to calculate the number of slides in each PowerPoint file saved to a particular directory. See his posting here. When running the code be sure to exclude his synopsis listed in gray at the beginning. Only use the part posted below.

Open PowerShell ISE (x86) and put the code in the white script pane at the top. Edit the line beginning '$Path = ' to list the folder in which you have saved multiple PowerPoint files. Press F5 to run the script and a chart will be generated in the blue console below listing the name of each presentation and the number of slides in it.

The results look like this:

[CmdletBinding()] [Alias()] [OutputType([psobject])] Param( # The folder containing the files to count [Parameter(ValueFromPipelineByPropertyName=$true, Position=0)] $Path = 'C:\FooFolder\ppts' ) Add-Type -AssemblyName System.Windows.Forms Add-Type -AssemblyName Office Add-Type -AssemblyName Microsoft.Office.Interop.Powerpoint Write-Verbose "Getting files from $path" $files = Get-ChildItem -filter *.ppt* -Path $Path [psobject]$NumberOfSlides= @() Foreach ($file in $files){ $application = New-Object -ComObject powerpoint.application Write-Verbose "Opening $file" $presentation = $application.Presentations.open($file.fullname) $slideCount = New-Object System.Object $slideCount | Add-Member -type NoteProperty -name Name -value $file.name $slideCount | Add-Member -type NoteProperty -name Slides -value $presentation.Slides.Count #Introduce a slight wait so powerpnt.exe has time to process file Start-Sleep -Seconds 2 $presentation.Close() $NumberOfSlides += $slideCount } $NumberOfSlides Write-Verbose "Cleaning up processes" get-process powerpnt | Stop-Process