exporting data from SQL query

Question

I have a SQL CmdExec Job set up to run a query however the output data contains headers, job information and total numbers of returned rows as part of the output file and I am trying to figure out how to exclude this, this project calls for only the raw data to be returned.

Based on other suggestions I've tried adding "sqlcmd -Q set nocount on; " before my query as well as "-S localhost -D master -o" at the end of the query but the extra data remains in the results.

Any suggestions are greatly appreciated.

sqlcmd -Q set nocount on;   
SELECT
cast(coalesce(ARMASTER.CUSTNO, ' ') as char(20)) as [CUSTNO],
cast(coalesce(ARMASTER.INVOICE, ' ') as char(20)) as [INVNO],
cast(coalesce(ITEM.PARTNO, ' ') as char(16)) as [ITEM],
cast(coalesce(ITEM.DESCRIPT, ' ') as char(65)) as [DESCRIP],
cast(coalesce(ITEM.DISRATE, ' ') as char(7)) as [DISC],
cast(coalesce(ITEM.PRICE, ' ') as char(20)) as [PRICE],
cast(coalesce(ITEM.SHIPQTY, ' ') as char(12)) as [QTYSHP],
cast(coalesce(ITEM.TOTPRICE, ' ') as char(20)) as [EXTPRICE],
cast(coalesce(ITEM.CUSERLINE, ' ') as char(10)) as [SEQNUM]

FROM ITEM
JOIN 
armaster ON ITEM.invoice = ARMASTER.invoice

ORDER BY ARMASTER.INVOICE
-S localhost -D master -o

Answer 1

You might want to use Powershell for this.

Invoke-Sqlcmd -ServerInstance "" -Database "" -Query "" | ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File ("C:\test.csv") -Force -Encoding ascii

See this question for more information.

You can use Windows Scheduler, or SQL Agent to schedule this script. For instructions, please follow the link which you would like to implement.

SQL Agent: https://www.sqlservercentral.com/articles/scheduling-a-powershell-script-using-sql-server-agent

Windows Agent: https://www.sharepointdiary.com/2013/03/create-scheduled-task-for-powershell-script.html

Answer 2

Did you try bcp? It is less flexible than sqlcmd but a standard tool to export/import data.

There are tons of export2csv tools which exist on the web. Download any and just set delimiter to space.

It is easy to write such exporter yourself on any language.

And of course, I can suggest my own tool: https://github.com/White-Owl/nisql