Results 1 to 2 of 2

Thread: bcp in issue

  1. #1
    Join Date
    Jul 2007
    Posts
    1

    Thumbs up Unanswered: bcp in issue

    Hi,

    I need to know how to do a bcp in for the following file which have quotes on the column. Any idea how can I get the data into a table? Format below.
    "P",100529,"USD"

    create table t1
    (
    c1 char(1),
    c2 int,
    c3 varchar(3)
    )

    appreciate your help. PM me is possible.
    tks.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    use a format file e.g.
    Code:
    1> create table t1 (c1 char(3), c2 int, c3 varchar(100))
    2> go
    
    $ cat t1.txt
    "NY",2,"New York, USA"
    "II",1,"India"
    "GP",3,"Gauteng, RSA"
    $ cat t1.fmt
    10.0
    4
    1       SYBCHAR 0       100     "\""    0       junk1
    2       SYBCHAR 0       100     "\","   1       data1
    3       SYBCHAR 0       100     ",\""   2       id
    4       SYBCHAR 0       100     "\"\n"  3       address
    $ bcp tempdb..t1 in t1.txt -Usa -P -Smysrv  -f t1.fmt -e t1.err
    
    Starting copy...
    
    3 rows copied.
    Clock Time (ms.): total = 1  Avg = 0 (3000.00 rows per sec.)
    
    1> select * from t1
    2> go
     c1  c2          c3                       
     --- ----------- -----------------------------------------------
     NY            2 New York, USA            
     II            1 India                    
     GP            3 Gauteng, RSA             
    
    (3 rows affected)
    1> drop table t1
    2> go

Posting Permissions

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