a little PowerShell

It’s been a while since I’ve posted any PowerShell code. I had to write a quick script today to run some SQL, save the output to CSV, then ZIP the CSV file. And I had to loop through and the run SQL multiple times, one for each month from January 2021 until today.

That forced me to look up some stuff that I didn’t know how to do in PowerShell, off the top of my head. (In fact, most of it was stuff I didn’t know off the top of my head. I don’t use PowerShell enough to remember anything…) So here’s an edited version of the script, simplified somewhat. It might come in handy some time, if I ever need to do this again.

# CustInvAll-export.ps1

#Requires -Version 7
#Requires -Modules SqlServer

$dateFmt = 'yyyy-MM-dd'
$sqlServer = "MyServer"
$dbName = "myDB"

$curDate = [DateTime]::Today
$startDate = [DateTime]'01/01/2021'
while ($startDate -lt $curDate) {
    $endDate = $startDate.AddMonths(1)
    $startDateFmt = $startDate.ToString($dateFmt) 
    $endDateFmt = $endDate.ToString($dateFmt)
    
    $exportSQL = @"
    SELECT *
    FROM MyTable
    where [INVOICEDATE] >= '$startDateFmt' and [INVOICEDATE] < '$endDateFmt'
"@
    $exportFile = "CustInvAll-$startDateFmt.csv"
    $exportFileZip = "CustInvAll-$startDateFmt-csv.zip"

    echo "Exporting from $startDateFmt to $EndDateFmt to file $exportFile"

    # Invoke-Sqlcmd -ServerInstance $sqlServer -Database $dbName -Query $exportSQL `
    # | Export-CSV -Path $exportFile -NoTypeInformation  -UseQuotes AsNeeded

    # Compress-Archive -LiteralPath $exportFile -DestinationPath $exportFileZip

    $startDate = $endDate
} 

It can also be found in a Gist.