Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP Export to CSV w/Column Headers Expand / Collapse
Author
Message
Posted Thursday, December 16, 2010 1:13 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 8:31 AM
Points: 243, Visits: 601
Hi,

Is it possible to include column headers with BCP? Below is my BCP call except that the out file doesn't include headers. I have searched the web and can't find anything to point me in the right direction.

DECLARE @bcpCmd VARCHAR(8000)
SET @bcpCmd = 'bcp "select * from mydatabase.dbo.mytable" queryout f:/test.csv -c -t, -r -T -S myserver'
EXEC master..xp_cmdshell @bcpCmd

NOTE: The server I'm working with in SQL 2000 therefore I can't use SQLCMD. Also the solution have to be done programmably so using SSMS studio to export and save to CSV is not what I need.

Thanks


MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
Post #1036089
Posted Thursday, December 16, 2010 1:44 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 4:53 AM
Points: 2,667, Visits: 5,902
The only way I have managed to achieve this with BCP is:

Create a view
The view should contain
SELECT 'col1name' as col1name, 'col2name' as col2name etc
UNION ALL -- all is important to avoid a sort
SELECT -- all your columns from whatever table

Then use BCP export the view.

This does rely on the fact that the results of the union will have the first row first - which strictly speaking they may not be, but with it constructed as above I have never seen it incorrect.

Add a function round each column to quote qualify if the column contains a quote, comma, NL etc and you have a very fast and really easy way of exporting to csv.

Each column will need to be char or varchar - or cast to the correct type as well otherwise the union will complain about data types.

Hope that helps

Mike




Post #1036120
Posted Friday, December 17, 2010 12:46 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 8:31 AM
Points: 243, Visits: 601
Mike John (12/16/2010)
The only way I have managed to achieve this with BCP is:

Create a view
The view should contain
SELECT 'col1name' as col1name, 'col2name' as col2name etc
UNION ALL -- all is important to avoid a sort
SELECT -- all your columns from whatever table

Then use BCP export the view.

This does rely on the fact that the results of the union will have the first row first - which strictly speaking they may not be, but with it constructed as above I have never seen it incorrect.

Add a function round each column to quote qualify if the column contains a quote, comma, NL etc and you have a very fast and really easy way of exporting to csv.

Each column will need to be char or varchar - or cast to the correct type as well otherwise the union will complain about data types.

Hope that helps

Mike



Thanks Mike. Your input pointed me in the right direction. Basically what I did was create two files and merge them. I created the bcp query results file without headers first and created another file will headers only using INFORMATION.SCHEMA. Thanks for your input - appreciated.


MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
Post #1036709
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse