php - 如何在 php 数据库中插入 csv 文件的第一行？
问题描述
我有两个 csv 文件，第一个 csv 文件 BI1_2020.csv 是这样的：
391;FR-RC O/O HOSSEGOR MEN;07/21/2020;0021000002509;GR01-S8;8666-CLEAR;TU;1;15;12.5;0;EUR
391;EN-RC O/O HOSSEGOR MEN;07/21/2020;0021000002510;WLY9BO-RC;1000-WHITE;2;1;22.99;19.16;4.81;EUR
391;FR-RC O/O HOSSEGOR MEN;07/21/2020;0021000002510;WLY5DB-RC;40-RED;8;1;19.99;16.66;4.2;EUR
391;EN-RC O/O HOSSEGOR MEN;07/21/2020;0021000002510;OBOAU4-RC;90-BLACK;2;1;39.99;33.33;11.27;EUR
391;FR-RC O/O HOSSEGOR MEN;07/21/2020;0021000002515;4OCEAN-HF;40-RED;TU;1;20;16.67;0;EUR
和另一个像这样的 BI2_2020.csv：
27336;00203-ET1;90-BLACK;9.5;2
27336;00203-ET1;90-BLACK;10;1
27336;00203-ET1;90-BLACK;12;1
27396;00203-ET1;90-BLACK;9.5;3
...
我编写了一个代码，根据文件名，BI1 或 BI2 将填充数据库中表的 csv 文件中的数据。该代码运行良好，但对于这两个文件，它都不会插入 csv 文件的第一行。我该如何解决？
<?php
// Starting clock time in seconds
$time_start = microtime(true);
include("../connexion.php");
ini_set('max_execution_time', 54000);
$timestamp = date("Y-m-d H:i:s");
$dir = 'D:/xampp/htdocs/retail_BI/test/';
$allFiles = scandir($dir);
$dest = 'D:/xampp/htdocs/retail_BI/test/files/';
foreach($allFiles as $file) {
if (!in_array($file,array(".","..")))
{
$file = $dir.$file;
$filename = basename( $file );
if ( strpos( $filename, 'BI1_' ) === 0 ) {
if (($handle = fopen($filename, "r")) !== false) {
//To remove BOM in the fist cell
fseek($handle, 3);
$bi1_values = array();
while (($data = fgetcsv($handle, 9000000, ";")) !== false) {
if (empty(array_filter($data))) {
continue;
}
$date = DateTime::createFromFormat('d/m/Y H:i:s A', $data[2]);
// if ($date === false) {
// break;
// }
$date1 = $date->format('Y-m-d'); // 2020-07-07
$date2 = $date->format('Hi A'); // 1247 AM
$bi1_values[] = "('$data[0]', '$data[1]','$date1','$date2','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]')";
if (count($bi1_values) == 1000) {
$query = "INSERT INTO dbo.Y2_Sales (storenumber, storename, date, time, TransRef, stylecode, color, size, quantity, unit_price, SalesExGST, cost, currency) VALUES " . implode(',', $bi1_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file1 = fopen("errors/erreur_BI1.txt", "w");
fwrite($file1,$query);
fclose($file1);
// $file1 = "errors/erreur_BI1.txt";
// file_put_contents($file1, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error; }
}
$bi1_values = array();
}
}
if (!empty($bi1_values)) {
$query = "INSERT INTO dbo.Y2_Sales (storenumber, storename, date, time, TransRef, stylecode, color, size, quantity, unit_price, SalesExGST, cost, currency) VALUES " . implode(',', $bi1_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file1 = fopen("errors/erreur_BI1.txt", "w");
fwrite($file1,$query);
fclose($file1);
// $file1 = "errors/erreur_BI1.txt";
// file_put_contents($file1, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error; }
}
}
fclose($handle);
//Moving the file to another folder
if(!rename($file, $dest . $filename)) {
echo "error copy";
}
}
} elseif ( strpos( $filename, 'BI2_' ) === 0 ) {
if (($handle = fopen($filename, "r")) !== false) {
// To remove BOM in the fist cell
fseek($handle, 3);
$bi2_values = array();
while (($data = fgetcsv($handle, 9000000, ";")) !== false) {
// print_r($data);
if (empty(array_filter($data))) {
continue;
}
$bi2_values[] = "('$data[0]', '$data[1]','$data[2]','$data[3]','$data[4]', '$timestamp')";
if (count($bi2_values) == 1000) {
$query = "INSERT INTO dbo.Y2_Inventory (storenumber, stylecode, colour, size, units, timestamp) VALUES " . implode(',', $bi2_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file2 = fopen("errors/erreur_BI2.txt", "w");
fwrite($file2,$query);
fclose($file2);
// $file2 = "errors/erreur_BI2.txt";
// file_put_contents($file2, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error;}
}
$bi2_values = array();
}
}
if (!empty($bi2_values)) {
$query = "INSERT INTO dbo.Y2_Inventory (storenumber, stylecode, colour, size, units, timestamp) VALUES " . implode(',', $bi2_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file2 = fopen("errors/erreur_BI2.txt", "w");
fwrite($file2,$query);
fclose($file2);
// $file2 = "errors/erreur_BI2.txt";
// file_put_contents($file2, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error;}
}
}
fclose($handle);
// Moving the file to another folder
if(!rename($file, $dest . $filename)) {
echo "error copy";
}
}
} elseif ( strpos( $filename, 'BI3_' ) === 0 ) {
if (($handle = fopen($filename, "r")) !== false) {
// To remove BOM in the fist cell
fseek($handle, 3);
$bi3_values = array();
while (($data = fgetcsv($handle, 9000000, ";")) !== false) {
// print_r($data);
if (empty(array_filter($data))) {
continue;
}
$bi3_values[] = "('$data[0]', '$data[1]','$data[2]','$data[3]','$data[4]', '$data[5]')";
if (count($bi3_values) == 1000) {
$query = "INSERT INTO dbo.Y2_Style (stylecode, brandname, department, category, description, season) VALUES " . implode(',', $bi3_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file3 = fopen("errors/erreur_BI3.txt", "w");
fwrite($file3,$query);
fclose($file3);
// $file3 = "errors/erreur_BI3.txt";
// file_put_contents($file3, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error;}
}
$bi3_values = array();
}
}
if (!empty($bi3_values)) {
$query = "INSERT INTO dbo.Y2_Style (stylecode, brandname, department, category, description, season) VALUES " . implode(',', $bi3_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file3 = fopen("errors/erreur_BI3.txt", "w");
fwrite($file3,$query);
fclose($file3);
// $file3 = "errors/erreur_BI3.txt";
// file_put_contents($file3, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error;}
}
}
fclose($handle);
// Moving the file to another folder
if(!rename($file, $dest . $filename)) {
echo "error copy";
}
}
}
}
}
$texte = "Process took ". number_format(microtime(true) - $time_start, 2). " seconds.";
file_put_contents("time.txt", $texte);
echo "good !";
?>
解决方案
count($bi1_values)在添加任何内容之前，您正在检查。所以计数将始终为 0，并且您不会添加它。您应该无条件地将值行添加到数组中，然后使用计数来确定是否执行查询以插入所有这些行。
您还有fclose($handle);循环内部，这将阻止读取文件的其余部分。它应该在循环完成之后。
<?php
// Starting clock time in seconds
$time_start = microtime(true);
include("../connexion.php");
ini_set('max_execution_time', 54000);
// $timestamp= date("y-m-d H:i");
$timestamp = date("Y-m-d H:i:s");
$dir = 'D:/xampp/htdocs/retail_BI/test/';
$allFiles = scandir($dir);
$dest = 'D:/xampp/htdocs/retail_BI/test/files/';
foreach($allFiles as $file) {
if (!in_array($file,array(".","..")))
{
$file = $dir.$file;
$filename = basename( $file );
if ( strpos( $filename, 'BI1_' ) === 0 ) {
if (($handle = fopen("$filename", "r")) !== false) {
//To remove BOM in the fist cell
fseek($handle, 3);
$bi1_values = array();
while (($data = fgetcsv($handle, 9000000, ";")) !== false) {
if (empty(array_filter($data))) {
continue;
}
$date = DateTime::createFromFormat('d/m/Y H:i:s A', $data[2]);
if ($date === false) {
break;
}
$date1 = $date->format('Y-m-d'); // 2020-07-07
$date2 = $date->format('Hi A'); // 1247 AM
$bi1_values[] = "('$data[0]', '$data[1]','$date1','$date2','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]')";
if (count($bi1_values) == 1000) {
$query = "INSERT INTO dbo.Y2_Sales (storenumber, storename, date, time, TransRef, stylecode, color, size, quantity, unit_price, SalesExGST, cost, currency) VALUES " . implode(',', $bi1_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file1 = "erreur_BI1.txt";
file_put_contents($file1, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error; }
}
$bi1_values = array();
}
}
if (!empty($bi1_values)) {
$query = "INSERT INTO dbo.Y2_Sales (storenumber, storename, date, time, TransRef, stylecode, color, size, quantity, unit_price, SalesExGST, cost, currency) VALUES " . implode(',', $bi1_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file1 = "erreur_BI1.txt";
file_put_contents($file1, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error; }
}
}
fclose($handle);
//Moving the file to another folder
if(!rename($file, $dest . $filename)) {
echo "error copy";
}
}
} else if ( strpos( $filename, 'BI2_' ) === 0 ) {
if (($handle = fopen("$filename", "r")) !== false) {
// To remove BOM in the fist cell
fseek($handle, 3);
$bi2_values = array();
while (($data = fgetcsv($handle, 9000000, ";")) !== false) {
// print_r($data);
if (empty(array_filter($data))) {
continue;
}
$bi2_values[] = "('$data[0]', '$data[1]','$data[2]','$data[3]','$data[4]', '$timestamp')";
if (count($bi2_values) == 1000) {
$query = "INSERT INTO dbo.Y2_Inventory (storenumber, stylecode, colour, size, units, timestamp) VALUES " . implode(',', $bi2_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file2 = "erreur_BI2.txt";
file_put_contents($file2, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error;}
}
$bi2_values = array();
}
}
if (!empty($bi2_values)) {
$query = "INSERT INTO dbo.Y2_Inventory (storenumber, stylecode, colour, size, units, timestamp) VALUES " . implode(',', $bi2_values);
$stmt = $conn->query( $query );
if (!$stmt) {
$file2 = "erreur_BI2.txt";
file_put_contents($file2, $query . PHP_EOL, FILE_APPEND | LOCK_EX);
// echo $conn->error;}
}
}
fclose($handle);
// Moving the file to another folder
if(!rename($file, $dest . $filename)) {
echo "error copy";
}
}
}
}
}
$texte = "Process took ". number_format(microtime(true) - $time_start, 2). " seconds.";
file_put_contents("time.txt", $texte, FILE_APPEND | LOCK_EX);
echo "good !";
?>
推荐阅读
- python - 如何在 2 行中水平附加到数组
- java - 检索表中包含 SQLite 特定列中的特定值的最新行的最佳方法是什么？
- python - 三个不同的数据框行组合
- r - 在 R 中的任何列中呈现不为零的行
- flutter - 如何防止我的应用程序状态发生变化？
- powershell - powershell 上等效的 Unix 命令
- permissions - 谷歌上的权限操作未显示
- tomcat - 使用 Tomcat jar 时 ANT 战争文件编译失败
- asynchronous - 如何在 Actix-web 的 WebSocket 处理程序中正确调用异步函数
- facebook - facebook 更改后获取 facebook 用户个人资料图片（10 月 24 日）