3

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
Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This seems like a work around rather than a solution
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.