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

See the -h option:
https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

Answer 3

Hi @Gund, Ramesh GRV ,

Use the -h -1 option can remove the ----line from the output.
As pituach mentioned, use SET NOCOUNT ON can remove the "rows affected".
The explanation is as follows.
-h headers
Specifies the number of rows to print between the column headings. The default is to print headings one time for each set of query results. This option sets the sqlcmd scripting variable SQLCMDHEADERS.
Use -1 to specify that headers not be printed. Any value that is not valid causes sqlcmd to generate an error message and then exit.

Here is a similar thread which you can refer to: https://stackoverflow.com/questions/2362229/how-to-suppress-hyphens-in-sqlcmd/6354313

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

If you want to have headers, but not have the underline, SQLCMD is not the tool for you. SQLCMD is primarily a text-based query tool. It is not targeted for file export.

You should probably look into using BCP instead. With BCP it is possible to add headers, but it's a little bit on the advanced side. Rather than taking up space here, I will refer you an article on my web site Using the Bulk-Load Tools in SQL Server. It includes an example of exporting with headers.

Answer 5

Hi,

input file is with simple command like "Select top 10 * from Test"

First of all to must understand that you should NOT use such queries! You should explicitly use the name of the columns in your query. In fact, this is related to the solution indirectly.

Yellow marked line need to be remove from o/p

This requirement is actually not so rare and there is a very simple solution, but not the one I will recommend :-)

Solution

All you need to do is to SELECT the name of the columns (hard coded) and UNINON ALL with your query, while CONVERT the values into VARCHAR. You use the -h parameter with value -1 to hide the headers and the unwanted line separator (the headers will come from your first query in the UNION) and use SET NOCOUNT ON to hide the number of rows effected.

The reason this is not recommended is the need to convert all values to string (VARCHAR for example). You need to remember that for performance this is not a good idea since we CONVERT an efficient data type (like bit or tiniint into to a data type that requires a lot more data length (need to pass more data in the network) like NVARCHAR and it is less efficient for processing.

For small amount of data this is not critical.

For example

If we have the query: SELECT C1,C2,C3 FROM TBL then we can execute instead the query

SELECT 'C1','C2','C3'
UNION ALL
SELECT 
   CONVERT(NVARCHAR(10),C1),
   CONVERT(NVARCHAR(10),C2),
   CONVERT(NVARCHAR(10),C3) 
FROM TBL

This will produce the requested result

---

Note! In most cases it is much better to use PowerShell using the comment Invoke-Sqlcmd, as I wrote in my first response

Note! if all you need is to get the result of the query in file and this does not have to be done using scripts, then you can use the SSMS and simply select to return the result in text file instead of a grid