How To Import Large Excel File To MySql Database Using PHP

Question

I have to upload excel file's data to MySQL database using php. I have found the code for that but I am unable to upload large files.

Can anyone please tell me that how can I increase the max file size limit for the code mentioned in below link:

http://www.9code.in/how-to-import-excel-file-to-mysql-database-using-php/

<!DOCTYPE html>
<?php 
include 'db.php';
include 'Excel/reader.php';
function uploadFile($fieldName, $fileType, $folderName, $name = "")
{
    $flg = 0;
    $MaxID = "";
    $ext = "";
    $uploadfile = "";
    if (isset($fieldName) AND $fieldName['name'] != '')
    {
        $flg = 1;
        $allowed_filetypes = $fileType;
        // I Need to increase this..... I tried changing values but nothing happened
        $max_filesize = 1048576;     
        $filename = $fieldName['name'];
        if ($name == "")
            $MaxID = time() . time() . rand(1, 100);
        else
            $MaxID = $name;
        $ext = substr($filename, strpos($filename, '.'), strlen($filename) - 1);
        if($ext==".xlsx")
            $ext=".xls";
        if (!in_array($ext, $allowed_filetypes))
            echo "<h1>The file you attempted to upload is not allowed...</h1>";
        else if (filesize($fieldName['tmp_name']) > $max_filesize)
            echo "<h1>The file you attempted to upload is too large...</h1>";
        else 
        {
            $uploadfile = $folderName . "/" . $MaxID . $ext;
            if (move_uploaded_file($fieldName['tmp_name'], $uploadfile) == FALSE)
            {
                echo "<h1>Error in Uploading File...</h1>";
                $MaxID = "";
            }
            else
                $MaxID = $MaxID . $ext;
        }
    }
    return $MaxID;
}
if(isset($_POST['submit']))
{
    if($_FILES['csvFile']['name']!="")
    {
        $fileName=uploadFile($_FILES['excelFile'],array(".csv"),"excel_file");
        $row=0;
        if(($handle = fopen("excel/".$fileName , "r")) !== FALSE) 
        {
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
            {
                $num = count($data);
                //print_r($data);
                $query="INSERT INTO StudentData(FirstName,LastName,MobileNo,City)VALUES('".$data[0]."','".$data[1]."','".$data[2]."','".$data[3]."')";
                mysql_query($query);
            }
            fclose($handle);
        }
    }
    else if($_FILES['excelFile']['name']!="")
    {
        $fileName=uploadFile($_FILES['excelFile'],array(".xls",".xlsx"),"excel_file");
        $data = new Spreadsheet_Excel_Reader();
        $data->read('excel_file/'.$fileName);
        for($i=1;$i<=$data->sheets[0]['numRows'];$i++)
        {
            $firstname=$data->sheets[0]['cells'][$i][1];
            $lastname=$data->sheets[0]['cells'][$i][2];
            $mobile=$data->sheets[0]['cells'][$i][3];
            $city=$data->sheets[0]['cells'][$i][4];
            $query="INSERT INTO StudentData(FirstName,LastName,MobileNo,City)VALUES('".$firstname."','".$lastname."','".$mobile."','".$city."')";
            mysql_query($query);
        }
    }
}
if(isset($_POST['delete']))
{
    mysql_query("DELETE FROM StudentData");
}
?>

As many suggested here , you have to change post size and upload size from php.ini file. — amilaishere
– amilaishere, Commented Mar 24, 2015 at 5:41

truesource · Accepted Answer · 2014-05-27 05:18:01Z

2

You can use LOAD DATA command in MySQL : Read More

you have to used load data in mysql statement. This can load your large file in database.

mysqli_query($dblink, '
    LOAD DATA LOCAL INFILE "'.$file.'"
        INTO TABLE transactions
        FIELDS TERMINATED by ","
        OPTIONALLY ENCLOSED BY "\'"
        LINES TERMINATED BY "\n"
');

edited May 27, 2014 at 5:18

answered May 26, 2014 at 12:10

truesource

3973 silver badges20 bronze badges

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

7 Comments

Darshan Over a year ago

I cant use coz that much access is not given to normal user...I need to create an interface from where they can upload data from excel

truesource Over a year ago

by default upload / import size in phpmyadmin is 2MB. you can increase your upload_max_filesize = 20M , post_max_size = 20M in your apache php.ini.try it

Darshan Over a year ago

I Tried but the problem remains the same

Darshan Over a year ago

It says: The file you attempted to upload is too large... The filename excel_file/1401167648140116764829 is not readable

Darshan Over a year ago

Can you check the code here : drive.google.com/file/d/0Bz6rEtX6kxclMFRBa1U1ZkpTWlE/…

|

Blake · Accepted Answer · 2014-05-26 12:18:58Z

0

Look at these values in your php.ini

upload_max_filesize = 10M post_max_size = 10M

answered May 26, 2014 at 12:18

Blake

769 bronze badges

2 Comments

Darshan Over a year ago

in my php.ini upload_max_filesize = 32M... But i am unable to upload files more than 1MB

Blake Over a year ago

Also check your max_input time depending on the version of php

ASNAOUI Ayoub · Accepted Answer · 2014-05-26 12:32:29Z

0

You need to set the value of upload_max_filesize and post_max_size in your php.ini :

; Maximum allowed size for uploaded files.
upload_max_filesize = 40M

; Must be greater than or equal to upload_max_filesize
post_max_size = 40M

I have to add that you have to restart the server

answered May 26, 2014 at 12:32

ASNAOUI Ayoub

4722 silver badges9 bronze badges

1 Comment

Darshan Over a year ago

I did as you said but the problem remains the same.... I think the source code needs to be edited.. You can check the source file in the below link.. drive.google.com/file/d/0Bz6rEtX6kxclMFRBa1U1ZkpTWlE/…

Darshan · Accepted Answer · 2014-05-27 11:03:22Z

0

According to ASNAOUI Ayoub I made the following changes:

; Maximum allowed size for uploaded files.
upload_max_filesize = 40M

; Must be greater than or equal to upload_max_filesize
post_max_size = 40M

But Stil the Problem was same then I tried to change the

$max_filesize = 41943040

Now It perfectly works.....

Thanks Everyone for the help

answered May 27, 2014 at 11:03

Darshan

391 gold badge2 silver badges12 bronze badges

4 Comments

Md Riad Hossain Over a year ago

Do you mean upload_max_filesize = 41943040 ?

Darshan Over a year ago

No, not in php.ini I am talking about tha variable used in the above code.

Md Riad Hossain Over a year ago

I got it! You have talked about your uploadFile() function. Then if I made the above change to php.ini then I would able to upload large file. Am I right ?

Darshan Over a year ago

Yes... i am able to upload large file as of now

Collectives™ on Stack Overflow

How To Import Large Excel File To MySql Database Using PHP

4 Answers 4

7 Comments

2 Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

2 Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related