CODEWITHSUNDEEP
sqlsql-serverpowershellcmdsqlcmd
user1550159
user1550159

Reputation: 1267

How to export results to Excel using SQLCMD in Powershell

I am using SQLCMD command in PowerShell to run SQL scripts. When I pipe the output to TXT, the formatting is all messed up. I want to export the results to an Excel file. How can I do that?

My command 

Sqlcmd -Q 'select top 10 * from people'  -S serverName -d testDB -o c:\output.txt | format-table

Upvotes: 3

Views: 9066

Answers (2)

A. Lion
A. Lion

Reputation: 680

You can use a csv2xls tool,

I wrote one around 10 years ago in a company where I worked using vb.NET but surely now there are Open surce that you can to try from github or sourceforge.

Eventually if you have Ms-Office installed on the same computer with powershell you can use it to open the resulting csv and saving it as xls.

See examples at https://social.technet.microsoft.com/Forums/windowsserver/en-US/370ee470-f2cd-4f30-a167-b106dd51d47a/powershell-convert-csv-to-xlsx?forum=winserverpowershell

Upvotes: 0

Greg
Greg

Reputation: 4055

Use sqlcmd.exe's column separator switch -s. then you can use excel's 'Text to Columns', or import it knowing the column separator.

-s col_separator

Specifies the column-separator character. The default is a blank space. This option sets the sqlcmd scripting variable SQLCMDCOLSEP. To use characters that have special meaning to the operating system such as the ampersand (&), or semicolon (;), enclose the character in quotation marks ("). The column separator can be any 8-bit character.

Upvotes: 2

Related Questions