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.
4,738 questions
No comments
{count} votes

Accepted answer
  1. MotoX80 24,711 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
    

3 additional answers

Sort by: Most helpful
  1. Rich Matheisen 36,241 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 . . .
    
  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 37,521 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--