Sqlcmd to generate file without dashed line under header, without row count
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"
The guy with the top answer didn't answer how to remove the dashed line. This is my awesome solution.
- First include -h -1 which removes both the dashed line and header
- Then before your select statement manually inject the header string that you need with a PRINT statement. So in your case PRINT 'acctnum,rptmonth,facilname' select..*...from...
Sorry I'm 4 years and 9 months late.
Use the following;
sqlcmd -S . -d MyDb -E -s, -h-1 -W -Q "set nocount on;select 'account','rptmonth', 'thename';select account,rptmonth, thename from theTable"
> c:\dataExport.csv
- remove the header -h-1
- remove row count [set nocount on;]
- add header select [select 'account','rptmonth', 'thename';]
- add your select [select account,rptmonth, thename from theTable;]