Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: Load Data - CSV

  1. #1
    New Coder
    Join Date
    Mar 2010
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Load Data - CSV

    Hi all and happy new year...hope you can help with my csv issue.

    I'm trying to load some data into my db using a csv file, everything is fine until it come across a string that has "quotation marks" around it and messes up the table row.

    Any ideas how to escape it?

    Example CSV Data

    "1","Joe","Bloggs",""16 AppleTree Drive""

    Code used for importing data

    LOAD DATA LOCAL INFILE '$csv' REPLACE INTO TABLE loader fields terminated by ',' enclosed by '\"' lines terminated by '\r\n' IGNORE 1 LINES ($col_names)

    Thanks for your help

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    This isn't a PHP issue, its a SQL import call. There should be an escape option of \ which is default in PHP csv handling and I would presume SQL as well.
    The issue you will have is that you don't have it escaped. Without escaping those " in there, you cannot expect that the csv can be properly interpreted.
    So, I'd say there are really two options. The first is to drop the enclosed by option, and insert the record with " surrounding the entry. A temporary table would suffice, and then use a SUBSTRING() call to trim them off and insert into the correct field.
    Or, use PHP to read the data, strip out the surrounding "'s or escape them where needed, and then insert that into the database.

  • #3
    New Coder
    Join Date
    Mar 2010
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the reply, it's greatly appreciated.


  •  

    Posting Permissions

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