question

GundRameshGRV-8766 avatar image
0 Votes"
GundRameshGRV-8766 asked GundRameshGRV-8766 answered

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

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"

sql-server-generalsql-server-transact-sqlsql-server-migration
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Not clear to me what come after the header, but simple to show. Can you please upload to the forum a file with sample result you get and a file with the expected result which you want to get. You can upload text file directly to the question/answer in the forum

Remove this "shows (10 rows affected)" is clear and is done by using SET NOCOUNT.

1 Vote 1 ·


By the way, you can use the PowerShell command: Invoke-Sqlcmd instead of using the SQLCMD utility

This is much more flexible and allow to format the result in nice ways and store as file

https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

0 Votes 0 ·

After Text result each header column is underlined by hyphens , The line between Header row and 1st row set need to be remove.

SET NOSOUNT ON - helped to remove row count from text o/p

140770-test-output.png




Yellow marked line need to be remove from o/p

0 Votes 0 ·
test-output.png (46.7 KiB)
pituach avatar image pituach GundRameshGRV-8766 ·

OK... now it is clear, and the solution is simple :-)

I will add a solution as answer in the bottom

0 Votes 0 ·

Hi @GundRameshGRV-8766 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·

Please find the attached current output file :

if I use -h -1 ===> Then header row is disappears - As I expect that header record to be in o/p file.

Here in file after header display and before 1st row there are line with hyphen --------- ( need to suppress)

by using SET NOCOUNT ON below issue resolved
(10 rows affected) is suppressed140820-test-output.png140826-test-expected-output.png



File : Test-output.png is current o/p from sqlcmd
File : Test expected-output.png is expected o/p -

Thanks : @SeeyaXi-msft - supress last line.

by -h -1 : this removes header line


I need to remove line between header row and 1st rows displayed

0 Votes 0 ·

There is no way to leave the header and remove the ---- between header and data.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered GundRameshGRV-8766 commented

Hi @GundRameshGRV-8766,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Seeya,

Above solution is 100% worked for my expected o/p

Thanks
Ramesh Gund

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Phill
-h removes the header row from o/p
I need to keep header row and supress unwanter underlines came below header and before 1st record in text file

I have just uploaded screen stamps

Thanks
Ramesh Gund

0 Votes 0 ·

That is not possible using the command line options.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @GundRameshGRV-8766,


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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach edited

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This will produce the requested result

Maybe. Maybe not. Since there is no ORDER BY there is guarantee that the headers will come first. This can be dealt with further more acrobatics.

The solution with BCP in my article still requires hardcoding of headers, but there is no need to convert to strings in SQL Server. (There will of course always be conversion to strings eventually, since we are producing a text file.)

0 Votes 0 ·


Maybe. Maybe not. Since there is no ORDER BY there is guarantee that the headers will come first.


True but this simple to fix by simply adding ORDER BY and adding a column to sort by

Here is a solution for this which `"guarantee that the headers will come first.

 CREATE TABLE TBL (ID INT Identity(1,1), C1 INT, C2 NVARCHAR(10), C3 DATETIME2(7) )
 GO
 INSERT TBL (C1,C2,C3)
 values (1,'a',getdate())
 GO
    
 ;With MyCTE (id,C1,C2,C3) AS(
      SELECT '0','C1','C2','C3'
      UNION ALL
      SELECT 
         ID,
         CONVERT(NVARCHAR(10),C1),
         CONVERT(NVARCHAR(10),C2),
         CONVERT(NVARCHAR(10),C3) 
      FROM TBL
 ) 
 SELECT C1,C2,C3 FROM MyCTE ORDER BY id


Note! If there is no column in the table to sort by and only want to make sure that the headers are on the top then simple use "0" for the value in the first query (as above) and in the second query inside the CTE use constant value like "2"





0 Votes 0 ·
GundRameshGRV-8766 avatar image
0 Votes"
GundRameshGRV-8766 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.