小贝子编程

如何在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 !";

?>

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium