Results 1 to 9 of 9

Thread: Bcp

  1. #1
    Join Date
    May 2004
    Posts
    25

    Unhappy Unanswered: Bcp

    I need to import a CSV file (strings are in quotations) into SQL server using BCP. Some of the fields contain quotations whereas some of them do not. When i try to import of file such as this an error regarding truncation appears and I am not able to import the data.
    When I try to import the same file without quotations around the fields it imports correctly. If you have help regarding this it would be greatly appreciated.

    "XXX","20040326080100",1040,"121732","100203223"," 0040227324003"

    or

    XXX,20040326080100,1040,121732,100203223,004022732 4003

    Thanks
    Drahos

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can they be tab delimited?

    The quotes are a pain
    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 2004
    Posts
    25
    No, actually I receive those delimited files with quoted strings from somewhere.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Let's see your bcp command?

    Did you try and DTS it in?
    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.

  5. #5
    Join Date
    May 2004
    Posts
    25
    I am using this command:

    bcp "Received_Data.dbo.XXX" in "Received_CSV.csv" -ffmt_file.fmt -T

    Data I am loading is huge and I was recomended to use bcp instead of DTS.
    I have tried DTS and it could load the data . There is a choise for 'Text qualifier' - you can choose 'double quoted', so it is OK.

    format file is attached

    Thanks again
    Drahos

  6. #6
    Join Date
    May 2004
    Posts
    25
    format file is attached
    Attached Files Attached Files

  7. #7
    Join Date
    May 2004
    Posts
    25
    Is there anyone who can help me with that?

  8. #8
    Join Date
    Feb 2004
    Posts
    492
    There's a workaround; tell bcp not to import specific columns. In your case, turn the quotes into columns. The example works for me and on the first column only:

    8.0
    8
    1 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
    2 SQLCHAR 0 3 "\"," 1 Col001 SQL_Latin1_General_CP1_CI_AS
    3 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
    4 SQLCHAR 0 25 "," 2 Col002 SQL_Latin1_General_CP1_CI_AS
    5 SQLCHAR 0 25 "," 3 Col003 SQL_Latin1_General_CP1_CI_AS
    6 SQLCHAR 0 25 "," 4 Col004 SQL_Latin1_General_CP1_CI_AS
    7 SQLCHAR 0 25 "," 5 Col005 SQL_Latin1_General_CP1_CI_AS
    8 SQLCHAR 0 25 "\r\n" 6 Col006 SQL_Latin1_General_CP1_CI_AS
    Last edited by Kaiowas; 08-30-04 at 13:31.

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Quote Originally Posted by Drahos
    Is there anyone who can help me with that?
    either use PERL/PYTHON to srip the quotes or use DTS. DTS will not be much slower than BCP but DTS will br 100 times easier to use and maintain.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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