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

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 4, 2015 at 14:35

Greg

4,1155 gold badges35 silver badges70 bronze badges

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

1 Comment

AleX_ Over a year ago

This seems like a work around rather than a solution

A. Lion · Accepted Answer · 2018-03-22 09:15:08Z

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

Collectives™ on Stack Overflow

How to export results to Excel using SQLCMD in Powershell

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related