How to create a csv with SQLCMD which can be imported into another database system

Question

I may be doing this all the wrong way so please point me in the right direction if I am missing something.

I have an Ubuntu server with SQLCMD installed and a Windows Server with a MSSQL database.

I have a working script to output data from a query out of the MSSQL database into a CSV file already BUT its not quite right. SQLCMD creates 2 excess features which I would like to remove and there is no real definitive guide on how to do it.

Sample output:

header1,header2
-----,-----
data1,data2
data3,data4

Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total       445  avg   445.0 (2.2 xacts per sec.)

So the features here I do not want in a CSV are quite obvious. The --- lines and the output data at the bottom.

My script looks something like this so far:

sqlcmd -S databaseserver -U "sa" -d database -p -W -Q "set nocount on; SELECT x as data, count(*) as datacount FROM location WHERE x IS NOT NULL GROUP BY x" -s "," -o output.csv -r0

I am happy to make .sh files with actions which take place after so any native ubuntu scripting method which can "finish the job" is acceptable. I am not that confident at writing the scripts but can usually work out what they do.

Answer 1

So the features here I do not want in a CSV are quite obvious. The --- lines and the output data at the bottom.

To remove the output at the bottom, omit the -p parameter.

sqlcmd -S databaseserver -U "sa" -d database -p -W -Q "set nocount on; SELECT x as data, count(*) as datacount FROM location WHERE x IS NOT NULL GROUP BY x" -s "," -o output.csv -r0

Result:

According to the documentation on sqlcmd

You can use the -h parameter to control the headers

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.

But you can only remove or change the frequency of the headers.

sqlcmd -S databaseserver -h -1 -U "sa" -d database -W -Q "set nocount on; SELECT x as data, count(*) as datacount FROM location WHERE x IS NOT NULL GROUP BY x" -s "," -o output.csv -r0

Result:

The not so clean solution that was also used here could be to print or select the column headers before running the select.

sqlcmd -S databaseserver -h -1 -U "sa" -d database -W -Q "set nocount on; PRINT('header1,header2'); SELECT x as data, count(*) as datacount FROM location WHERE x IS NOT NULL GROUP BY x" -s "," -o output.csv -r0

Result:

Answer 2

If you are open to using Powershell...

Assume the following table definition:

--Demo setup
DROP TABLE IF EXISTS BillingData;
go
CREATE TABLE BillingData
    (BillingId int, Feature varchar(13), Usage int, Measurement varchar(9), Cost int)
;

INSERT INTO BillingData
    (BillingId, Feature, Usage, Measurement, Cost)
VALUES
    (1, 'Walks', 25, 'walks', 200.32),
    (1, 'Baths', 5, 'baths', 251.32),
    (2, 'Litter change', 53, 'changes', 110.21),
    (2, 'Groom', 25, 'brushings', 123.00),
    (2, 'Scratching', 213, 'clipping', 123.00),
    (3, 'Pilling', 11, 'medicate', 10.00),
    (4, 'Groom', 5, 'brushings', 50.00),
    (4, 'Exercise', 1, 'run', 25.12),
    (1, 'Walks', 500, 'walks', 12351.31),
    (1, 'Baths', 53, 'baths', 1235.00),
    (2, 'Baths', 53, 'baths', 1235.00)
;

---

Using Powershell:

$OutFile = "c:\test\testout.txt"
Invoke-SqlCmd -Query "select * from test.dbo.billingdata" -ServerInstance . | 
ConvertTo-CSV -NoTypeInformation |
Select-Object | % {$_ -replace '"', ""} | Out-File $OutFile -Force -Encoding ascii

---

Results of output file:

BillingId,Feature,Usage,Measurement,Cost
1,Walks,25,walks,200
1,Baths,5,baths,251
2,Litter change,53,changes,110
2,Groom,25,brushings,123
2,Scratching,213,clipping,123
3,Pilling,11,medicate,10
4,Groom,5,brushings,50
4,Exercise,1,run,25
1,Walks,500,walks,12351
1,Baths,53,baths,1235
2,Baths,53,baths,1235