PowerShell Script to Count Strings in File

Here's a simple PowerShell script you can use to find the number of times a string appears in a text file. Simply open PowerShell ISE and change the directory to the one with the text file you want to search. Use the standard Windows cd command.

Then enter the script in this form:

@( Get-Content hellwegecomplaint.txt | Where-Object { $_.Contains("PageID") } ).Count

In this example we're searching the text file hellwegecomplaint.txt for references to the string 'PageID'. As you can see we get the correct result: 13.

Thanks to Phil Erb for this idea.

Powershell Command to Compare Files

All Windows users have a powerful task automation tool called Powershell which uses its own scripting language. Tonight's tip shows how to user Powershell to compare the differences and similarities between two different text files.

Go to Start . . . and search for Powershell, and then open Powershell ISE. Use the standard cd - change directory - command to change the prompt to the directory where you have two files you want to compare.

In this example you can see we have two text files listing American cities. There are a few cities which are included in both lists.

If we enter this script in Powershell, referencing the two text files, a new file named 'results.txt' will be generated.

Compare-Object -ReferenceObject (Get-Content cities1.txt) -DifferenceObject (Get-Content cities2.txt) -IncludeEqual >results.txt

The resulting file will have two columns. The second column has either an arrow or an equal sign to indicate that an individual string is in either the first or second referenced file only, or both.

Thanks to the Scripting Guys for posting this here:

https://blogs.technet.microsoft.com/heyscriptingguy/2013/11/18/powertip-compare-the-contents-of-files-with-powershell/

Powershell Script for Sorting Multiple Excel Files

A user with the handle Peter3 posted a Powershell script to stackoverflow.com which can be used to sort an Excel spreadsheet. Below I have edited the script in a very basic way so three different Excel files can be sorted by the script. I am just copying the portions from line reading, "function Release-Ref ($ref) { " to "$a = Release-Ref($objExcel) " . The file path to the Excel spreadsheets is listed at:

$objWorkbook = $objExcel.Workbooks.Open("C:\helloworld3\fielding.xlsx")

You specify the worksheet you want to sort on this line:

$objWorksheet = $objWorkbook.Worksheets.Item(1)

So this would sort the first worksheet,

$objWorksheet = $objWorkbook.Worksheets.Item(2)

. . . would sort the second worksheet, and so forth.

You specify the column you want to sort the data by on this line:

$objRange2 = $objworksheet.Range("A2")

. . . so the reference to A2 causes the data to be sort by column A starting with A2 as the first cell. The script will auto-Save the files, so you can just close them all without saving each one.

Just enter the script in the Script Pane in PowerShell ISE, and press play

function Release-Ref ($ref) { ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( [System.__ComObject]$ref) -gt 0) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() } $objExcel = new-object -comobject excel.application $objExcel.Visible = $True $objWorkbook = $objExcel.Workbooks.Open("C:\helloworld3\fielding.xlsx") $objWorksheet = $objWorkbook.Worksheets.Item(1)

$objRange = $objWorksheet.UsedRange $objRange2 = $objworksheet.Range("A2") [void] $objRange.Sort($objRange2) $objWorkbook.Save() $a = Release-Ref($objWorksheet) $a = Release-Ref($objWorkbook) $a = Release-Ref($objExcel) function Release-Ref ($ref) { ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( [System.__ComObject]$ref) -gt 0) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() } $objExcel = new-object -comobject excel.application $objExcel.Visible = $True $objWorkbook = $objExcel.Workbooks.Open("C:\helloworld3\batting.xlsx") $objWorksheet = $objWorkbook.Worksheets.Item(1)

$objRange = $objWorksheet.UsedRange $objRange2 = $objworksheet.Range("A2") [void] $objRange.Sort($objRange2) $objWorkbook.Save() $a = Release-Ref($objWorksheet) $a = Release-Ref($objWorkbook) $a = Release-Ref($objExcel) function Release-Ref ($ref) { ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( [System.__ComObject]$ref) -gt 0) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() } $objExcel = new-object -comobject excel.application $objExcel.Visible = $True $objWorkbook = $objExcel.Workbooks.Open("C:\helloworld3\pitching.xlsx") $objWorksheet = $objWorkbook.Worksheets.Item(1)

$objRange = $objWorksheet.UsedRange $objRange2 = $objworksheet.Range("A2") [void] $objRange.Sort($objRange2) $objWorkbook.Save() $a = Release-Ref($objWorksheet) $a = Release-Ref($objWorkbook) $a = Release-Ref($objExcel)