PowerShell - Run SQL Query save file to network specify output file name

Bron Tamulis 41 Reputation points
2022-01-25T17:20:19.177+00:00

Simple task that I am hoping Power shell can be used.
Each day run a SQL query and save csv file to a network folder.

This works:
invoke-sqlcmd -ServerInstance DESKTOP-HG7ATKK\GP2018 -Database FARGO -query "Select * from IV00101" `
| Export-Csv -Delimiter "|" -Path "C:\SQL\export.csv" -NoTypeInformation

I need to modify so that the 'exports.csv' file created is formatted a specific format.

Format:
gppaymentsYYYYMMDDHHMMSS

Example:

gppayments20220125080000

How do I tweak so I can specify the file name?

Thank you in advance

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,364 questions
0 comments
Accepted answer
  1. MotoX80 31,571 Reputation points
    2022-01-25T19:49:28.317+00:00

    How's this? 

    $FileName = "C:\SQL\gppayments{0}.csv" -f (get-date -format "yyyyMMddHHmmss")
$FileName
Invoke-sqlcmd -ServerInstance DESKTOP-HG7ATKK\GP2018 -Database FARGO -query "Select * from IV00101" `
| Export-Csv -Delimiter "|" -Path $FileName -NoTypeInformation
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich Matheisen 44,776 Reputation points
    2022-01-25T19:39:49.323+00:00

    How about this? 

    $filename = "C:\SQL\gppayments{0}.csv" -f (get-date -format "yyyyMMddHHmmss")
Invoke-Command . . . 
} | Export-Csv $filename . . .
    0 comments
  2. Bron Tamulis 41 Reputation points
    2022-01-25T20:47:27.093+00:00

    Both suggestions worked beautifully. Thank you very much.

  3. Limitless Technology 39,351 Reputation points
    2022-01-26T09:43:10.033+00:00

    Hello BronTamulis

    You can use define the filename append format as "C:\SQL\gppayments{0}.csv" -f (get-date -format "yyyyMMddHHmmss")

    For instance:

    $FileName = "C:\SQL\gppayments{0}.csv" -f (get-date -format "yyyyMMddHHmmss")
    $FileName
    Invoke-sqlcmd -ServerInstance DESKTOP-HG7ATKK\GP2018 -Database FARGO -query "Select * from IV00101" `
    | Export-Csv -Delimiter "|" -Path $FileName -NoTypeInformation

    another way should be using Get-Date as next

    $SavePath = "C:\SQL"
    $SavePathName = Join-Path -Path $LogPath -ChildPath "gppayments$(Get-Date -Format 'MM-dd-yyyy')"

    --If the reply is helpful, please Upvote and Accept as answer--