EbrahemSamer
2 months ago
446
3
General

How to read 5M excel file and inserted in in mysql database using PHPExcel?

Posted 2 months ago by EbrahemSamer

I am inserting excel file in database and it words fine with 2M files but from 3M to up can not be inserted it just treated like empty files I do not know why....

Here is my code...

include "classes/PHPExcel/IOFactory.php";

$table_name = filter_input(INPUT_POST, 'db_table_name', FILTER_SANITIZE_STRING);

$table_cols_names = $_POST['col_name'];
$table_cols_types = $_POST['col_type'];

$file_name = $_FILES['file']['tmp_name'];


$excelObject = PHPExcel_IOFactory::load($file_name);
$get_sheet = $excelObject->getActiveSheet()->toArray(null);


if( count($table_cols_names) != count($get_sheet[0]) )
{
    $_SESSION['new_election_error'] = "عدد الحقول غير متساويه داخل الملف";
    header("Location: /elections/?newelection=1");
    exit;
}

$query = "CREATE TABLE $table_name ( id INTEGER PRIMARY KEY AUTO_INCREMENT, ";

if(count($table_cols_names) == count($table_cols_types) ) {
    
    for($i = 0; $i < count($table_cols_names); $i++)
    {
        if($i == count($table_cols_names) - 1) {
            $query .= " " . $table_cols_names[$i] . " " . $table_cols_types[$i] . " ";
        }else {
            $query .= " " . $table_cols_names[$i] . " " . $table_cols_types[$i] . ", ";
        }
    }
} else {
    $_SESSION['new_election_error'] = "عمليه خاطئه, قم بإعاده المحاوله";
    header("Location: /elections/?newelection=1");
    exit;
}

$query .= " );";
if(! $pdo->query($query) )
{
    $_SESSION['new_election_error'] = "لم يتم انشاء الإنتخابات قم بإعاده المحاوله";
    header("Location: /elections/?newelection=1");
    exit;
}
$table_names_impleded = implode(', ', $table_cols_names);

for($l = 0; $l < floor(count($get_sheet) / 20000)+1; $l++) {
    
    $query = "INSERT INTO $table_name (id, ". $table_names_impleded .") VALUES ";
        
    for($i = ($l*20000)+1; $i < count($get_sheet); $i++)
    {   
            
        $query .= "(NULL, ";
        for($j = 0; $j < count($get_sheet[0]); $j++){
            if($j == count($get_sheet[0]) - 1) {
                $query .= "'". $get_sheet[$i][$j] ."'";
            } else {
                $query .= "'". $get_sheet[$i][$j] ."', ";
            }
        }
    
        if($i == ($l+1)*20000 || ( $i < ($l+1)*20000 && $i == count($get_sheet) - 1 ) ) {
            $query .= ");";
            $pdo->query($query);
            continue;
        } else {
            $query .= "),";
        }
        
    }
}

$_SESSION['new_election_success'] = "تم رفع البيانات بنجاح";
header("Location: /elections/?newelection=1");

any Ideas

Please sign in or create an account to participate in this conversation.