Website Scripts And Cloud Tutorials

PHP Read excel file and insert into mysql

Byadmin

Oct 3, 2014 ,

Last updated on February 1st, 2016 at 01:28 am

Click to rate this tutorial!
[Total: 5 Average: 2.4]

PHP Read excel file and insert into MySQL

Many webmasters face issues while reading Microsoft excel spreadsheet data and insert them into the mysql database. Here is simple script that can read the excel file data and display it on the page. User also has the ability to validate the details and then using a submit button add the data from excel to mysql using php.
The below script contains database connection details along with Excel file name. Also please make sure to download PHPExcleReader, Extract the folder and save it in the same location where you save the below script.Save the below script as excel_read_insert_mysql.php.

Also refer to excel spreadsheet related scripts posted before,
Export mysql tables to excel new version using phpxls pear spreadsheet writer
Export MySQL to CSV (Excel) using php

When you run the demo given at the bottom you can see the step by step process of

  • Reading the excel and displaying the data in a webpage.
  • Converting that data in array
  • Inserting array in to mysql
  • Make sure you change the location of the PHPExcel class files extracted accordingly. Here my path looks like

    /** PHPExcel_IOFactory */
    include 'Classes/PHPExcel/IOFactory.php';
    

    Features of the script

    It will find the highest column number and row number using the PHP Excel class

    $highestColumn = $sheet->getHighestColumn();
    $highestrow = $sheet->getHighestrow();
    

    I have a simple excel spreadsheet which has these details.

    Scott	USA	         IL
    Shane	Australia      	Melbourne
    John	India	         Bangalore
    

    We will be passing the filename in as a query string and this will make the script much more powerful.

    $filename=$_GET['filename'];
    $inputFileName = 'excel_file/'.$filename;
    

    Please find the entire code below, Make sure you update the mysql details along with other customization according to your environment.

    <?php
    $host = "localhost";
    $username = "root";
    $password = "mypassword";
    $database = "mydatabase";
    //Make your connection to database
    $con = mysql_connect($host,$username,$password);
    
    //Check your connection
    if (!$con) {
    die("Could not connect: " . mysql_error());
    }
    
    //Select your database
    $db_selected = mysql_select_db($database, $con);
    //Check your connection
    if (!$con) {
    die("Could not connect: " . mysql_error());
    }
    ?><html><head><meta http-equiv="X-UA-Compatible" content="IE=8">
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    </head>
    <body>
    <title>Spreadsheet Processing</title>
    <style type="text/css">
    body{
    margin-top: 60px;
    padding:0;
    font-family:'Droid Sans',sans-serif
    }
    div #envelope{
    width:50%;
    margin:10px 30% 10px 25%;
    background-color:#f2f4fb;
    padding:10px 0;
    border:1px solid gray;
    border-radius:10px
    }
    form{
    width:70%;
    margin:0 15%
    }
    form header{
    text-align:center;
    font-family:'Roboto Slab',serif
    }
    </style>
    <?php
    /** Include path **/
    set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
    
    /** PHPExcel_IOFactory */
    include 'Classes/PHPExcel/IOFactory.php';
    $filename=$_GET['filename'];
    $inputFileName = $filename;  // File to read
    try {
        $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($inputFileName);
    } catch(Exception $e) {
        die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
    }
    
    //  Get worksheet dimensions
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();
    $highestrow = $sheet->getHighestrow();
    $i=0;
    $myarrayname = array();
    $myarrayarea = array();
    $myarrayinfo = array();
    //  Loop through each row of the worksheet in turn
    $lastColumn = $sheet->getHighestColumn();
    $lastColumn++;
    echo "<p><b>Displaying The Spreadsheet Data</b><hr><table border=1>";
    for ($column = 'A'; $column != $lastColumn; $column++) {
    echo "<tr>";
    for($row1=1;$row1<=$highestrow;$row1++)
    {
        $cell = $sheet->getCell($column.$row1);
        $value = $cell->getFormattedValue();
        if($column == 'A')
        $myarrayname [] = "'".$value."'";
        if($column == 'B')
        $myarrayarea [] = "'".$value."'";
        if($column == 'C')
        $myarrayinfo [] = "'".$value."'";
        echo "<td>".$value."<br></td>";
    
    echo "<p>";
    
    }echo "</tr>";
    }echo "</table>";
    echo "<p><b>Converted Them In To Array</b><hr>";
    print_r($myarrayname);echo "<p>";
    print_r($myarrayarea);echo "<p>";
    print_r($myarrayinfo);
    $number_of_elements = count($myarrayinfo);
    $number_of_elements = $number_of_elements-1;
    //split the array
    if(isset($_GET['process']))
    {
    $table = "mytable";
    echo "<p><b>Inserting Them To Database</b><hr>";
    for($i=0;$i<=$number_of_elements;$i++)
    {
    $query="INSERT INTO $table(name,area,info) VALUES($myarrayname[$i],$myarrayarea[$i],$myarrayinfo[$i])";
    echo "<br>".$query."<br>";
    }
    
    echo "<hr>";}
    else{
    ?>
    <center>
    
    <h2>Please Check Whether All The Information Displayed Above Are Correct.</h2>
    <form name="process_form" action="excel_read_insert_mysql.php" method="get" onSubmit="return process_me()">
    <input type="text" name="filename" value="<?php echo $filename;?>" style="display:none">
    <input type="text" name="process" value="process" style="display:none">
    <input type="submit" value="Process This Request Now" onClick=""> <input type="button" onclick="javascript:history.go(-1)" value="Go back">
    <center>
    <?php
    }
    ?>
    Since This Is A Demo We Will Not Be Showing The Orginal MYSQL Data Transfer.
    </body>
    </html>
    

    Demo

    Click to rate this tutorial!
    [Total: 5 Average: 2.4]

    Leave a Reply

    Your email address will not be published.