How to suppress hyphens in SQLCMD

Question

How can I suppress hyphens (------------) from the results set of this sqlcmd command:

C:\temp>sqlcmd -d AdventureWorks -s ";" 
 -Q "SET NOCOUNT ON SELECT top 5 FirstName, LastName FROM Person.Contact;"
FirstName                                         ;LastName
--------------------------------------------------;----------------------------
Gustavo                                           ;Achong
Catherine                                         ;Abel
Kim                                               ;Abercrombie
Humberto                                          ;Acevedo
Pilar                                             ;Ackerman

C:\temp>

Domination

Answer 1

I don't think there's any option available to achieve this - you'll have to live with those headers, I'm afraid.

Switchback

To be more precise, within the current options of the utility, you can have the headers (with hyphens) or remove them both completely; but you can't have header names without the hyphens. – Buddhi

-h-1 removes the header generated by SQLCMD sqlcmd -S server -d database -U username -P password -o "somefile.csv" -h-1 -Q "exec myStoredProcedure NULL, NULL" -W -w 2000 -s"," > sometextfile.csv – Dijon

Please check my post below. Anything can be done without any other tool! – Homans

@ShaiAlon "Anything can be done" with the sqlcmd tool? – Buddhi

Yes, as long as you know how to run anything with a single-line query – Homans

@Buddhi Please check my answer below. I hope it answers the question now... – Homans

@ShaiAlon you can run SQL files using sqlcmd with -i – Pyxis

I don't know why this is downvoted so much. You have to use shell (pipe) or Powershell, sqlcmd.exe by itself doesn't have an option to write standard CSV files. Which I think speaks volumes to MSSQL (lack of) usability in modern data science and devops scenarios. – Erving

Answer 2

Use the -h -1 option to remove the column headers and the dashes (--------) from the output and SET NOCOUNT ON to remove the "rows affected" at the end. This is great if you're creating a report or CSV file for another system to process.

Example:

SQLCMD -S 127.0.0.1\SQL_SERVER_Instance -d db_name -U db_login -P password -i your_script.sql -o your_output.csv -h -1

In your SQL script:

SET NOCOUNT ON -- removes (rows affected from the output)
select 'your_column_1, your_column_2'
select * from your_table

You don't need to use a union between your select statements for this.

Kelikeligot

Just a heads up, there can't be a space between the -h and -1. Only use: -h-1 – Observe

@Observe not sure that's true, I'm using a script with -h -1 in it with no issues, its my understanding that they are just like any other command line switch like -d or -U, pretty sure you need the space – Kelikeligot

while this does remove the dashes "---" it also removes the column names for me as well – Goodson

@Goodson note in the above script you need to have an aditional select to actually do the column names select 'your_column_1, your_column_2' select * from your_table – Kelikeligot

yep sorry - oversaw that completely. is there any way to keep colnames but just avoid the pesky dashes? – Goodson

doesn't work for me. I do need to select my column titles 'As' and I'm not selecting everything with * so I'm not sure if that is causing any problems. This problem is frusterating. – Kindness

When I did this it removed the column names... – Addict

@JustinHaubrich see the sql script in my answer you have to select the column names manually as a row. – Kelikeligot

This is extremely cumbersome (writing a second select statement to select all the column headers) when you have more than a couple of columns to select. – Assyria

Answer 3

The only thing I can think of is removing the header using the -h -1 switch and adding the column names as the first row to the SQL Query:

SELECT 'FirstName' as FirstName, 'LastName' as LastName
UNION
SELECT top 5 FirstName, LastName FROM Person.Contact

Note that if there are other data types then (var)char, you need to convert the field using : CAST(MyColumnName AS varchar(32)) as MyColumnName

Guillaume

Please check my anwer below. it doesn't contain -h -1 and answers the question. – Homans

This is extremely cumbersome (writing a second select statement to select all the column headers) when you have more than a couple of columns to select. – Assyria

Answer 4

How can I supress hyphens (------------) from the results set of this sqlcmd command:

You can do it all in a simple way in one command, without any script or file manipulation!

sqlcmd -d AdventureWorks -s ";" -Q "SET NOCOUNT ON; SELECT top 5 FirstName, LastName FROM Person.Contact" |findstr /v /c:"---"

Add set nocount on; to remove the (X rows affected) line. Add |findstr /v /c:"---" to remove the underlines. This way you get a clean answer, with only:

FirstName                                         ;LastName
Gustavo                                           ;Achong
Catherine                                         ;Abel
Kim                                               ;Abercrombie
Humberto                                          ;Acevedo
Pilar                                             ;Ackerman

Homans

Yes, this now addresses the question asked. I've removed my downvote. – Buddhi

And, if the first column ever returned a field starting with '---', you'd be throwing that away too. – Casie

@ShaiAlon Hi Sir, do you have a linux version of this code? – Rakish

Hi @Dekso, Do you mean like this? sqlcmd -d AdventureWorks -s ";" -Q "SET NOCOUNT ON; SELECT top 5 FirstName, LastName FROM Person.Contact" |grep -v '---' – Homans

This does not seem to make a difference for me. I tried both with just three dashes as you've used and with the exact number of dashes that are normally outputted in my CSV file, and neither amount in the sqlcmd works. I also tried with no space between the pipe and findstr and adding a space, to no avail. Did this feature break, get dropped, or does it require some additional utility be installed? – Assyria

I should note; I am outputting the results to a CSV file, which your command does not do. Do I need to run the findstr command before outputting to a file via the -o flag? – Assyria

Answer 5

Using only sqlcmd and the Windows command line, with no stored procedures:

REM Get the column headers ("set nocount on" is necessary to suppress the rows affected message)
sqlcmd -S MyServer -Q "set nocount on;select top 0 * from MyDatabase.MySchema.MyTable" -o "MyTableColumns.csv" -s "," -W

REM Remove hyphen line
findstr /R /C:"^[^-]*$" MyTableColumns.csv > MyTableHeader.csv

REM Get data without headers
sqlcmd -S MyServer -Q "set nocount on;select * from MyDatabase.MySchema.MyTable" -o "MyTableData.csv" -h -1 -s "," -W
REM You can also use bcp for this step
REM bcp "select * from MyDatabase.MySchema.MyTable" queryout  "MyTableData.csv"  -c -t"," -r"\n" -S MyServer -T

REM Append header and data
type MyTableHeader.csv MyTableData.csv > MyTableDataWithHeader.csv

To handle data with delimiters inside (for example "Atlanta, GA") you'll need to specify the fields separately (rather than use "select *") and use the QUOTENAME function in SQL Server.

Knisley

Answer 6

Or maybe post-process the output through sed as:

sqlcmd ... | sed -e '2d'

to delete the second line?

You can get a Win32 sed from http://gnuwin32.sourceforge.net/packages/sed.htm

Casie

Answer 7

To get rid of the hyphens, I add some code to my stored procedures that will output the column header only. Extracting data using SQLCMD is done in 2 parts.

First I call my stored procedure with a special set of parameters. In my case, when I call the SP with all NULLs, it mean I wish to have the column header.

Then I call SQLCMD a second time and append the output to the file that I just created before and voila!

Create a blank CSV file to hold the column header

sqlcmd -S server -d database -U username -P password -o "somefile.csv" -h-1 
  -Q "exec myStoredProcedure NULL, NULL" -W -w 2000 -s"," > sometextfile.csv

Now append the output result from the stored procedure

sqlcmd -S server -d database -U username -P password -o "somefile.csv" -h-1 
  -Q "exec myStoredProcedure 2011, 10" -W -w 2000 -s"," >> sometextfile.csv

Notice the first command uses > and the second one >>. When using one -> "Create or overwrite" and two -> "Append or create".

• -h-1 removes the header generated by SQLCMD
• -W removes the trailing spaces
• -w set the max row width
• -s defines columns separator

Vesicant

Answer 8

You can do the following:

exec 'sqlcmd -S YourServer -E -Q "set nocount on; select * from YourTable" -s "," -W | findstr /R /C:"^[^-]*$" > yourfile.csv'

This will create a csv file with the headers, without the dashes, and trimmed white space, in one fell swoop. Based on MichaelM's answer above.

Laevorotation

Answer 9

I had to do a ton of research because none of the answers here exactly fit what I was looking for. So I am going to sum in hopes that it will help others.

For me, the easiest way was to use sed. While this was already suggested here, there was incorrect syntax to run sqlcmd and sed concurrently. If you're using Windows, as was the case for me, head to http://gnuwin32.sourceforge.net/packages/sed.htm to download sed

My final command looked something like:

SQLCMD -m 1 -S "userpc\SQLEXPRESS" -E -i "C:\someDirectory\sqlfile.sql" -W -s "," -o "C:\someDirectory\output.csv" && sed -i 2d "C:\someDirectory\output.csv"

Where

--m 1 eliminates the "Changed database context to..." line
-&& runs the second command if the first command runs successfully
-sed -i 2d deletes the second line containing the hyphens and overwrites the output of sed to the original output

Be aware that the sed output file name and the output from sqlcmd must match otherwise the output from sqlcmd will not be correctly overwritten. There should not be quotes around 2d in the sed statement as answered above and is present in their documentation or you will get an error.

Hope this helps!

Alialia

Please note that this could be a difficult solution in a corporate environment where downloading and installing third party open source software built for *nix Platforms could incur a good deal of IT HumBuging..especially if this process is being designed to run on a Windows Server for an official business process. – Information

Answer 10

if you can use PowerShell instead

• Invoke-DbaQuery from https://github.com/sqlcollaborative/dbatools
• Invoke-SqlCmd -Query from official MSSQL PowerShell module

working example:

Invoke-DbaQuery "SELECT * FROM [MyApp].[dbo].[ClientConfig] WHERE ClientID='Default'" -SqlInstance mysql.mydomain.com -ReadOnly `
  | Export-Csv -Path clientconfig-default.csv -Encoding utf8 -UseQuotes AsNeeded

Note -ReadOnly is only helpful on clusters, and probably noise (early optimization). But I'm a developer querying/diffing production databases, so overly cautious so minimize load which might be noticed by DBAs.

I sometimes use https://docs.dbatools.io/#Copy-DbaDbTableData to copy whole tables mssql-to-mssql. It is fast, I think uses BCP.EXE or BulkInsert.

Erving

it writes lots of trailing zeros for decimal/float, but so does sqlcmd – Erving

Answer 11

If you could output to a file first (using the -o option), another post-process option using powershell would work by reading the file's contents--while skipping the second line--and writing it back to itself.

(Get-Content "file.txt" | Where {$_.ReadCount -ne 2}) | Set-Content "file.txt"

Buddhi

Why do you need to use another external tool as PS, when you have option -h -1 in the sqlcmd? – Homans

@ShaiAlon The -h -1 option removes the header completely. The question was how to remove just the hyphens that appear following the row of header names. – Buddhi

Please check my answer again, I've fixed it. – Homans

If you have powershell available consider: Invoke-DbaQuery from github.com/sqlcollaborative/dbatools or Invoke-SqlCmd -Query from official MSSQL PowerShell module. My working sample: Invoke-DbaQuery "SELECT * FROM [MyApp].[dbo].[ClientConfig] WHERE ClientID='Default'" -ReadOnly -SqlInstance mysql.mydomain.com | Export-Csv -Path clientconfig-default.csv -Encoding utf8 -UseQuotes AsNeeded – Erving

Answer 12

MichaelM has a decent solution, but as slingshot pointed out ... findstr could be over aggressive and remove data lines that contain hyphens. Another approach would be calling sqlcommand to get the data set and then using set /p to grab the first row from the output file and assign it to a variable. Then delete the orignal output file. Next, echo out the headers to a new file. Lastly, pipe another headerless sqlcmd to that file.

sqlcmd -S server -d database -E -Q "exec myStoredProcedure" -W -o my_headers.csv -m-1
set /p headers=< my_headers.csv
del my_headers.csv
echo %headers% > my_data.csv
sqlcmd -S server -d database -E -Q "exec myStoredProcedure" -W -m-1 -h-1 >> my_data.csv

Also Top 0 is nice choice to create a "headers only file" but only if you are using a select statement. If you're calling a stored procedure, look into using FMTONLY ON to grab the headers only from the SP.

sqlcmd -S server -d database -E -Q "Set NOCOUNT ON; Set FMTONLY ON; exec myStoredProcedure" -W -o my_headers.csv -m-1

A caveat being that the FMTONLY ON can't be used against SP's using #temp tables. This is because FMTONLY ON doesn't execute the SP. It only grabs metadata. But if the column names are coming from tables that don't exist pre-execution then you can't get those column names.

• Troy

Effectuate

Answer 13

Public Sub HyphenDelete(strFilename1 As String, Hyphens As Integer)


Const FOR_READING = 1
Const FOR_WRITING = 2

strFileName = strFilename1

strCheckForString = Left("-------", Hyphens)
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextFile(strFileName, FOR_READING)
strContents = objTS.ReadAll
objTS.Close

arrLines = Split(strContents, vbNewLine)
Set objTS = objFS.OpenTextFile(strFileName, FOR_WRITING)

For Each strLine In arrLines
   If Not (Left(UCase(LTrim(strLine)), Len(strCheckForString)) = strCheckForString) Then
      objTS.WriteLine strLine
   End If
Next

End Sub

Braasch

Answer 14

If outputting to a file try the following upon successful execution:

    findstr /v /c:"---" sqloutput.dat > finaloutput.dat

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

Lynellelynett

You can do it right after the query without redirecting to a file – Homans

@ShaiAlon piping is more efficient, but it requires "SET NOCOUNT", and also adds small margin of error: if only 2 columns both 1 or 2 characters then this will fail, or if using "-;-" and change delimiter. This seems like the safest method as it preserves sqlcmd output. – Erving

Answer 15

remove dashes from results:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'testMail',
@recipients = '[email protected]',
@query = '  
SET NOCOUNT ON
SELECT W.* FROM (
(SELECT CAST("id" AS VARCHAR(5) ) as id , CAST("name" AS VARCHAR(50) )as name ) 
UNION ALL 

SELECT CAST( id AS VARCHAR(5) ) AS id, CAST (name AS VARCHAR(50) ) AS name from mydb.dbo.bank
 )W
' ,
@subject = 'Test email',
@attach_query_result_as_file = 1,
@query_attachment_filename='filename.xls',
@query_result_separator='   ',   
@query_result_header = 0

    -----remember @query..separator should be--Tab space----

Indemnity

Answer 16

I typically process the resulting .csv file as follows:

sed -i '2d' "$file"  # remove 2nd line of dashes
sed -i '$d' "$file"  # remove last line (row count string)
sed -i '$d' "$file"  # remove one more empty line at the end of the file

Where:

• -i edits the file in-place...
• 2d deletes line 2, in this case, the lines of dashes...
• $d deletes single line at the end of the file...
• "$file" is the bash variable holding the path string to the .csv file.

Pahlavi

Answer 17

This is what I ended up with and works well for me:

sqlcmd -S 12.34.56.789 -i "C:\Qry.sql" -s"," -W -w1000 | findstr /V /R /C:"^[-,]*$" > Reprt.csv

I believe at this point, the findstr & regex are all that needs to be explained:

findstr /V /R /C:"^[-,]*$"

The regex states "At the beginning ^, has either - or , [-,] any number of times *, until you reach the end of the line $.

/V means, show me anything that doesn't match.

The > following it redirects the display to a file.

I did also add this to my SQL file:

SET NOCOUNT ON;

Lampblack

Answer 18

The following line:

findstr /R /C:"^[^-]*$" MyTableColumns.csv > MyTableHeader.csv

is very dangerous as it removes all lines containing a "-". You better have a look at findstr /? and use something like:

findstr /B /V /C:"-----" MyTableColumns.csv > MyTableHeader.csv

Hagan

Answer 19

if you want to spool the data out then you can set the following switch:

SET UNDERLINE OFF;

Stour

Completely false; this is not even a valid command in SQL, much less any other language in the scope of this question. – Corrigan