How to export results to Excel using SQLCMD in Powershell

Question

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

Answer 1

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.