php - LOAD DATA LOCAL INFILE 跳过列

Question

我正在使用 LOAD DATA LOCAL INFILE 将 csv 加载到我的数据库中。我的数据库中的列数与 csv 文件中的列数相同。我的问题是前 1000 行左右正确地导入到 sql 中，然后接下来的几百行被几列关闭，然后恢复正常。我唯一的猜测可能是它是由于空格引起的。每个字段都用“”括起来，我已经在我的代码中指定了它。手动更新 csv 会很痛苦，因为有超过 700,000 行，我从外部服务器下载并将每天更新。我真的很想弄清楚问题的原因和解决方案。

这是我的加载数据代码(没有数据库连接的东西):

$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";

我从另一个 Stackoverflow 线程获得了这段代码，并对其进行了多次修改以到达我所在的位置。我搜索了 Stackoverflow 和许多其他在线论坛来寻找答案，但一直无法找到完全适合我的答案。我当前的代码集已经从尝试一个接一个的东西演变成了这个。我意识到我可能找错了地方，或者搜索了错误的短语来找到我的答案。我很乐意被指出正确的方向。

我不知道这是否有帮助，但这是我的 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"

更新:

这一行是正确插入的:

"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"

虽然这个插入不正确:

"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"

这些是我的字段:

"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

被错误解析的行在 details 字段中有反斜杠。默认情况下，MYSQL 将这些视为转义前缀，但看起来您的数据供应商希望它们是文字。你需要覆盖它。

$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);