?
Solved

Export SQL table with column name to CSV file using BCP

Posted on 2008-06-19
10
Medium Priority
?
12,034 Views
Last Modified: 2013-11-30
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
Comment
Question by:Anvie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 19

Accepted Solution

by:
frankytee earned 800 total points
ID: 21827825
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
 
LVL 19

Assisted Solution

by:frankytee
frankytee earned 800 total points
ID: 21827855
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
 
LVL 13

Assisted Solution

by:MikeWalsh
MikeWalsh earned 400 total points
ID: 21827857
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 800 total points
ID: 21827891
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
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 800 total points
ID: 21828815
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
 
LVL 19

Expert Comment

by:frankytee
ID: 21831324
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21831868
@nmcdermaid + @frankytee - didn't realize both of you are aussie as well... Maybe we all venture up north...
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21831997
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 21970497
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21971304
@nmcdermaid: my e-mail address is in my bio - drop me an e-mail...
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question