CSV文件导入到mysql数据库使用PHP面临导入错误 - csv file import to mysql database using php facing error to import

使用php脚本在phpmyadmin中导入CVS文件(Microsoft Office Excel逗号分隔值文件(.csv))。当我导入CSV文件，然后它没有正确导入到数据库。行以"，"结束。请建议我如何使它行终止"auto"，我附上示例文件。

我只是与这个链接共享CSV文件http://www.soniinfotech.com/product-data.csv

经过一些建议和纠正，我得到了另一个问题。实际cvs文件大小为106700行，其插入为81499行。我有插入文件七次，但它的总行是81499插入。请帮助我解决这个问题，我如何可以插入所有行数据库。

<?php
set_time_limit(2400);
$mysqli = new mysqli("localhost" , "user" , "pass" , "db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
$table_name= "store";
$csv_file = "import.csv"; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$field_csv = array();
$i = 0;
while (($csv_data = fgetcsv($csvfile, 0, ",")) !== FALSE) {     if($i==0) { $i++; continue; }  // to exclude first line in the csv file.
$field_csv['productId'] = $csv_data[0];  // 1
$field_csv['title'] = $csv_data[1]; // 2
$field_csv['description'] = $csv_data[2]; // 3
$field_csv['imageUrlStr'] = $csv_data[3];  // 4
$field_csv['mrp'] = $csv_data[4]; // 5
$field_csv['price'] = $csv_data[5]; // 6
$field_csv['productUrl'] = $csv_data[6];  // 7
$field_csv['categories'] = $csv_data[7]; // 8
$field_csv['productBrand'] = $csv_data[8]; // 9
$field_csv['deliveryTime'] = $csv_data[9];  // 10
$field_csv['inStock'] = $csv_data[10]; // 11
$field_csv['codAvailable'] = $csv_data[11]; // 12
$field_csv['emiAvailable'] = $csv_data[12];  // 13
$field_csv['offers'] = $csv_data[13]; // 14
$field_csv['discount'] = $csv_data[14]; // 15
$field_csv['cashBack'] = $csv_data[15];  // 16
$field_csv['size'] = $csv_data[16]; // 17
$field_csv['color'] = $csv_data[17]; // 18
$field_csv['sizeUnit'] = $csv_data[18];  // 19
$field_csv['sizeVariants'] = $csv_data[19]; // 20
$field_csv['colorVariants'] = $csv_data[20]; // 21
$field_csv['styleCode'] = $csv_data[21]; // 22
$query = "INSERT INTO $table_name SET productId = '".$field_csv['productId']."',title = '".$field_csv['title']."',description = '".$field_csv['description']."',imageUrlStr = '".$field_csv['imageUrlStr']."',mrp = '".$field_csv['mrp']."',price = '".$field_csv['price']."',productUrl = '".$field_csv['productUrl']."',categories = '".$field_csv['categories']."',productBrand = '".$field_csv['productBrand']."',deliveryTime = '".$field_csv['deliveryTime']."',inStock = '".$field_csv['inStock']."',codAvailable = '".$field_csv['codAvailable']."',emiAvailable = '".$field_csv['emiAvailable']."',offers = '".$field_csv['offers']."',discount = '".$field_csv['discount']."',cashBack = '".$field_csv['cashBack']."',size = '".$field_csv['size']."',color = '".$field_csv['color']."',sizeUnit = '".$field_csv['sizeUnit']."',sizeVariants = '".$field_csv['sizeVariants']."',colorVariants = '".$field_csv['colorVariants']."',styleCode = '".$field_csv['styleCode']."' ";
mysqli_query($mysqli,$query);
}
fclose($csvfile);
echo "CSV data successfully imported to table!!";
// close connection
$mysqli->close();
?>

您提供的代码有一些问题:

1。不指定最大行宽

只有当您知道长度参数(第二个参数)足以读取整行时才将长度参数(第二个参数)指定为fgetcsv()。否则，只需使用0(零)让PHP自动检测换行符。(它甚至会智能地使用新行，实际上是换行，避免在CSV引用值中可能发生的换行)

fgetcsv($csvfile, 0, ",")

2。在插入到DB

之前转义字符串

当你在MySQL中插入字符串值时，你需要正确地转义它们。如果您不这样做，并且字符串包含引号，它可能会破坏您的SQL查询，导致插入失败。

使用非转义字符串也会带来sql注入攻击的风险。

转义字符串，使用mysqli_real_escape_string()函数

即:

$field_csv['productId'] = mysqli_real_escape_string( $mysqli, $csv_data[0] );  // 1
$field_csv['title'] = mysqli_real_escape_string( $mysqli, $csv_data[1] ); // 2
$field_csv['description'] = mysqli_real_escape_string( $mysqli, $csv_data[2] ); // 3
$field_csv['imageUrlStr'] = mysqli_real_escape_string( $mysqli, $csv_data[3] );  // 4
// ... etc.

3。使用mysqli_error()调试SQL查询中可能出现的错误

如果你不检查你的SQL查询错误，你是盲目的。如果发生了错误的事情，你甚至可能没有注意到它。

始终检查mysqli_query()的返回值。(查询失败时将返回布尔值FALSE)并使用mysqli_error()

打印错误

if ( ! mysqli_query( $mysqli,$query ) ) {
  printf( "Error: %s'n", mysqli_error( $mysqli ) );
}

<?php
set_time_limit(2400);
$mysqli = new mysqli("host" , "user" , "pass" , "db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
$table_name= "store";
$csv_file = "import.csv"; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$field_csv = array();
$i = 0;
while (($csv_data = fgetcsv($csvfile, 0, ",")) !== FALSE) {
    if($i==0) { $i++; continue; }  // to exclude first line in the csv file.
$field_csv['productId'] =mysqli_real_escape_string( $mysqli, $csv_data[0] );  // 1
$field_csv['title'] = mysqli_real_escape_string( $mysqli, $csv_data[1]     ); // 2
$field_csv['description'] = mysqli_real_escape_string( $mysqli, $csv_data[2] ); // 3
$field_csv['imageUrlStr'] = mysqli_real_escape_string( $mysqli, $csv_data[3] );  // 4
$field_csv['mrp'] = mysqli_real_escape_string( $mysqli, $csv_data[4] ); // 5
$field_csv['price'] = mysqli_real_escape_string( $mysqli, $csv_data[5] ); // 6
$field_csv['productUrl'] = mysqli_real_escape_string( $mysqli, $csv_data[6] );  // 7
$field_csv['categories'] = mysqli_real_escape_string( $mysqli, $csv_data[7] ); // 8
$field_csv['productBrand'] = mysqli_real_escape_string( $mysqli, $csv_data[8] ); // 9
$field_csv['deliveryTime'] = mysqli_real_escape_string( $mysqli, $csv_data[9] );  // 10
$field_csv['inStock'] = mysqli_real_escape_string( $mysqli, $csv_data[10] ); // 11
$field_csv['codAvailable'] = mysqli_real_escape_string( $mysqli, $csv_data[11] ); // 12
$field_csv['emiAvailable'] = mysqli_real_escape_string( $mysqli, $csv_data[12] );  // 13
$field_csv['offers'] = mysqli_real_escape_string( $mysqli, $csv_data[13] ); // 14
$field_csv['discount'] = mysqli_real_escape_string( $mysqli, $csv_data[14] ); // 15
$field_csv['cashBack'] = mysqli_real_escape_string( $mysqli, $csv_data[15] );  // 16
$field_csv['size'] = mysqli_real_escape_string( $mysqli, $csv_data[16] ); // 17
$field_csv['color'] = mysqli_real_escape_string( $mysqli, $csv_data[17] ); // 18
$field_csv['sizeUnit'] = mysqli_real_escape_string( $mysqli, $csv_data[18] );  // 19
$field_csv['sizeVariants'] = mysqli_real_escape_string( $mysqli, $csv_data[19] ); // 20
$field_csv['colorVariants'] = mysqli_real_escape_string( $mysqli, $csv_data[20] ); // 21
$field_csv['styleCode'] = mysqli_real_escape_string( $mysqli, $csv_data[21] ); // 22

$query = "INSERT INTO $table_name SET productId = '".$field_csv['productId']."',title = '".$field_csv['title']."',description = '".$field_csv['description']."',imageUrlStr = '".
$field_csv['imageUrlStr']."',mrp = '".$field_csv['mrp']."',price = '".$field_csv['price']."',productUrl = '".$field_csv['productUrl']."',categories = '".$field_csv['categories']."',productBrand = '".
$field_csv['productBrand']."',deliveryTime = '".$field_csv['deliveryTime']."',inStock = '".$field_csv['inStock']."',codAvailable = '".$field_csv['codAvailable']."',emiAvailable = '".$field_csv
['emiAvailable']."',offers = '".$field_csv['offers']."',discount = '".$field_csv['discount']."',cashBack = '".$field_csv['cashBack']."',size = '".$field_csv['size']."',color = '".$field_csv
['color']."',sizeUnit = '".$field_csv['sizeUnit']."',sizeVariants = '".$field_csv['sizeVariants']."',colorVariants = '".$field_csv['colorVariants']."',styleCode = '".$field_csv['styleCode']."' ";
mysqli_query($mysqli,$query);
}
fclose($csvfile);
echo "CSV data successfully imported to table!!";
// close connection
$mysqli->close();
?>