How to insert the first line of a csv file in a php database?

Question

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 !";


?>

There is break statement when $date equals false. Could that be causing it? And shouldn't it be continue? — Rolf
– Rolf, Commented Jul 22, 2020 at 12:51
In my first line my date is the same as the others so he should insert the line but each time he skips it @Rolf — user12886821
– user12886821, Commented Jul 22, 2020 at 12:54
Print a message before each continue, to see if that is being triggered. — Barmar
– Barmar, Commented Jul 22, 2020 at 13:10
I generally recommend it, it simplifies the code. Remember to use the FILE_APPEND option. — Barmar
– Barmar, Commented Jul 28, 2020 at 18:16

Barmar · Accepted Answer · 2020-07-30 14:56:14Z

0

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 !";


?>

edited Jul 30, 2020 at 14:56

answered Jul 29, 2020 at 15:09

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user12886821 Over a year ago

Thank you very much for your answer, it works perfectly! There's just an extra brace at the end to close the first "if (strpos..." @Barmar

user12886821 Over a year ago

I changed 'file_put_contents' with 'fopen' because at each execution of the script it doesn't overwrite the old errors but puts the new one after it @Barmar

user12886821 Over a year ago

The insertion is weird: on the second file no problem, but on the 3rd it doesn't insert any data and the 2nd it stops at 4000 lines. I think that if it finds a line in error in the 1000 lines block it doesn't insert the whole block instead of just excluding the line in question. Because if I do a classic line-by-line insertion it works fine @Barmar

Barmar Over a year ago

FILE_APPEND should make it append instead of overwrite.

Barmar Over a year ago

I'm not familiar with SQL-Server. MySQL has a setting called max_allowed_packet, which limits the size of a single query, there may be something analogous in SQL-Server.

|

Collectives™ on Stack Overflow

How to insert the first line of a csv file in a php database?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related