CODEWITHSUNDEEP
sql-server-2005sqlcmd
Lill Lansey
Lill Lansey

Reputation: 4915

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.

Upvotes: 31

Views: 81680

Answers (11)

nenea
nenea

Reputation: 147

This is the one line solution, without doing anything inside the stored procedure to append the column headers:

sqlcmd -S . -d MyDb -E -s, -W -Q "select account,rptmonth, thename from theTable" | findstr /v /c:"-" /b > "c:\dataExport.csv" & exit 0

What this does is it intercepts all console output and replaces the "-" char BEFORE it redirects to the output file. There is NO need to output to intermediary file. And you will need a one-liner command if you use an agent to run these commands remotely on the sql server machines, which most of the times are locked from hosting *.bat files (which you'd need for multiline commands). I added the "exit 0" at the end to not fail the caller application overall. You may remove it starting "& exit 0" if you don't care about that.

This one liner is why I chose sqlcmd over bcp out, by the way. BCP, although optimized for speed, cannot output column headers unless doing the ugly trick within the stored proc, to append them there as a union all.

Just in case you have access to writing a bat file that contains this one liner, you MUST add @ECHO OFF before it. Otherwise the console output will also have the actual command.

Upvotes: 4

JSkyS
JSkyS

Reputation: 443

The guy with the top answer didn't answer how to remove the dashed line. This is my 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...

Upvotes: 26

Spyros El.
Spyros El.

Reputation: 423

Kind of late to the party but if you're on Linux you can use sed to remove the second line after the file is produced like this:

sed -i '2d' exported.csv

Upvotes: 0

Diego Schmidt
Diego Schmidt

Reputation: 21

With SQL Server 2017 (14.x) and later you can print header with:

SELECT string_agg(COLUMN_NAME, ', ') within group (order by ORDINAL_POSITION asc) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YOUR_TABLE_NAME'

Upvotes: 2

thalearningmenace
thalearningmenace

Reputation: 21

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.

Upvotes: 0

Z.T. Wai
Z.T. Wai

Reputation: 11

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

Upvotes: 1

Rinaldo Tavani Pinheiro
Rinaldo Tavani Pinheiro

Reputation: 21

In my case worked well as :

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

Upvotes: 2

Waleed A.K.
Waleed A.K.

Reputation: 1656

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;]

Upvotes: 9

Jim Evans
Jim Evans

Reputation: 1

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

Upvotes: 0

Dan
Dan

Reputation: 41

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 ",".

Upvotes: 2

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

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"

Upvotes: 64

Related Questions