LOAD DATA LOCAL INFILE skipping columns

Question

I am loading a csv into my database using LOAD DATA LOCAL INFILE. I have the same number of columns in my database as in my csv file. My issue is the first 1000 or so lines import into sql properly, then the next several hundred lines are off by a couple of columns, and then it returns back to normal. My only guess could be that it's due to blank spaces.. Every field is enclosed by "" and I have specified that in my code. It would be a pain to update the csv manually as there's over 700,000 lines and Im downloading from an external server and will be updating it daily. I would really like to figure out the cause and solution of the problem.

Here's my load data code (without the db connection stuff):

$fieldseparator = ","; 
$lineseparator = "\r\n";
$doubleQuotes = "\"";
$csvfile = "Doba_Power_Export_Product_Apparel_shoes_and_jewelry.csv";

if(!file_exists($csvfile)) {
die("File not found. Make sure you specified the correct path.");
}

try {
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
    $databaseusername, $databasepassword,
    array(
        PDO::MYSQL_ATTR_LOCAL_INFILE => true,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    )
);
} catch (PDOException $e) {
die("database connection failed: ".$e->getMessage());
}

$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
  FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
  OPTIONALLY ENCLOSED BY ".$pdo->quote($doubleQuotes)."
  LINES TERMINATED BY ".$pdo->quote($lineseparator)."
  IGNORE 1 LINES ". $affectedRows);

echo "Loaded a total of $affectedRows records from this csv file.\n";

I have gotten this code from another Stackoverflow thread and have modified it many times to get to where I'm at. I have scoured Stackoverflow and many other online boards looking for an answer and have been unable to find one that works completely for me. My current code set has evolved to this from trying out one thing after another. I realize I may be looking in the wrong place, or searching the wrong phrase to find my answer. I would love to just be pointed in the right direction.

I don't know if this helps, but here is a couple of lines from my csv:

"6","4.00","B & F","17154","GFCAP2","Giovanni Navarre® Solid Genuine Leather Baseball Cap","","Spanning the worlds of sports and fashion, this soft solid leather, all American baseball cap affords attention and comfort to the lady or gentleman wearing it. Strap with hook and loop fastener easily adjusts to any size.","new","","","Giovanni Navarre","100","CN","2016-01-06 14:05:42.0","21548","GFCAP2","","024409080722","Giovanni Navarre® Solid Genuine Leather Baseball Cap","0.24","0","0","0.24","9.16","","0.00","5.39","","5.24","","19.95","1732","in-stock","2016-08-25","1","0","2016-06-28 14:05:39.0","","Apparel, shoes & jewelry||Apparel||Men||Hat","Case UPC:=024409003301||Web Description:=Spanning the worlds of sports and fashion, this soft solid leather, all American baseball cap affords attention and comfort to the lady or gentleman wearing it. Strap with hook and loop fastener easily adjusts to any size.","http://d1k0ppjronk6up.cloudfront.net/products/6/_Store_Support_images_GFCAP2_GFCAP2_800.jpg","800","800","","Apparel","0"

"6","4.00","B & F","17306","GFUM48","All-Weather™ 48" Auto-Open Umbrella","","For sporting events, outdoor concerts or just a plain rainy day, this umbrella will please even the most discriminating owner. Measures 48" across the top.","new","","","All Weather","60","CN","2016-01-09 14:05:46.0","21700","GFUM48","","024409081279","All-Weather™ 48" Auto-Open Umbrella","0.80","0","0","0.80","10.46","","0.00","4.96","","4.82","","18.95","567","in-stock","2016-10-20","0","0","2016-06-24 14:06:01.0","","Apparel, shoes & jewelry||Accessories||Cold-weather accessories","Case UPC:=024409006296||Web Description:=For sporting events, outdoor concerts or just a plain rainy day, this umbrella will please even the most discriminating owner. Measures 48" across the top.","http://d1k0ppjronk6up.cloudfront.net/products/6/_Store_Support_images_GFUM48_GFUM48_800.jpg","800","800","","Apparel","0"

Update:

This line is one that inserts correctly:

"6","4.00","B & F","17154","GFCAP2","Giovanni Navarre® Solid Genuine Leather Baseball Cap","","Spanning the worlds of sports and fashion, this soft solid leather, all American baseball cap affords attention and comfort to the lady or gentleman wearing it. Strap with hook and loop fastener easily adjusts to any size.","new","","","Giovanni Navarre","100","CN","2016-01-06 14:05:42.0","21548","GFCAP2","","024409080722","Giovanni Navarre® Solid Genuine Leather Baseball Cap","0.24","0","0","0.24","9.16","","0.00","5.39","","5.24","","19.95","1732","in-stock","2016-08-25","1","0","2016-06-28 14:05:39.0","","Apparel, shoes & jewelry||Apparel||Men||Hat","Case UPC:=024409003301||Web Description:=Spanning the worlds of sports and fashion, this soft solid leather, all American baseball cap affords attention and comfort to the lady or gentleman wearing it. Strap with hook and loop fastener easily adjusts to any size.","http://d1k0ppjronk6up.cloudfront.net/products/6/_Store_Support_images_GFCAP2_GFCAP2_800.jpg","800","800","","Apparel","0"

While this one is inserting incorrectly:

"93","0.00","Alphabroder","4188327","M999","12.7 oz. Fleece Blanket","","100% polyester fleece; One side is anti-pill; 60""""W x 50""""H; Finished with a matching whipstitch (Charcoal has a Black whipstitch);","new","cozy and durable fleece throw; each throw is finished with a matching whipstitch (except charcoal which has a black whipstitch); 60\ x 50\""""""""""""""""""""","Harriton","Harriton","0","","2015-10-30 10:40:51.0","4804844","M999~B03446430","B03446430","","CHARCOAL - OS","1.29","0","0","1.29","11.55","","0.00","7.39","","7.18","21.66","21.66","5618","in-stock","","0","0","2016-06-29 01:21:23.0","2015-10-29 12:13:27.0","Apparel, shoes & jewelry||Accessories||Bags & backpacks","color:=CHARCOAL||manufacturer:=Harriton||Popularity:=380||size:=OS","","","","","Apparel","0"

These are my fields:

"supplier_id","drop_ship_fee","supplier_name","product_id","product_sku","title","warranty","description","condition","details","manufacturer","brand_name","case_pack_quantity","country_of_origin","product_last_update","item_id","item_sku","mpn","upc","item_name","item_weight","ship_alone","ship_freight","ship_weight","ship_cost","max_ship_single_box","map","price","custom_price","prepay_price","street_price","msrp","qty_avail","stock","est_avail","pending_order_quantity","qty_on_order","item_last_update","item_discontinued_date","categories","attributes","image_file","image_width","image_height","additional_images","folder_paths","is_customized"

Answer 1

The line that was parsed incorrectly has backslashes in the details field. By default, MYSQL treats these as escape prefixes, but it looks like your data supplier intends them to be literal. You need to override that.

$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
  FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
  OPTIONALLY ENCLOSED BY ".$pdo->quote($doubleQuotes)."
  ESCAPED BY ''
  LINES TERMINATED BY ".$pdo->quote($lineseparator)."
  IGNORE 1 LINES ". $affectedRows);