Professional Web Applications Themes

bulk copy using format file - Microsoft SQL / MS SQL Server

Hi, I have a table which i need to copy some data into. The data format is comma delimited with quotes around strings. Using the table below. At first I created a format file using the correct types ie SQLINT but found the integers were ed and seemed to correct itself when I just used SQLCHAR. Now if possible I would like to remove the quotes that appear around strings after I have imported the data Tia Stu Table : CREATE TABLE [dbo].[ImportTest] ( [idkey] [int] NOT NULL , [testdesc1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [idCountry] [int] NOT ...

  1. #1

    Default bulk copy using format file

    Hi,


    I have a table
    which i need to copy some data into. The data format is
    comma delimited with quotes around strings.
    Using the table below. At first I created a format file
    using the correct types ie SQLINT but found the integers
    were ed and seemed to correct itself when I just used
    SQLCHAR. Now if possible I would like to remove the quotes
    that appear around strings after I have imported the data

    Tia

    Stu
    Table :
    CREATE TABLE [dbo].[ImportTest] (
    [idkey] [int] NOT NULL ,
    [testdesc1] [varchar] (50) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [idCountry] [int] NOT NULL ,
    [testDesc2] [varchar] (50) COLLATE
    SQL_Latin1_General_CP1_CI_AS NOT NULL
    ) ON [PRIMARY]
    GO

    Bcp format File :
    8.0
    4
    1 SQLCHAR 0
    4 "," 1 idKey ""
    2 SQLCHAR 0
    50 "," 2 testdesc1
    SQL_Latin1_General_CP1_CI_AS
    3 SQLCHAR 0
    4 "," 3 idcountry ""
    4 SQLCHAR 0
    50 "\r\n" 4 testdesc2
    SQL_Latin1_General_CP1_CI_AS


    SQL command
    bulk insert cgcalcdb.dbo.importTest
    FROM 'c:\temp\import.txt'
    WITH (formatFile='c:\temp\bcpFormat')

    Stuart Guest

  2. #2

    Default Re: bulk copy using format file

    if you want to update table data you will have to use update statement as
    shown in the following example:
    ex:
    create table #t (i varchar(500))
    insert into #t values('"vishal"')
    insert into #t values('"vikram"')
    insert into #t values('"vijay"')
    --Update statement to replace double quotes.
    update #t set i = replace (i, '"', '')

    select * from #t

    --
    -Vishal

    "Stuart Dee" <com> wrote in message
    news:0cb601c35b50$6679b770$gbl... 


    Vishal Guest

  3. #3

    Default Re: bulk copy using format file

    I had already thought of that. I was hoping I could do it
    in one Bulk insert statement

    Thanks

     
    update statement as [/ref]
    used [/ref]
    quotes [/ref]
    data 
    >
    >
    >.
    >[/ref]
    Stuart Guest

  4. #4

    Default Re: bulk copy using format file

    Stu,
     

    You can eliminate the quotes in the format file.
    All fields in a text file, even numeric fileds, should be SQLCHAR in
    the format file.

    8.0
    4
    1 SQLCHAR 0 4 ",\"" 1 idKey ""
    2 SQLCHAR 0 50 "\"," 2 testdesc1 SQL_Latin1_General_CP1_CI_AS
    3 SQLCHAR 0 4 ",\"" 3 idcountry ""
    4 SQLCHAR 0 50 "\"\r\n" 4 testdesc2 SQL_Latin1_General_CP1_CI_AS


    Linda

    lindawie Guest

Similar Threads

  1. PROBLEM: copy then pastes at 90 degrees (pdf format)
    By timwilliamspl@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 12th, 03:46 PM
  2. Format XML Copy
    By evilpixel in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 16th, 05:31 PM
  3. text format during copy/paste
    By rick in forum FileMaker
    Replies: 2
    Last Post: November 11th, 06:45 PM
  4. format file and bcp/bulk insert
    By Robert Taylor in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 7th, 11:26 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139