Post reply

Bulk Insert Loading problem

  • March 28, 2006 at 9:10 am

    #93707

    using sql 2000

    I have used Bulk Insert many times, but as in all things, something new comes up.

    I am trying to use a 'bulk insert' command on a comma delimited file with a 'format file' in which the string columns of the data are surrounded by double quotes.  I cannot get the data loaded because with the double quotes I would get truncation.

    any idea on how to get this to work with the double quotes?

    Russ....

     

  • March 28, 2006 at 5:07 pm

    #629320

    Create a format file using bcp and use the same in ur BULK INSERT

  • March 29, 2006 at 7:34 am

    #629482

    Thanks for responding.  I do have and reference a format file and the first few lines are like this:

    8.0

    22

    1     SQLCHAR    0    72     ","     1     business_name   SQL_Latin1_General_Cp1_CI_AS

    2     SQLCHAR    0    70     ","     2     business_address     SQL_Latin1_General_Cp1_CI_AS

    3     SQLCHAR    0    20     ","     3     business_city     SQL_Latin1_General_Cp1_CI_AS

    4     SQLCHAR    0    9      ","     4     business_zip     SQL_Latin1_General_Cp1_CI_AS

    5     SQLCHAR    0    15     ","     5     business_phone     SQL_Latin1_General_Cp1_CI_AS

    I have the column terminator set as 'comma', but how do I indicate the field delimiter of double quotes?

    Russ...

  • March 29, 2006 at 8:09 am

    #629492

    This has been in the forums fairly often.  Check

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=165368

    Basically, your delimiter would be "\",\"", but there a couple "tricks" you need to do for the first and last column.  See Jeff's description in the link above.

    Hope this helps



    Mark

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply