Category Archives: PowerShell

PowerShell

PowerShell – Count number of commas in each row of a CSV

December 12, 2022 – 7:15 pm

So sometimes your data is so bad that you can’t trust a two column CSV to be turned into a dictionary, or more aptly, the file that is supposed to be two columns won’t open with in python to convert it to a dictionary because it doesn’t see two columns. So where is the issue? Probably a column count, which you can usually figure out if there are extra in excel but what if there are less? This PS snippet will read a given CSV, count the number of columns in the header row and report on any difference in the file, assuming your header row is correct this tells you which lines aren’t helping to validate a file.

$data = Get-Content location.csv

#Count in header
$header = $data[0].Split(",").Count
#Line counter
$i = 1

#Find lines with less or more commas
$data | ForEach-Object { 
    $c = $_.Split(",").Count
    if($c -ne $header) { "Line $i - $c commas" }
    $i++
}
Permalink | Comments Off on PowerShell – Count number of commas in each row of a CSV
PowerShell

Powershell – Deleting Empty CSV Files

December 7, 2020 – 4:04 pm

Sometimes in integration, you have jobs that run every 5 minutes or so, now these jobs run regardless if there’s data or not and they’ll produce an ’empty’ file. That’s to say there isn’t any usable data in it, just a header row. So you have one file every 5 minutes, that’s 12 an hour or 288 in a day. Some are going to have date, others not. While this can be done with a batch file (elsewhere on this site), Powershell offers a quicker way to do the same thing. Below is a Powershell script that looks through CSV’s and kills ones with only one line of data (header) leaving anything with real data in place.

$path="C:\onedrive_tt\Client\TCC_Touchpoints-Maintenance\scripts\20201207-CleanupTest\response\"
Get-ChildItem $path -Filter *.csv |
ForEach-Object {
$content = $path+"$_"
[int]$LinesInFile = 0
$reader = New-Object IO.StreamReader $content
 while($reader.ReadLine() -ne $null){ $LinesInFile++ }
$reader.Close()
#Write-Host($content+" - "+$LinesInFile)
if ($LinesInFile -eq 1)
{
#Write-Host("File: "+$content+" with "+$LinesInFile+" line will be deleted")
Remove-Item -Path $content -Force
}
}
Permalink | Comments Off on Powershell – Deleting Empty CSV Files
PowerShell

TCC & Powershell – CSV to Set Filters

October 26, 2020 – 4:41 pm

If you’ve been working with TCC for any length of time you’ll know the purple squirrel, rainbow unicorn and pot at the end of the rainbow is being able to set the filters based on a CSV. This ability would mark a boon for automating ad hoc integrations.

Now there are options, Python could pull it off but Python doesn’t live and play on a stock server I run across 95% of the time. Powershell does however, well it usually lives there, not sure how much play is involved.

In any case, it turns out we can do this whole process in Powershell without a whole bunch of headache. 

#Declare variables
$sourcePath = "..\..\scripts\mappingFiles\mappingContestNumberToJobCode.csv"
$targetPath = "..\..\scripts\mappingFiles\mappingFilterList.txt"
$targetContent = @()
$outputContent = New-Object System.Text.UTF8Encoding $false
#Loop through source file to build filter
Import-Csv $sourcePath | ForEach-Object {
    $targetContent += "<quer:string>$($_.Requisition)</quer:string>"
}
#Write out file as UTF8 no BOM
[System.IO.File]::WriteAllLines($targetPath, $targetContent, $outputContent)
#Update export specificaitons with the new filter set
((Get-Content -path ..\..\scripts\RequisitionExport\RequisitionExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionExport\RequisitionExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionMappingExport\RequisitionMappingExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionMappingExport\RequisitionMappingExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionCollaboratorExport\RequisitionCollaboratorExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionCollaboratorExport\RequisitionCollaboratorExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionOpenExport\RequisitionOpenExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionOpenExport\RequisitionOpenExport_sq.xml;
((Get-Content -path ..\..\scripts\SourcingRequestExport\SourcingRequestExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\SourcingRequestExport\SourcingRequestExport_sq.xml;
((Get-Content -path ..\..\scripts\CandidateMatchExport\CandidateMatchExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\CandidateMatchExport\CandidateMatchExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionQuestionExport\RequisitionQuestionExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionQuestionExport\RequisitionQuestionExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionOtherLocationExport\RequisitionOtherLocationExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionOtherLocationExport\RequisitionOtherLocationExport_sq.xml;
Permalink | Comments Off on TCC & Powershell – CSV to Set Filters
PowerShell

PowerShell – Replace in Files

April 24, 2020 – 12:23 pm

One of the hard parts about working on remote servers is they don’t usually have the proper toolkit to fix things as needed, meaning you have to make the changes in the development workstation, repackage and post, pull, copy, extract and paste in order to get anything done. Fine if it’s the initial load but when you only need to change something in a dozen files or so it can be tedious. So in the spirit of making due with what you have, you can use PowerShell to emulate the replace in file normally invoked from Notepad++ to work around it.
Here is the example:

foreach ($file in Get-ChildItem FileMask*.bat)
{
powershell -Command "(gc $file) -replace 'Text1', 'Text2' | Out-File -encoding UTF8 $file"
}
Permalink | Comments Off on PowerShell – Replace in Files
PowerShell

Powershell – Archive Log Files by Day

October 9, 2019 – 2:27 pm

Log files, you have to love them because they allow you to figure out what’s going on but what do you do with them, by default they just keep writing and writing and at some point you need to do something with them.
I found this powershell script that can do exactly that and put it here for safe keeping

##############
# This script requires WMF version >= 5.0 -  Download WMF 5.1 from https://www.microsoft.com/en-us/download/details.aspx?id=54616
# The directory that has the files desired to be imported MUST HAVE TRAILING "\"
$path = "C:\onedrive_tt\Client\AbbVie\TCC_Touchpoints\doc\20190910-ResponseFiles\results\"
# Optional filter to only touch files with specified extension 
$mask = "*.csv" 
$ZipFileName= "CompressedBackup.zip"
#Filter to only touch files older than specified hours
$hours = 24
# Begin SMTP Server information 
# $SMTPPassword = "PlainText SMTP Password"
# $SMTPUser = "SMTP Authentication user, will also be SMTP From: address"
# $SMTPServer = "my.smtpserver.com"
# $SMTPTo = 'receive@myemail.com'
# $SMTPSubjectFail = "ZIP Folder script failed to run"
# $secpasswd = ConvertTo-SecureString $SMTPPassword -AsPlainText -Force
# $mycreds = New-Object System.Management.Automation.PSCredential($SMTPUser, $secpasswd)
# End SMTP Configuration
$problem = $false
$date = (Get-Date).tostring("yyyyMMdd")
#Pre setting error message to no issue, will change later in the script
$errorMessage="no issue"


#############


try {
    # Get all items from specified path, recursing into directories but not returning directories themselves. Excluding files with modificiation dates less than $hours
    $files = Get-ChildItem $path -Recurse -Include $mask | where {($_.LastWriteTime -lt (Get-Date).AddHours(-$hours)) -and ($_.psIsContainer -eq $false)} 
    foreach ($file in $files) {
        $Directory = $path + $file.LastWriteTime.Date.ToString('yyyyMMdd')
        if (!(Test-Path $Directory)) {
	        New-Item $directory -type directory
	    }
	    Move-Item $file.fullname $Directory
    }
    $folders = Get-ChildItem $path -Directory  | % { $_.FullName }
    foreach ($folder in $folders) {
        $zipname = $folder + $ZipFileName
        if (!(test-path $zipname)){
        compress-archive -LiteralPath $folder -DestinationPath $zipname
        }
        if (test-path $zipname){
        remove-item -Recurse $folder 
        }
        else {
        Write-host $zipname + "Did not create, so I'm not deleting" + $folder
        }
    }
}
catch {
    $problem = $true
    $errormessage = $_.Exception.Message
}
finally {
    if ($problem){
        Write-Host $errorMessage
        # Send-MailMessage  -SmtpServer  $SMTPServer -UseSsl -From $SMTPUser -To $SMTPTo -Subject $SMTPSubjectFail  -Body $ErrorMessage -Credential $mycreds  
    }
    else {
        Write-Host "Script ran sucessfully"
    }
}
Permalink | Comments Off on Powershell – Archive Log Files by Day