2

Database Name : TESTING
Table Name : TEST 

Client_Code Client_Name Amount      
123             ABC      10     
456             PQR      20     
789             XYZ      30     
147             IJK      40

Required Output in CSV with Fallowing Header

Client Code Client Name Total balance       
123          ABC          10        
456          PQR          20        
789          XYZ          30        
147          IJK          40

I am using fallowing Query, But Result I am getting without Header

exec master..xp_cmdshell 'bcp "select * from TESTING.dbo.TEST" queryout C:\queryoutput.csv -t"," -S"NEMP-HNI6101\SQLEXPRESS" -T -c -C RAW'

How to Export to csv using above command with header like Client Code, Client Name and Total balance?

Kindly suggest sql Query

gofr1
  • 15,741
  • 11
  • 42
  • 52
Mittal
  • 41
  • 1
  • 7
  • Instead of `select * from TESTING.dbo.TEST` use column names like `select [Client Code],[Client Name], [Total balance] from TESTING.dbo.TEST` – gofr1 Nov 30 '17 at 10:27
  • Column header still not coming in CSV – Mittal Nov 30 '17 at 10:46
  • 2
    Possible duplicate of [Custom column name for bcp queryout SQL Server](https://stackoverflow.com/questions/45287612/custom-column-name-for-bcp-queryout-sql-server) – SMor Nov 30 '17 at 14:49

1 Answers1

2

You should do a union with your headers just like this

 exec master..xp_cmdshell 'bcp "SELECT ''Client_Code'',''Client_Name'',''Total Balance'' union all select cast(Client_Code as nvarchar(50)),Client_Name, cast([Amount] as nvarchar(50)) from TESTING.dbo.TEST" queryout C:\queryoutput.csv -t"," -S"NEMP-HNI6101\SQLEXPRESS" -T -c -C RAW'
SqlKindaGuy
  • 3,501
  • 2
  • 12
  • 29
  • Getting fallowing erroroutput SQLState = S0002, NativeError = 208 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'TESTING.dbo.TEST'. SQLState = 37000, NativeError = 8180 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. NULL – Mittal Dec 01 '17 at 05:05
  • @Mittal do you actually have a table `TESTING.dbo.TEST`? – gofr1 Dec 01 '17 at 06:49
  • Maybe your connected to the right database. Switch database or make sure your query window says TESTING in database. Or make sure you actually have the table TEST and on schema DBO – SqlKindaGuy Dec 01 '17 at 08:23