Saturday 24 September 2016

Import Excel/CSV file to MySQL Database Using PHP


VIDEO   Subscribe this channel

In this tutorial in going to show you how to create n application that can be used to import CSV/Excel file using PHP. To start with this project create a Database in your phpmyadmin name “exceltest” then execute this SQL query to create a new table called subject.


CREATE TABLE IF NOT EXISTS `subject` (
`SUBJ_ID` INT(11) NOT NULL AUTO_INCREMENT,
`SUBJ_CODE` VARCHAR(30) NOT NULL,
`SUBJ_DESCRIPTION` VARCHAR(255) NOT NULL,
`UNIT` INT(2) NOT NULL,
`PRE_REQUISITE` VARCHAR(30) NOT NULL DEFAULT 'None',
`COURSE_ID` INT(11) NOT NULL,
`AY` VARCHAR(30) NOT NULL,
`SEMESTER` VARCHAR(20) NOT NULL,
PRIMARY KEY (`SUBJ_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=500 ;


Then inside you htdocs or document root folder create a new folder and name it as “excelTest”. Next we need to create a PHP file named “db.php” that will hold our database connection and here’s the following code:


<?php
     $conn= mysql_connect("localhost","root","") or die("Could not connect");
     mysql_select_db("studentdb",$conn) or die("could not connect database");
?>



Next create another PHP file named “index.php”,and this php file will be the first page that will load to our browser when we access the “excelTest” folder from our web directory. And this index.php will load all the list of subject if the subject table is not empty as well as this page will allow the user to import the CSV/Excel file and upload the data to MySQL Database. and it will look like as shown below.



And here’s the code for “index.php” file:

<!DOCTYPE html>
<?php 
include 'db.php';
?>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Import Excel To Mysql Database Using PHP </title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="title" content="Hemant Vishwakarma">
<meta name="description" content="Import Excel File To MySql Database Using php">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
</head>
<body>    
    <br><br>
        <div class="container">
            <div class="row">
            <div class="col-md-12 text-center"><h1>Visit My Blog www.hemant9807.blogspot.com</h1></div>
    <br>
                <div class="col-md-3 hidden-phone"></div>
                <div class="col-md-6" id="form-login">
                    <form class="well" action="import.php" method="post" name="upload_excel" enctype="multipart/form-data">
                        <fieldset>
                            <legend>Import CSV/Excel file</legend>
                            <div class="control-group">
                                <div class="control-label">
                                    <label>CSV/Excel File:</label>
                                </div>
                                <div class="controls form-group">
                                    <input type="file" name="file" id="file" class="input-large form-control">
                                </div>
                            </div>
                            
                            <div class="control-group">
                                <div class="controls">
                                <button type="submit" id="submit" name="Import" class="btn btn-success btn-flat btn-lg pull-right button-loading" data-loading-text="Loading...">Upload</button>
                                </div>
                            </div>
                        </fieldset>
                    </form>
                </div>
                <div class="col-md-3 hidden-phone"></div>
            </div>
            
    
            <table class="table table-bordered">
                <thead>
                        <tr>
                            <th>ID</th>
                            <th>Subject</th>
                            <th>Description</th>
                            <th>Unit</th>
                            <th>Semester</th>
                            
                     
                        </tr>
                      </thead>
                <?php
                    $SQLSELECT = "SELECT * FROM subject ";
                    $result_set =  mysql_query($SQLSELECT, $conn);
                    while($row = mysql_fetch_array($result_set))
                    {
                    ?>
                        <tr>
                            <td><?php echo $row['SUBJ_ID']; ?></td>
                            <td><?php echo $row['SUBJ_CODE']; ?></td>
                            <td><?php echo $row['SUBJ_DESCRIPTION']; ?></td>
                            <td><?php echo $row['UNIT']; ?></td>
                            <td><?php echo $row['SEMESTER']; ?></td>
                        </tr>
                    <?php
                    }
                ?>
            </table>
        </div>
</body>
</html>


Next, we’re going to create another PHP file named “import.php” that will used to process the data from CSV/Excel to MySQL Database. and here’s the following code:



<?php
include 'db.php';
if(isset($_POST["Import"])){

echo $filename=$_FILES["file"]["tmp_name"];

if($_FILES["file"]["size"] > 0)
{

$file = fopen($filename, "r");
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{

//It wiil insert a row to our subject table from our csv file`
$sql = "INSERT into subject (`SUBJ_CODE`, `SUBJ_DESCRIPTION`, `UNIT`, `PRE_REQUISITE`,COURSE_ID, `AY`, `SEMESTER`)
values('$emapData[1]','$emapData[2]','$emapData[3]','$emapData[4]','$emapData[5]','$emapData[6]','$emapData[7]')";
//we are using mysql_query function. it returns a resource on true else False on error
$result = mysql_query( $sql, $conn );
if(! $result )
{
echo "<script type=\"text/javascript\">
alert(\"Invalid File:Please Upload CSV File.\");
window.location = \"index.php\"
</script>";
}
}
fclose($file);
//throws a message if data successfully imported to mysql database from excel file
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"index.php\"
</script>";
//close of connection
mysql_close($conn);
}
}
?> 
Import Excel/CSV file to MySQL Database Using PHP
Posted by at

27 comments:

  1. Htech1 December 2016 at 21:04

    hi my name is hammad and ia ma php developer also . i have a erro rin my php file .
    the error is the file i create for upload excel data to mysql using php is working on localhost on wamp server but it's not working on my onlinw hosting server any soltion please tell me
    Thanks in advance

    ReplyDelete
    Replies
    1. Unknown2 December 2016 at 11:49

      Hi Hammad

      Download the source code

      Link - https://drive.google.com/file/d/0B0xTe-b-qqoEVmpnREhkUFY2NEk/view?usp=sharing

      Delete
  2. 0ghin17 December 2016 at 12:09

    what if i have a header and a footer sir at my excel or csv file thanks

    ReplyDelete
  3. Unknown24 January 2017 at 14:05

    Nice work, thanks man

    ReplyDelete
  4. Unknown24 January 2017 at 14:05

    Nice work, thanks man

    ReplyDelete
  5. daniel5 February 2017 at 11:40

    thanks your the best

    ReplyDelete
  6. aepikman7 February 2017 at 02:14

    Thank you very much.
    I have a question, empty rows isn't shown as NULL. Is there any way to shown them as NULL?
    Thank you again.

    ReplyDelete
  7. Arulkumar9 February 2017 at 07:09

    Nice work bro.

    ReplyDelete
  8. Unknown9 February 2017 at 12:38

    Hi hemant,
    Hi hemant while I am uploading .CSV file the following error shows in import.php page

    "Please upload CSV file"

    Pls help to sort this out

    ReplyDelete
  9. Unknown10 February 2017 at 07:46

    Hi hemant , I have to upload the excel file and display it as gridview before storing the file in database
    Can you give some solutions.


    And iam very thankful for your kind help

    ReplyDelete
  10. Iron Butterfly18 February 2017 at 15:53

    Hi,This is niya
    Blank datas are inserting into database,is there any connection in between database column names and excell columns

    ReplyDelete
    Replies
    1. Unknown18 February 2017 at 16:08

      Click here to link download this file = https://drive.google.com/file/d/0B0xTe-b-qqoEVmpnREhkUFY2NEk/view?usp=sharing

      Delete
  11. Iron Butterfly18 February 2017 at 15:56

    hi,
    when echo my datas ,it displayed as symbols and blank datas entering to database table.Please send a solution asap

    ReplyDelete
    Replies
    1. Unknown18 February 2017 at 16:08

      Click here to link download this file = https://drive.google.com/file/d/0B0xTe-b-qqoEVmpnREhkUFY2NEk/view?usp=sharing

      Delete
  12. Unknown5 April 2017 at 12:04

    Join this fb group- https://www.facebook.com/groups/allitdevelopers/

    ReplyDelete
  13. Raijin8 May 2017 at 08:58

    Hi sir can I see your CSV file? It would really help me. Thanks

    ReplyDelete
  14. Raijin8 May 2017 at 09:03

    Can I look at your CSV file? It would really be a big help on my ongoing project. Thanks!

    ReplyDelete
  15. Aneesh Dalvi17 May 2017 at 14:56

    Hi Hemant, great work on the blog.
    I tried the same steps as mentioned, the outcome is as expected, but there are a few minor issues I am facing:
    1. "Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in D:\xampp\htdocs\ExcelUpload\db.php on line 2."
    I get this warning whenever I run the code, and it is visible on the webpage.

    2.The first(header) row of the excel file is also getting added to the sql table as a record.
    Please help. Thanks in advance.

    ReplyDelete
  16. Unknown19 May 2017 at 13:05

    Hi Sir, it does work for me when i have a few rows of data in excel, but when i upload the original csv file with 28k+ of rows data, the system is stuck in calling the import.php. Please help. TIA.

    ReplyDelete
  17. Anonymous21 May 2017 at 08:55

    Hi
    can you tell me why ID start with 500

    ReplyDelete
  18. Anonymous27 September 2017 at 11:32

    Hello sir,
    Will you help me to import csv file into database using codeigniter.

    ReplyDelete
    Replies
    1. Unknown1 October 2017 at 13:22

      https://drive.google.com/file/d/0B0xTe-b-qqoEVmpnREhkUFY2NEk/view?usp=sharing

      Delete
  19. Unknown21 February 2018 at 13:23

    thank u hemant nice share

    ReplyDelete
  20. Anonymous2 April 2018 at 10:29

    thnk x

    ReplyDelete
  21. Unknown27 September 2018 at 15:01

    problem invalid file but my file is csv what is the problem?

    ReplyDelete
  22. Unknown27 September 2018 at 15:06

    cant upload csv file but my file is csv what is the problem?

    ReplyDelete
  23. Hemant26 November 2019 at 20:50

    NZeTA for New Zealand
    On August 2019, the Government of New Zealand implemented the NZeTA to facilitate obtaining an authorization to visit the country for Tourism and Transit purposes. The new online process is valid for 2 years and allows multiple entries to New Zealand.

    NZeTa can be easily obtained online, simplifying the process to enter New Zealand by offering travelers the option to apply directly online for a travel authorization.

    The eTA for New Zealand grants its holder multiple entries to the country during its 2-year validity. Holders of the NZeTA are generally allowed to stay in the country for stays of up to 90 days from their date of arrival in New Zealand. Passports must have a minimum validity of at least three (3) months from the expected date of departure from New Zealand.

    Visitors traveling to New Zealand for short-term stays can apply to have a NZeTA to travel to and within the country. To be able to obtain a valid eTA travel authority to visit New Zealand, travelers should complete an NZeTA online application. The online New Zealand eTA application is straightforward and simple to complete. To get the New Zealand eTA, citizens of eligible countries are required to carefully fill out an online application form.

    ReplyDelete

Subscribe to: Post Comments (Atom)