Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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.

like image 716
Lill Lansey Avatar asked Jun 30 '14 19:06

Lill Lansey


People also ask

How do I turn off hyphens in SQLCMD?

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.

What is the purpose of using SQLCMD Q command?

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.

How do I export SQL query result to text file in SQL Server?

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.


1 Answers

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"
like image 174
Bogdan Sahlean Avatar answered Sep 23 '22 06:09

Bogdan Sahlean