Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12722
  • Last Modified:

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

0
Anvie
Asked:
Anvie
  • 4
  • 3
  • 2
  • +1
5 Solutions
 
frankyteeCommented:
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
 
frankyteeCommented:
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
 
MikeWalshCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
nmcdermaidCommented:
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
 
nmcdermaidCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now