Converted SQL output to TEXT file - Need to Remove Extra Underline and result set displayed from Text file

Question

Hello,
I am using SQLCMD to generate .txt file from SQL Query

command used :
sqlcmd -E -S AUE-P-SQL02 -d Assembly -W -i C:\GRV\test.sql -o c:\GRV\TEST.TXT -s '|'

Restult of .Text File is : as below.

ETQ$NUMBER	SBD_MAT_PART_SKU_NUMBER_P	SUPPLIER_MATERIAL_NAME	SBD_MAT_COST_P	SBD_MAT_CURRENCY_TYPE_1_P	SBD_MAT_SAP_PLANT_NUMBER_P	SBD_MAT_SAP_SYSTEM_ID_P	SBD_SAP_MODIFIED_DATE_1_P
~CLV1~NAC			.00000000000000000000	USD	CLV1	NAC	20210406
-~CLV1~NAC	-	trolley	317.54700000000000000000	USD	CLV1	NAC	20210406
10010~CLV1~NAC	10010	20H208033	.00000000000000000000	USD	CLV1	NAC	20210813
10013~CLV1~NAC	10013	CHOOSE HANDLE	448.84862000000000000000	USD	CLV1	NAC	20210406
10014~CLV1~NAC	10014	CHOOSE HANDLE	645.51967000000000000000	USD	CLV1	NAC	20210406
10017~CLV1~NAC	10017	CHOOSE HANDLE	533.02659000000000000000	USD	CLV1	NAC	20210406
10018~CLV1~NAC	10018	CHOOSE HANDLE	662.26467000000000000000	USD	CLV1	NAC	20210406
10019~CLV1~NAC	10019	CHOOSE HANDLE	639.13467000000000000000	USD	CLV1	NAC	20210406
10020~CLV1~NAC	10020	CHOOSE HANDLE	675.89467000000000000000	USD	CLV1	NAC	20210406
10021~CLV1~NAC	10021	CHOOSE HANDLE	549.77159000000000000000	USD	CLV1	NAC	20210406

(10 rows affected)

I want to remove the ------ line came after column heading and after last line it shows (10 rows affected)
input file is with simple command like "Select top 10 * from Test"

Answer 1

Hi @Gund, Ramesh GRV ,

Agree with Tom. >There is no way to leave the header and remove the ---- between header and data.
But I found a stupid method that you can try, which is to print the column name plus the -h -1 option.
In your sql script you can add statements as follows.
A simple example:

USE AdventureWorksLT2017  
GO  
SET NOCOUNT ON  
print 'ProductID | Name | ProductNumber| Color | StandardCost | ListPrice | Size |  Weight | ProductCategoryID | ProductModelID | SellStartDate | SellEndDate | DiscontinuedDate | ThumbNailPhoto | ThumbnailPhotoFileName | rowguid | ModifiedDate'  
select top 10 * from SalesLT.Product  

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

Create Test.SQL file with following content and put in your working input folder: c:\TEST or UNC Path

SET NOCOUNT ON
print 'Field1 | Field2 | Field3 | Field4 | Fiend5'
select top 10 from Test

---

Note : In my example output fields are separated by pipe | symbol you can use , or other chare

---

Run Below command from Command prompt or you can create Test.bat file and run it

Command to run : c:\test>SQLCMD.exe -E -S MySqlServer -d MyDB -W -i c:\test\test.sql -h -1 -o c:\test\test.txt -s "|"