[SOLVED] Sqlcmd to generate file without dashed line under header, without row count

Sqlcmd to generate file without dashed line under header, without row count

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.

Solution

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.

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"