Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    31

    Unanswered: bcp'ed columns be wrapped in double quotes?

    Below is a row I bcp'ed from a Sybase 12.5.2 server, the columns are comma delimited:

    12345,JOHN,DOE,,,,,,Hello Word, Hello Moon, Hello Sun,abcd,bacu,,,,,,,,

    (note that some of the columns are actually empty)



    The problem is that data in some of the columns are actually contain commas, for example this column:

    Hello Word, Hello Moon, Hello Sun


    I need the delimiter to be comma, my question is, is there a way for me to force the data for each column to be wrapped in double-quotes (without using Unix shell commands) ? So now I will now have something like this:


    "12345","JOHN","DOE","","","","","","Hello Word, Hello Moon, Hello Sun","abcd","bacu","","","","","","","",""

  2. #2
    Join Date
    Aug 2004
    Posts
    38
    Try:

    -t\",\"

    where \ escapes special characters in the korn shell. Might be different depending on your shell, but this one works for me.

  3. #3
    Join Date
    Oct 2004
    Posts
    31
    I will need to try this instead :

    Code:
    -t"\" \""

    Essentially what is happening is that " " (i.e double-quote followed by space followed by another double-quote...it will appear thoug that a single white spcae is the delimiter) is being used as the delimiter (instead of a comma being the delimiter), so then you get something like this:


    And I will get something like this:

    Code:
    12345" "JOHN" "DOE" "" "" "......." "Hello Word, Hello Moon, Hello Sun" "abcd" "bacu......etc
    Note that there is no double-quotes at the start and there won't be one at the end either.
    Last edited by bluwulf; 10-04-05 at 22:14.

  4. #4
    Join Date
    Oct 2004
    Posts
    31
    I came across these links so hopefully I will be able to figure something out:



    I came across this link that seems to have some ideas to on doing something like this

    BCP export to CSV with double qoutes around data

    BCP format
    file


    Select INTO TextFile ..... How to?

  5. #5
    Join Date
    Sep 2005
    Location
    France, Paris
    Posts
    34
    Hello,

    Why not to try another field delimiter ? like -t"|"
    Regards.

  6. #6
    Join Date
    Oct 2004
    Posts
    31
    No...I need the column data to be wrapped in double-quotes. It is also (although unlikely) possible for pipes to be present in the column data.

  7. #7
    Join Date
    Aug 2010
    Posts
    1

    Solved

    First of all Thanks for pointing me off in the right direction, I was trying to export an MSSQL database to CSV for importing to MySQL and had never heard of BCP!

    I have just needed to do this and Im pleased to post the command line i used.

    This is not a 100% fix, You still need to add 2 quotes.
    1 before the first field of the first row, and
    1 after the last field of the last row.

    Code:
    BCP.exe SourceDB..TableName out C:\path\to\tablename.csv /w /t\",\" /r\"\r\n\"
    This is fine for windows xp, but may need escaping differently for other systems.

    Regards,
    James Tandy
    TandyUK Servers

Posting Permissions

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