23

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.

Lill Lansey
  • 4,575
  • 12
  • 52
  • 76

8 Answers8

53

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"
Bogdan Sahlean
  • 19,000
  • 3
  • 39
  • 56
  • 2
    Thanks for the "nocount" help. That worked. However, as I stated in my question, I need the column heading titles, but not the dashed line, and -h -1 also removes the column heading titles. Is there a way to get rid of the dashed line under the column headers : -------,--------, but keep the column headers? – Lill Lansey Jul 07 '14 at 12:43
  • 1
    I am awarding you the bounty although you answered only part of the question. Thank you for that. – Lill Lansey Jul 10 '14 at 13:41
  • 1
    @LillLansey you are probably going to need to select the column names using meta data (if dynamically) or manually select the column names in quotes and then union all the data underneath the columns. That can be your -Q query. Combining that method with this method should produce what you want. Or you could also create a process to open the file and add the headers and then close the file. But that's going to be a few more lines of code. – DtechNet Dec 17 '18 at 18:35
  • If you want to remove the dashed line but still keep the headers, you want to use JSkyS' answer below. – Weihui Guo Apr 16 '19 at 19:53
16

The guy with the top answer didn't answer how to remove the dashed line. This is my awesome solution.

  1. First include -h -1 which removes both the dashed line and header
  2. 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.

JSkyS
  • 323
  • 3
  • 13
6

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;]
Waleed A.K.
  • 1,470
  • 13
  • 13
2

To remove the Row Count: Add the below to your SQL statement

    SET NOCOUNT ON;

To remove the hyphen row try the following upon successful execution:

    findstr /v /c:"---" c:\dataExport.csv > c:\finalExport.csv

I use "---" as all my columns are over 3 characters and I never have that string in my data but you could also use "-,-" to reduce the risk further or any delimiter based on your data in place of the ",".

Dan
  • 41
  • 2
2

In my case worked well as :

type Temp.txt | findstr /v -- > DestFile.txt

Rinaldo Tavani Pinheiro
  • 21
  • 1
0

1.Create the file first with the header columns

2.Apprend the sqlcmd output to the file using the option -h-1

echo acctnum,rptmonth,facilname > c:\dataExport.csv sqlcmd -S . -d MyDb -E -s, -h-1 -W -Q "select account,rptmonth, thename from theTable" >> c:\dataExport.csv

Jim Evans
  • 1
0

In addition, if you want to query out all records in a table, you can code as

  1. SET NOCOUNT ON;
  2. SELECT SUBSTRING((SELECT ','+ COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=N'%table_name%' FOR XML PATH('') ), 2, 9999);
  3. SELECT * FROM %table_name%

Assign the above queries into a variable %query%. The the command will be looks like as below.

SQLCMD -h -1 -W -E -S %sql_server% -d %sql_dabase% -Q %query% -s"," -o output_file.csv
Z.T. Wai
  • 1
  • 1
  • 2
    How is that an improvement to the answer that was accepted 5(!) years ago? You add a select that goes unexplained. As well as parameters that the OP did not use. You should explain these. – Markus Deibel Sep 17 '19 at 05:15
0

I used another solution to solve the issue of removing the dashed line below the header.

DECLARE @combinedString VARCHAR(MAX);

SELECT @combinedString = COALESCE(@combinedString + '|', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME  = 'YOUR_TABLE_NAME'

Then just use Print @combinedString above your select statement. I used pipe delimiter.

Suraj Rao
  • 28,850
  • 10
  • 94
  • 99
thalearningmenace
  • 11
  • 3