I have two csv files like, first csv file BI1_2020.csv like this:
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
and another BI2_2020.csv like this:
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
...
I wrote a code that depending on the filename, either BI1 or BI2 will fill with data from the csv file of the tables in my database. The code works well but for both files it doesn't insert the first line of the csv file. How do I fix this?
<?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 !";
?>
You're checking count($bi1_values) before you add anything to it. So the count will always be 0, and you won't add to it. You should be adding the row of values to the array unconditionally, then using the count to determine whether to execute the query to insert all those rows.
You also have fclose($handle); inside the loop, which will prevent reading the rest of the file. It should be after the loop is done.
<?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 !";
?>