我有兩個 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;EUR391;EN-RC O/O HOSSEGOR MEN;07/21/2020;0021000002510;WLY9BO-RC;1000-WHITE;2;1;22.99;19.16;4.81;EUR391;FR-RC O/O HOSSEGOR MEN;07/21/2020;0021000002510;WLY5DB-RC;40-RED;8;1;19.99;16.66;4.2;EUR391;EN-RC O/O HOSSEGOR MEN;07/21/2020;0021000002510;OBOAU4-RC;90-BLACK;2;1;39.99;33.33;11.27;EUR391;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;227336;00203-ET1;90-BLACK;10;127336;00203-ET1;90-BLACK;12;127396;00203-ET1;90-BLACK;9.5;3...我編寫了一段代碼，根據文件名，BI1 或 BI2 將填充數據庫中表的 csv 文件中的數據。該代碼運行良好，但對于這兩個文件，它不會插入 csv 文件的第一行。我該如何解決？
1 回答
幕布斯6054654
TA貢獻1876條經驗 獲得超7個贊
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 !";
?>
- 1 回答
- 0 關注
- 91 瀏覽
添加回答
舉報
0/150
提交
取消