Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Posts
    58

    Question Unanswered: bcp and text qualifying

    I would like to output to a csv file using bcp with the fields text qualified by double quotes. Like this:

    "1","John Doe","100 main st"
    "2","John Smith","101 main st"
    "3","John Johnson","102 main st"

    Right now here's what my bcp command looks like:
    bcp "select id, name, address from People" QUERYOUT d:\data.csv -S aserver -T -t"," -R

    And it returns results without quotes:
    1,John Doe,100 main st
    2,John Smith,101 main st
    3,John Johnson,102 main st

    How to I get the results in the file with quotes? Thanks for the help.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I believe it is

    /t"\",\""

    The inner double quotes have to be escaped. Hope this helps.

  3. #3
    Join Date
    Feb 2002
    Posts
    58
    I tried that, which works for every field, except the 2 outside fields. The first field doesn't have a begining quote and the last field doesnt have an ending quote.

    Thanks for the reply though. I appricate the help. Same goes to anyone else who has an idea!

  4. #4
    Join Date
    Feb 2002
    Posts
    58

    Wink

    Alright, so now I tried using a format file, and it worked well, I was able to get double quotes around every field except the first one. So I did some research and supposedly you can add the line:
    1 SQLCHAR 0 0 "\"" 1 first_quote ""
    to your format file, and it will add a " to the beginning of each record.

    Upon trying this I get the error: Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server

    Doing research on the error I came up with nothing. Any ideas?

  5. #5
    Join Date
    Feb 2002
    Posts
    58
    Has anyone created a csv file with text qualifiers using the bcp command?

  6. #6
    Join Date
    Sep 2003
    Posts
    8
    Originally posted by WhiZa
    Has anyone created a csv file with text qualifiers using the bcp command?

    Did you try to format each field in the select command ?
    Maybe this is not the best way but is working.

  7. #7
    Join Date
    Feb 2002
    Posts
    58
    I guess I figured it out.

    I my select statement I added a field like this SELECT '', * FROM...

    After doing this I was able to add 1 SQLCHAR 0 0 "\"" 1 first_quote ""
    to the format file, and I was able to add a " to the begining of each record. Acctualy it adds a space and a " to each record, but it works all the same.

    I hope this helps someone out, it took my slow brain a while to figure it out

Posting Permissions

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