Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unanswered: Bcp and column delimiters

    Where can I get the complet info on Bcp parameters ?

    I want to export a table with "$" delimiting columns
    what would be the syntax of the bcp command ?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to use a format file...

    Do you know what one looks like?

    What version of SQL Server?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    Brett,

    If he were executing bcp through query analyzer could he not just use the -t field terminator?

    e.g.

    bcp database.dbo.tablename out -c -t'$' -r'\n' -Uusername -Ppassword
    -Sservername

  4. #4
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    Originally posted by rocket39
    Brett,

    If he were executing bcp through query analyzer could he not just use the -t field terminator?

    e.g.

    bcp database.dbo.tablename out -c -t'$' -r'\n' -Uusername -Ppassword
    -Sservername
    oops mispoke, bcp is not run through query analyzer...and documentation for all the of the flags for bcp can be found in BOL by searching on bcp.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Been working with mainframe flatfiles too long..

    And who says you can't do it out of QA?

    (Still need to tweak the paramters I'm afraid)

    Code:
    USE Northwind
    GO
    
    DECLARE @cmd varchar(8000), @username varchar(30), @password varchar(30), @File varchar(255), @Table sysname
    
    SELECT @username = 'sa', @password = 'xxx', @Table = 'Orders', @File = 'D:\Tax\Order.dat'
    
    SET @cmd = 'bcp '+ db_name() + '.dbo.' + @Table + ' out ' 
    	+ @File
    	+ ' -c -t'
    	+ '''' + '$' + '''' 
    	+ '-r'
    	+ '''' + '\nl' + ''''
    	+ '-U' + @username +' -P' + @password +' -S' + @@servername
    
    EXEC master..xp_cmdshell @cmd
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Thanks to both of you,
    that will work great I think !

    Hey, look I've got a OneStar promotion ! )

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Karolyn
    Hey, look I've got a OneStar promotion ! )
    Who da man....uhh...woman(?)


    You go (fill in gender)!

    Lots of great SQL stuff out here...

    check out..


    http://www.sqlteam.com/Default.asp
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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