Solved

Export SQL table with column name to CSV file using BCP

Posted on 2008-06-19
10
11,646 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 19

Accepted Solution

by:
frankytee earned 200 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 200 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 100 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
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 200 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 200 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Read about achieving the basic levels of HRIS security in the workplace.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now