Export to CSV with Column Header using cmd shell and bcp

Question

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

Instead of select * from TESTING.dbo.TEST use column names like select [Client Code],[Client Name], [Total balance] from TESTING.dbo.TEST — gofr1
– gofr1, Commented Nov 30, 2017 at 10:27
Possible duplicate of Custom column name for bcp queryout SQL Server — SMor
– SMor, Commented Nov 30, 2017 at 14:49

SqlKindaGuy · Accepted Answer · 2017-11-30 10:50:58Z

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'

answered Nov 30, 2017 at 10:50

SqlKindaGuy

3,6172 gold badges17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mittal Over a year ago

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

gofr1 Over a year ago

@Mittal do you actually have a table TESTING.dbo.TEST?

SqlKindaGuy Over a year ago

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

Collectives™ on Stack Overflow

Export to CSV with Column Header using cmd shell and bcp

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related