Using the following sqlcmd script:
sqlcmd -S . -d MyDb -E -s, -W -Q "select account,rptmonth, thename from theTable"
> c:\dataExport.csv
I get an csv output file containing
acctnum,rptmonth,facilname
-------,--------,---------
ALLE04,201406,Allendale Community for Senior Living-LTC APPL02,201406,Applewood Estates ARBO02,201406,Arbors Care Center
ARIS01,201406,AristaCare at Cherry Hill
. . .(139 rows affected)
Is there a way to get rid of the dashed line under the column headers : -------,--------, but keep the column headers?
and also a way to get rid of the two lines used for the row count on the bottom?
I tries using parm -h-1 but that got rid of the column headers as well as the dashed line.
use the -h -1 option to remove the dashes (--------) from the output and SET NOCOUNT ON to remove the "rows affected". This is great if you're creating a report or CSV file for another system to process. You don't need to use a union between your select statements for this.
The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through a variety of available modes: At the command prompt. In Query Editor in SQLCMD mode.
However, if you prefer to export SQL query results to a text file via a Wizard, we have your back. To begin with, right-click the database in SQL Server Management Studio or SSMS. Then, select the Import or Export data option and head to Export Data under Tasks. Next, open the SQL Server Import and Export wizard.
Solutions:
1) To remove the row count ("(139 rows affected)") you should use SET NOCOUNT ON
statement. See ref.
2) To remove column headers you should use -h
parameter with value -1
. See ref (section Formatting Options).
Examples:
C:\Users\sqlservr.exe>sqlcmd -S(local)\SQL2012 -d Test -E -h -1 -s, -W -Q "set nocount on; select * from dbo.Account" > d:\export.txt.
or
C:\Users\sqlservr.exe>sqlcmd -S(local)\SQL2012 -d Test -E -h -1 -s, -W -Q "set nocount on; select * from dbo.Account" -o "d:\export2.txt"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With