How To Import Large Excel File To MySql Database Using PHP
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");
}
?>
Answer
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"
');