Importing A CSV MsSql

Discussion in 'Databases' started by AndyFarrell, Aug 10, 2011.

0
  1. #1
    hello smart helpful people :)
    i am tryin to import a CSV into an SQL database. i am using the code below but it does not work because some column has , in its value. example "Auto Recyclers, Ltd." is there a way to import this correctly.



    [SQL]
    BULK
    INSERT junkyards
    FROM 'c:\junkyards.csv'
    WITH
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
    )
    GO

    [/SQL]




    example row


    link,"DD Engines, Inc.",44 Highway 231 ,Hazel Green,Alabama,35750,fax,phone2,phone1,url,Specializing in used and rebuilt engines
     
    AndyFarrell, Aug 10, 2011 IP
  2. demonbyte

    demonbyte Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    You will likely have to escape all commas between quotes in the CSV file using a backslash \

    Ex.

    link,"DD Engines\, Inc.",44 Highway 231 ,Hazel Green,Alabama,35750,fax,phone2,phone1,url,Specializing in used and rebuilt engines
     
    demonbyte, Aug 11, 2011 IP
  3. demonbyte

    demonbyte Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Upon further research it seams my initial solution is incorrect.

    I would take a look at this stack overflow post who had a similar issue:
    http://stackoverflow.com/questions/4123875/commas-within-csv-data
     
    demonbyte, Aug 11, 2011 IP
  4. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #4
    The csv spec calls for any text field with a comma to be quoted:

    300, xxx, "Auto Recyclers, Ltd.", Auto Recyclers, ...

    To make life simpler, every text field can be quoted and it will still work.
     
    Rukbat, Aug 24, 2011 IP