Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Posts
    46

    Question Unanswered: Is it possible to add column heading with BCP?

    Hello,

    Anybody know how to copy data with column name in csv file?

    The following script can copy data only in csv file.


    DECLARE @sql varchar(8000)
    SELECT @sql = 'bcp "select * from Mydb.dbo.tblInfo" queryout c:\CSV\test.csv -c -t, -T -S'
    EXEC master.sys.xp_cmdshell @sql
    Last edited by rajan142; 12-29-08 at 22:00.

  2. #2
    Join Date
    Oct 2008
    Posts
    46

    Question Is it possible to add column heading with BCP?

    Hello,

    Is there way to include column in CSV file when using BCP?

    DECLARE @sql varchar(8000)
    SELECT @sql = 'bcp "select * from Mydb.dbo.tblInfo" queryout c:\CSV\test.csv -c -t, -T -S'
    EXEC master.sys.xp_cmdshell @sql

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You need to wait longer than 1hr 18mins before reposting your question! Patience is a virtue

    The only way that I'm aware of is to run 2 BCP commands, the first simply containing column names
    Code:
    SET @sql = 'BCP "SELECT 'column1', 'column2', 'column3', 'columnN' FROM mydb.dbo.mytable" ...'
    EXEC master.sys.xp_cmdshell @sql
    
    SET @sql = 'BCP "SELECT [dont], [use], [select], [star] FROM mydb.dbo.mytable" ...'
    EXEC master.sys.xp_cmdshell @sql
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    can you union in the BCP ?

  5. #5
    Join Date
    Oct 2008
    Posts
    46

    Thumbs down Data export in CSV file useing BCP Utility

    I solved my problems. Run the following script in your sql server.

    USE testDb
    GO
    IF EXISTS (SELECT * FROM sysobjects WHERE sysstat & 0xf = 2
    AND id = OBJECT_ID('dbo.VW_Customers_RAW'))
    DROP VIEW dbo.VW_Customers_RAW
    GO
    CREATE VIEW dbo.VW_Customers_RAW
    AS

    SELECT 'CustomerID'AS CustomerID, 'FName' AS FName, 'LName' AS LName
    UNION ALL
    SELECT RTRIM(CustomerID)
    , FName, LName
    FROM tblCustomer
    GO
    DECLARE @Path varchar(255), @sql varchar(255)
    SET @Path='D:\CSV\Test.csv'

    SET @sql='BCP testdb.dbo.VW_Customers_RAW out '+@Path+' -T -w -t,'
    EXEC master.dbo.xp_cmdshell @sql
    GO

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well the reason I didn't suggest union all is for two reasons:
    1. without an order by clause you cannot guarantee the order of results, meaning your columns may get lost somewhere in the resultset other than the top
    2. data type; these have to match per column, meaning you'd have to convert all columns in your dataset to character to match your headings.

    The first issue can be resolved by using a subquery, but the second is just going to be a problem no matter what!
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2008
    Posts
    46
    How can i Order By the above script?

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Easiest way is to go with this...
    Code:
    SET @sql = 'BCP "SELECT 'column1', 'column2', 'column3', 'columnN''
    EXEC master.sys.xp_cmdshell @sql
    
    SET @sql = 'BCP "SELECT [dont], [use], [select], [star] FROM mydb.dbo.mytable" ...  ORDER BY [blah]'
    EXEC master.sys.xp_cmdshell @sql
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Other option
    Code:
    SELECT col1
         , col2
         , col3
    FROM   (
            SELECT 0 As [order_by_this_column]
                 , 'col1' As [col1]
                 , 'col2' As [col2]
                 , 'col3' As [col3]
            UNION ALL
            SELECT 1
                 , col1
                 , col2
                 , col3
            FROM   dbo.your_table
           ) As [a_subquery]
    ORDER
        BY [order_by_this_column] ASC
    George
    Home | Blog

  10. #10
    Join Date
    Oct 2008
    Posts
    46
    I do not get data order by FName. but i see the column header on the top.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ORDER
        BY [order_by_this_column] ASC
         , FName ASC
    George
    Home | Blog

  12. #12
    Join Date
    Oct 2008
    Posts
    46
    Thanks, the problem is solve.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •