1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Import huge csv from Affiliate Window using LOAD DATA LOCAL INFILE

Discussion in 'MySQL' started by jimmyt200388, Jul 30, 2015.

  1. #1
    I am trying to import huge csv files without having to split them up and use phpmyadmin.

    Here is a sample of my csv format (some fields have "" some dont.
    
    aw_product_id,merchant_product_id,merchant_category,aw_deep_link,merchant_image_url,search_price,description,product_name,merchant_deep_link,aw_image_url,merchant_name,merchant_id,category_name,category_id,delivery_cost,currency,store_price,display_price,data_feed_id,web_offer,brand_name
    3156121231,462496,/cycle/clothing/ladies/gloves,http://www.awin1.com/pclick.php?p=3156121231&a=117981&m=2485,http://products.activinstinct.com/y2014/m09/462496/14723969-n.jpg,33.49,"A close fitting fully waterproof glove with ProGel palm padding for better cushioning on the handlebars.Features:*Waterproof, windproof, breathable construction*ProGel palm padding*Cuff adjuster for precise fitting*Critically positioned reflective trims","ALTURA ProGel Waterproof Ladies Gloves",http://www.activinstinct.com/cycle/clothing/ladies/gloves/altura-progel-waterproof-ladies-gloves/,http://images.productserve.com/preview/5/2485/31/12/3156121231.jpg,ActivInstinct,2485,"Women's Accessories",149,3.95,GBP,,GBP33.49,2485,0,Altura
    
    Code (markup):
    This is my code so far:
    
    $pdo = new PDO($dsn, $user, $password, array(PDO::MYSQL_ATTR_LOCAL_INFILE=>1));
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $pdo->exec("LOAD DATA LOCAL INFILE 'example.csv'
    INTO TABLE products
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY '\'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (aw_product_id,merchant_product_id,merchant_category,aw_deep_link,merchant_image_url,search_price,description,product_name,merchant_deep_link,aw_image_url,merchant_name,merchant_id,category_name,category_id,delivery_cost,currency,store_price,display_price,data_feed_id,web_offer,brand_name);");
    
    PHP:
    Any help would be greatly appreciated as I have been struggling with it for days. I can get it to work without the optionally enclosed by " but this messes up the formatting and things go into new columns
     
    jimmyt200388, Jul 30, 2015 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    Remove the "optionally enclosed by" line, and make sure all the fields are enclosed by ""? That should work.
     
    PoPSiCLe, Jul 30, 2015 IP
  3. jimmyt200388

    jimmyt200388 Well-Known Member

    Messages:
    192
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    130
    #3
    How can I make sure all fields are enclosed by "" if the csv comes from an external source? Can this be done automated?
     
    jimmyt200388, Jul 31, 2015 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    Well... yes, but it might be trouble... you would have to have a rather good knowledge of what type of data the content is - however, if it's already formatted like you explained, and doesn't deviate too much from that, you could do this with PHP, some regex and proper formatting, I believe. Not that I'm a big regex-person myself, so I'm not entirely sure what you would have to do, but basically, what you would look for is something like for every separator (ie, the comma), you replace it with "," - except where it's already enclosed in "" - however, is it perhaps possible to get the external source with a different separator? A | for instance? That would probably make it a lot easier to find each one (could be done without any regex, even).
     
    PoPSiCLe, Jul 31, 2015 IP
  5. pmf123

    pmf123 Notable Member

    Messages:
    1,447
    Likes Received:
    75
    Best Answers:
    0
    Trophy Points:
    215
    #5
    get rid of "OPTIONALLY"
     
    pmf123, Aug 19, 2015 IP