Import huge csv from Affiliate Window using LOAD DATA LOCAL INFILE

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

 

Remove the "optionally enclosed by" line, and make sure all the fields are enclosed by ""? That should work.

 

How can I make sure all fields are enclosed by "" if the csv comes from an external source? Can this be done automated?

 

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).

 

get rid of "OPTIONALLY"