Post reply

Bulk insert

  • February 25, 2009 at 7:18 am

    #384012

    hello

    declare @file as varchar(300)

    Declare @cmd nvarchar(4000)

    truncate table tablename

    set @file = '\\server\file.csv'

    --print @file

    SET @cmd = 'BULK INSERT tablename FROM '''+ @file +'''

    WITH (FIELDTERMINATOR = '','',

    ROWTERMINATOR = '''+ CHAR(10)+'''

    )'

    EXEC(@cmd)

    I am trying to inserting data from csv file into SQL table by Bulk insert. Now csv is comma separated file and some of the fields in the csv is having the comma as it is free text line. So the bulk insert thinks that the field has changed from that comma but it didnt. It is getting error coz of that.

    how can i avoid this?

    thanks,

    Vijay

  • February 25, 2009 at 9:06 am

    #950068

    doesn't sound like a valid CSV file

    are the text columns quoted or not?

    e.g. does the file look like this

    1,"company name","this is a description,company in USA"

    or is it

    1,company name,this is a description,company in USA

    or is it mixed

    1,company name,"this is a description,company in USA"

  • February 25, 2009 at 11:36 am

    #950183

    hi,

    it does look like

    "1,"company name","this is a description,company in USA"

    But in bulk insert the field terminator is "," so it is getting confused at description where it finds ,.

    thanks,

    vijay

  • February 25, 2009 at 11:53 am

    #950194

    dva2007 (2/25/2009)

    hi,

    it does look like

    "1,"company name","this is a description,company in USA"

    But in bulk insert the field terminator is "," so it is getting confused at description where it finds ,.

    thanks,

    vijay

    Isn't there an extra [ " ] ?


    * Noel

  • February 25, 2009 at 12:10 pm

    #950200

    all the fields are double quoted and separated by ,.

    so it should be "1" , "company", "hello"

    if it is like above text then it is fine.

    but it is "1", "company,name" , "hello"

    so it gets , in text of the field.

    thanks,

    vijay

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

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