[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Export SQL table with column name to CSV file using BCP

Posted on 2008-06-19
10
Medium Priority
?
12,217 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 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