Monthly Archives: December 2020

TCC

NetChange Result Files

December 28, 2020 – 12:03 pm

So net-change has given you a bunch of errors, oh well, that’s why we’re here. But it would be nice if net-change would give you the actual identifier instead of instead of a concatenated entity, operation and identifier with a pipe. Oh well, here’s how you pull it in SQL (access) so I don’t have to look up the three functions again.

SELECT SourceCandError.Identifier
,StrReverse(SourceCandError.Identifier) as Rev
,InStr(SourceCandError.Identifier, "|") as FirstPipeLoc
,InStr(StrReverse(SourceCandError.Identifier), "|") as LastPipeLoc
,Right(SourceCandError.Identifier, InStr(StrReverse(SourceCandError.Identifier), "|") -1) as Ident
,Right(SourceCandError.Identifier, InStr(SourceCandError.Identifier, "|")) as AfterPipe
,Mid(SourceCandError.Identifier, InStr(SourceCandError.Identifier, "|")+1, Len(SourceCandError.Identifier) - InStr(SourceCandError.Identifier, "|") - InStr(StrReverse(SourceCandError.Identifier), "|")) as Oper
,SourceCandError.Status
FROM SourceCandError;
Permalink | Comments Off on NetChange Result Files
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