Export the query to excel sheet automatically


You can use powershell to export query results to a Excel file. You can download script from QueryResultsToExcel.ps1

running the powershell script:

View Excel file:

 

Advertisements

Developer @ SQL Server Team, Microsoft

Posted in Uncategorized
5 comments on “Export the query to excel sheet automatically
  1. wonderful post.Never knew this, regards for letting me know.

  2. dfwdraco76 says:

    When I launch sqlps from a command prompt, and then run my .ps1 script, it works perfectly.

    But when I try to run the script directly from command line (as I’d need to for a scheduled task) using slqps, I get this:

    U:\Scripts>sqlps script.ps1
    The term ‘script.ps1’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
    At line:1 char:29
    + script.ps1 <<<<
    + CategoryInfo : ObjectNotFound: (script.ps1:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    Thanks for your help!!!

  3. dfwdraco76 says:

    When I run sqlps from a command line, and then execute my powershell script, it works beautifully!

    But when I try to launch the script directly from the command prompt w/ ‘sqlps scriptname.ps1’ I get an error:

    U:\Scripts>sqlps script.ps1
    The term ‘script.ps1’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
    At line:1 char:29
    + script.ps1 <<<sqlps -Command “”
    .\script.ps1 : SQL Server PowerShell provider error: Path SQLSERVER:\script.ps1 does not exist. Please specify a valid path.
    At line:1 char:2
    + & <<<< {.\script.ps1}
    + CategoryInfo : NotSpecified: (:) [], GenericProviderException
    + FullyQualifiedErrorId : Microsoft.SqlServer.Management.PowerShell.GenericProviderException

    Can you help?

  4. dfwdraco76 says:

    Please disregard my question… I don’t know what is different this morning, but it is working now.

  5. Amon says:

    Hi, Figured this out and posting to help others who may also want to know this. Do the following to authenticate the database. Add the following to your script:

    $user = “sa”
    $password = “sa”

    Append the following to the following and enter as required.

    $results = Invoke-Sqlcmd -Query $query -ServerInstance $instanceName -Username $user -Password $password

    Hope this helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: