Thursday , 24 July 2014
Home » Helpful Tips and Tricks » Import CSV/Excel data into MYSQL database via PHP
Import CSV/Excel data into MYSQL database via PHP

Import CSV/Excel data into MYSQL database via PHP

Hello Friends,

Today i came across a functionality where i need to import the CSV/Excel file in to MYSQL database via PHP script. There is a special requirement from client where he can upload the CSV/Excel file in file upload field in HTML form and all data from CSV/Excel must import into MYSQL database table through PHP.

You can import data of CSV/Excel into MYSQL via PHP using fgetcsv() function along with some file handling functions. Here i would like to share that script with all of you. I hope that in future this article will be helpful to you when you need to implement this type of functionality.

 

if(isset($_POST['SUBMIT']))
{
     $fname = $_FILES['sel_file']['name'];
    
     $chk_ext = explode(".",$fname);
    
     if(strtolower($chk_ext[1]) == "csv")
     {
    
         $filename = $_FILES['sel_file']['tmp_name'];
         $handle = fopen($filename, "r");
   
         while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
         {
            $sql = "INSERT into user(name,email,phone) values('$data[0]','$data[1]','$data[2]')";
            mysql_query($sql) or die(mysql_error());
         }
   
         fclose($handle);
         echo "Successfully Imported";
     }
     else
     {
         echo "Invalid File";
     }   
}

<form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post'>

    Import File : <input type='text' name='sel_file' size='20'>
    <input type='submit' name='submit' value='submit'>

</form>

Above code will first check for valid csv file. If it is valid csv file than with the use of fopen() function , uploaded file will be opened in read mode. Now using fgetcsv() function , you will have a line by line data from csv file. Each line you will get is an array with all column values.  So now you have all data from csv file. You can play with them according to your needs. I have shown an example to insert 3 data in database table user. If your csv file contains more data than you will get in $data[0] , $data[1], $data[2],$data[3] and so on..

Thats it. Let me know your thoughts for the same. If you face any problem in this than let me know via comment or contact us form.

To know more about programming,JavaScript issues,jQuery,Expression Engine,MYSQL database and Open-source, enter your email address below. We will send you free tutorials.

Enter your email address:
 
Feel free to ask any question. Just leave your comment below and we will answer your comment with in 24 hours.

About Rakshit Patel

I am PHP Freelancer, Rakshit Patel AKA WordPress Freelancer working as PHP Programmer India. I am here to share all my developing knowledge, quick coding techniques, quick solutions to save your time and help you guys for any issue or bug fixing. If you have any PHP, WordPress, Magento related work, please contact me. I am interested for the work.

93 comments

  1. i am new to php programming . i am not getting your code . it is doing nothing . the above script is not working. i want to know whether file is need to be keep in wamp server or somewhere else and at the start of code it is not in code please help i am new to php

  2. Thanks a lot, this is what I am looking for-)

  3. Oops i was doing the same silly mistake of not adding the enctype=”multipart/form-data”.
    This code is working properly

  4. Thank You sir! Now I’m using this

  5. Thank you for sharing your thoughts here. I really appreciate it.

  6. @mahendra I have checked this script only two little mistake on this script. Just replace

    to

    And

    to

  7. @mahendra I have checked this script only two little mistake on this script. Just replace

    input type=’text’ name=’sel_file’ size=’20′
    to
    input type=”file” name=”sel_file” id=”sel_file”

    And

    input type=’submit’ name=’submit’ value=’submit’
    to
    input type=’submit’ name=’submit’ value=’SUBMIT’

  8. Thanks .iam using the code.but i have a small problem.iam getting only one record inserted in the database.Only the last record.plz help me

  9. thanks for sharing the code sir :)

  10. Thanks .iam using the code.but i have a small problem.iam getting only one record inserted in the database.Only the last record.plz help me

    please help me as soon as possibel

  11. This code won’t work if there are more than one dot in name of file.
    Try this: if(strtolower($chk_ext[substr_count($files_name,".")]) == “csv”)

  12. How can I skip the first line in a csv file using the code above?

  13. Hello boss the excel file is uploading but not showing content in to the database and the code is
    <?php
    mysql_connect("localhost", "root", "") or die(mysql_error());
    echo "Connected to MySQL”;
    mysql_select_db(“test”) or die(mysql_error());
    echo “Connected to Database”;
    ?>

    <form action='’ method=’post’ enctype=”multipart/form-data”>

    Import File : <!—->

  14. the code is working

  15. I want information about only for .xls sheets to import data into mysql database. if any one knows please help me

  16. running sucessfully but after some change, Nice and helpful tutorial, need some rework on it… here is mine

    <form action='’ method=’post’ enctype=”multipart/form-data” >

    Import File :

  17. Hey, the above script is not well formed, if the file has more then one .(dot), it will not work. Use the mime type of the file instead, this never lies. Or you could use array_pop, on the exploded array to get the last part of the array (this is the extension).

    $mime_type = $[FILES]['sel_file]['type'] or $chk_ext_array = explode(‘.’, $fname); $extension = array_pop($chk_ext_array);

    But thanks for mentioning the fgetcsv, this is what I was looking for :)

  18. After Uploading the excel sheet, it is not showing any message. I added enctype=”multipart/form-data” in the form. Also changed the ID and Value of the input type. pls help me.

  19. Import CSV/Excel data into ORACLE database via PHP

  20. Thank you. Very important to me. I will not sleep through the night.

  21. Thanks, After the mentioned change codes works very well!
    But I would like to know
    1.the meaning of the 1000 mentioned in fgetcsv($handle, 1000, “,”).
    2.what if I want to skip the first line of my csv file since it contains titles. thanks in advance.

  22. Pretty nice post. I just stumbled upon your weblog and wanted
    to say that I’ve truly enjoyed browsing your blog posts. In any case I’ll be
    subscribing in your feed and I am hoping you write again soon!

  23. Thanks for a marvelous posting! I certainly enjoyed reading it, you could be a
    great author.I will make certain to bookmark your blog
    and will often come back later in life. I want
    to encourage you to ultimately continue your great job, have a nice evening!

  24. Hudlege Oslo

    The file was uploading but the data was not showing in database. I think in this code you have not mentioned the database name anywhere.can you please check and reply me.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>