Export SQL table with column name to CSV file using BCP

Hi,

I am using BCP to export SQL table to CSV and it works great. but it doesn't include the header - Column name. I need to include it ;-(

Below is my T-SQL.

Thanks!
Declare @sql varchar(8000)
select @sql = 'bcp "select * from Table" queryout C:\ -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

Open in new window

AnvieAsked:
Who is Participating?
 
frankyteeConnect With a Mentor Commented:
create a view which contains the field names (using union) and bcp the view, check out
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22659046.html
0
 
frankyteeConnect With a Mentor Commented:
ie,
create view myView
as
select t.c1,t.c2,t.c3
from
  (select 1 i,  'col1name' c1,  col2name' c2,  'col3name' c3...
  union
  select 2 i,cast(col1 as varchar) c1, cast(col2 as varchar) c2, cast(col3 as varchar) c3... from yourtable) t
order by t.i asc

then your bcp code:
Declare @sql varchar(8000)
select @sql = 'bcp "select * from myView" queryout C:\ -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
0
 
MikeWalshConnect With a Mentor Commented:
The quick answer is no. You can't do this with BCP. You can try and use DTS or the export table wizard (then schedule it/script it). This would be the easiest way to do it. You can try and do various techniques, creating a view to have your first row have the column names, use a copy and manually create your column list like on this link:http://groups.google.com/group/microsoft.public.sqlserver.tools/browse_thread/thread/f5e75f9f50c9342/b58fa1711f889031?hl=en&lnk=st&q=bcp+out+with+header+SQLSERVER#b58fa1711f889031

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nmcdermaidConnect With a Mentor Commented:
If the columns are static, you can create a CSV file which just has the columns in it.

Then after you export the CSV file you can just append the columns-only file to the data only file with this DOS command:

COPY ColumnsOnly.TXT + DataOnly.CSV FullFile.CSV
0
 
nmcdermaidConnect With a Mentor Commented:
Actually you could even BCP out the column heading names to a file beforehand from the system catalogs  and append to the file afterwards using the copy command.
0
 
frankyteeCommented:
hey nmcdermaid
had a look a your profile. i'm envious as i'm thinking of moving up north to sunny qld and one day get into datawarehousing as well (it pays a fair bit better than just plain old sql server stuff). hows the IT industry up there as i can't find much in brisbane when jobserve.com etc
cheers

frank
0
 
Mark WillsTopic AdvisorCommented:
@nmcdermaid + @frankytee - didn't realize both of you are aussie as well... Maybe we all venture up north...
1
 
frankyteeCommented:
mark_wills, another aussie! with these intererst rates, petrol and housing prices in sydney, if there enough jobs up there then qld is definitely the place to be.
0
 
nmcdermaidCommented:
Apologies to the moderators if this is not the venue for this but...

I work for a Business Intellegence Consultancy and we have a couple of vacancies. If you have ETL skills/Database Skills, and particularly BI skills (Cognos especially but MS may suffice) then why don't you go to www.bistech.com.au and have a look around. There's a contact email there somewhere.

We are a friendly approachable bunch. I'm going mountain bike riding on our quarterly team builder next Friday. Hopefully I won't break anything!!!

Good luck anyway.
0
 
Mark WillsTopic AdvisorCommented:
@nmcdermaid: my e-mail address is in my bio - drop me an e-mail...
0
All Courses

From novice to tech pro — start learning today.