DOWNLOADDEMO

In this tutorial we will learn how to “Import Excel Sheet Data in MySQL Database using PHP. When we develop some database linked website where we have to enter data in database, sometime we have to upload bulk data in database. If we insert data one by one it will be very difficult and time consuming. Here You will learn how to insert bulk of data in database from excel sheet in a couple of minutes.
This script will import data from .csv file, so save your excel sheet in .csv file. The code is very simple we have two file here

  1. index.php
  2. connection.php

connection.php contain our MySQL connection detail that is localhost, username, password and obviously database name.

$hostname = "localhost";
$username = "root";
$password = "";
$database = "test";


$conn = mysql_connect("$hostname","$username","$password") or die(mysql_error());
mysql_select_db("$database", $conn);

index.php, it contain simple form to let user to upload file and some PHP code to insert that data in our MySQL database. So lets have a view on our index.php file

(You may also read: Export MySQL Table Data into Excel Sheet Format in PHP)

HTML form:

<form name="import" method="post" enctype="multipart/form-data">
    	<input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
</form>

Please make sure that you have added enctype=”multipart/form-data” attribute in your form tag. So it can handle file uploading.

Here is our PHP code:

if(isset($_POST["submit"]))
{
	$file = $_FILES['file']['tmp_name'];
	$handle = fopen($file, "r");
	$c = 0;
	while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
	{
		$name = $filesop[0];
		$email = $filesop[1];
		
		$sql = mysql_query("INSERT INTO csv (name, email) VALUES ('$name','$email')");
	}
	
		if($sql){
			echo "You database has imported successfully";
		}else{
			echo "Sorry! There is some problem.";
		}
}

In my case I have a database “test” and a table “csv”, csv table have three fields id, name and email. You can use your existing database and configure your query in above code and the important thing to note is that when you are making .csv file make it according to your MySQL database pattern. For example if you have name first in your query then make first column of excel sheet for name and up-to soon.

OK there is one question that how we can get the column address of excel sheet or .csv file. We are getting our data in form of an array. Then obviously we should have to treat it like an array (if you don’t know about array please read it at php.net).  So it will work like this.

$filesop[number_of_column];

$filesop is our array name and number _of_column refers to our address of column of excel sheet. And array always start from 0 it means that the address of 1st column of excel sheet is 0 here.

DOWNLOADDEMO

(You may also read: Export MySQL Table Data into Excel Sheet Format in PHP)

RELATED ARTICLESMORE FROM AUTHOR

133 COMMENTS

  1. I have a little problem. My first row if csv file have headers. I don’t want to insert that row in database. How can I do that?

    Regards.

  5. when uploading excel file, the magical characters are appearing to replace the array.. how do i handle this?

  7. when uploading excel file the special characters are inserted in the table
    for example
    ÐÏࡱá>þÿ …
    8X@”·Ú… 8¼ªCalibri18¼�…
    etc….

  9. I have an error says “The filename is not readable” when I upload the file and run the coding above

  11. @lisam. that error is because of file permission, the folder that you are uploading your file in, please make sure its permission are 755, if not please change and try.
    other reason to encounter that problem is your file is not found in the directory.


  17. while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
    {
    $name = $filesop[0];
    $email = $filesop[1];

    $sql = mysql_query("INSERT INTO csv (name, email) VALUES ('$name','$email')");
    $c = $c + 1;
    }

    The code under while loop show each column in your excel sheet.

  18. Dear Hafiz Please help me
    Fatal error: Maximum execution time of 30 seconds exceeded in D:\Learning\PHP\xampp\htdocs\gcrmaterial\prosesimport1.php on line 33

  22. ( ! ) Notice: Undefined offset: 1 in C:\wamp\www\abjt\excell\index.php on line 76
    Call Stack
    # Time Memory Function Location
    1 0.0015 373592 {main}( ) ..\index.php:0

  23. while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
    {
    $name = $filesop[0];
    $email = $filesop[1];

    $sql = mysql_query("INSERT INTO csv (name, email) VALUES ('$name','$email')");
    $c = $c + 1;
    }

    The code under while loop show each column in your excel sheet.

  24. sir the exact data is not uploading into database…only 1 and 0 is inserting in the place of name and email columns…can you provide me a solution

  26. Sir, i have an error here. please help. Cannot break/continue 1 level in C:\xampp\htdocs\terrypalmer_frontend_v2.0\pages\import_category.php on line 82
    line 82 code : if($row == 1){ $row++; continue; }

  28. hey Hafiz, how to read large no. of column data. i mean i have an excel file that holds like 112 columns. i want to input this file and read the content.. till column z $data->sheets[$i][cells][$j][26]; is done.. i assume for column AA i would have to do $data->sheets[$i][cells][$j][27];

    any suggestions are helpful.

  29. Hello Hafiz
    I have database p_reg (ida,idr,tgl,real,pokok,jasa) and excel a_reg.csv(idr,real,pokok,jasa)
    but my code always say “Sorry, there is some problem”
    what happen!!!

  30. the excel_reader2 is just providing you data from the Excel file #zeal. once you have the data in php variables, its upto you how and where you want to insert it.
    Yes it is possible.
    $query_string = sprintf(“INSERT INTO tbl_name (col1, col2,col3) values (%s,%s,%s)”, $var1, $var2, $var3);
    $query_string2 = sprintf(“INSERT INTO tbl_name2 (col1, col2,col3) values (%s,%s,%s)”, $var4,$var5,$var6);

    and so on and so forth.
    i hope you understood this

  32. when i am getting following error
    Notice: Undefined offset: 1 in C:\wamp\www\ImportFromExcel\index.php on line 76
    Call Stack
    # Time Memory Function Location
    1 0.0010 148088 {main}( ) ..\index.php:0

  33. Focus on this code, line 73 – 80. Moreover if you have any problem you can send screenshot of error your facing and and file you are trying to upload as well as your database. We will try our best to provide you solution as per your requirement. You can email us at abdullah@eggslab.net

    while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
    {
    $name = $filesop[0];
    $email = $filesop[1];

    $sql = mysql_query("INSERT INTO csv (name, email) VALUES ('$name','$email')");
    $c = $c + 1;
    }

  43. heading also inserted even after including
    if($row == 1){ $row++; continue; }
    $num = count($filesop);
    $row = 1;

    these lines.
    Please help me.

  48. hey, i tried your code and it’s worked fine except if i put in the excel values in other language (not in English) do you have solution for me?
    thx

  50. Sir, thank you for this tutorial. May I ask if there is any I could use a .csv file that is divided by columns instead of commas?

  51. Hi Abdullah Majid I have a question. What happen if the file csv have 1 row empty, example
    first, second and third row have data, but the 4 is empty and the row number 5 have data.

    how can i validate if the row is not empty? and omit that row.

  58. Hello. I am having an issue trying to import my csv, i have the lines:

    $codigo = $filesop[0];
    $termo = $filesop[1];
    $dataivig = $filesop[2];
    $datafvig = $filesop[3];
    $datafimp = $filesop[4];

    For the first one i get no errors, it stores correctly on the database, but for the others i get undefined offset: 1, 2, 3 and 4.Can you help me with this? Nice code btw, grats!

  61. I have uploaded a .csv file and but it insert 25 records in database “ÞL1µbma]ßüÒué‚ñtÍðÁ(gZë×[WvrúÀÔ2®×ëu{µœž`ßM­” TYPE OF DATA.??

    Whats wrong?

  63. i m having problem
    Notice: Undefined offset: 4 in C:\xampp\htdocs\prj\Poultry Farm\project 3\excel.php on line 25
    how to solve it..

  66. where should i added this into the code ? if i do not want the header to be inserted into the value ?

    if($row == 1){ $row++; continue; }
    $num = count($filesop);
    and $row = 1; before while loop

  68. Hello i have a problem with my codes its showing me:
    Notice: Undefined offset: 2 in C:\wamp\www\aliyuacademy_2016\administrator\sub_master\nursery\arabic\arabic_CAscoresheet_upload.php on line 164

  71. I’m also having this weird problem. I have tried uploading a .xls and .xlsx file. but it insert 25 records in database where there is only 3 rows in the sheet. And all are “ÞL1µbma]ßüÒué‚ñtÍðÁ(gZë×[WvrúÀÔ2®×ëu{µœž`ßM­” TYPE OF DATA.??

    As you suggested before, I’ve checked the database table design, nothing wrong there. Actually now i’m not sending data to database, just echoing the sql and it comes with same result.

    “INSERT INTO csv (name, email) VALUES (‘«Ø( MÉ?peKcá<ÙÙ»`0åch¸GÙaC|U–w<üÔ€j¢É¶J@ت`õÑçŸÿ¢Í
    %T˜KháC&Ig/¬ÆÐP œ|Î×ñ»£ÈÔÀÏÝ^äö{-éÑɃ!›Îxæ4$²ŠÔ<z?G´üO¢)ó8Ÿ¡ç.t;çº9–Õå', '$1 —.@dÇVôqÇ4p$1—õJ#aqéMSaqéPƒaqéS)FÃâÒ¬R̆ť¥˜
    ‹‡n®R<^')"

    Can you help please? Thanks in advance.

  72. I want to upload data in wordpress table POST and Postmeta.
    four columns will be added in Post table and rest of the columns will be added in POSTMETA table in the form of key value pair.

    Can you please tell me, after adding data in POST table how to get the ID from the POST table and then use the same ID to add data in POSTMETA table in key value pair with corresponding ID ?

  75. hello sir, thanks for your code. mine works perfectly. I want to ask if I want to update my records from excel which already contained in my database without make duplicated data. Did this possible?

  77. load data local infile ‘http://www.apilayer.net/api/live?access_key=2dfdb2509d1cb499cdea9495ef59268d&format=1’
    into table currencies
    fields terminated by ‘:’
    optionally enclosed by ‘”‘
    escaped by ‘{‘
    escaped by ‘}’
    escaped by ‘”‘
    LINES STARTING BY ‘USD’
    ignore 7 lines
    (@col1,@col2) set countrycode=@col1,exchangerate=@col2;

    this query using php file to dump the mysql?

  78. Hello sir!

    The download links is down.
    Furthermore, the demo has errors.
    Can you fix them?

    Thank you for sharing!

  79. Hi Abdullah Majid,

    Can you tell me? What is the function of “$c = 0;”?
    Because, when i erase it, the importer works as usual.
    Thanks anyway. 😀

  82. Hi Abdullah Majid, your code is worked perfectly. Thanks 😀
    But i gave a little bit additional codes if you want INSERT excel to database without header.

    Insert this code right above while loop :
    $lineNo = 0;
    $startLine = (change with number of the row you want to start insert it);

    Then, insert this code inside the while loop and above of variable :
    $lineNo++;
    if($lineNo >= $startLine) {

    and dont forget add the “close curly bracket ( } )” right below the mysql_query

  83. When i upload in the demo page a csv with two columns and two rows, i get this message: You database has imported successfully. You have inserted 1 recoreds.
    When i try to run this on my server i see that all records exists on one mysql field.
    Any help?

  88. Dear Abdullah Majid
    My problem is rather complex. In my excel sheet within the data fields there are comma and double quotes. So, exporting the same gives an error. How to work up as I am designing a resume form where the fields can have any character type data. Kindly help.

  92. hlo,In demo file .csv file is uploaded.I use the same example code it shows Sorry! There is some problem. can u pls solve my prob.

  100. while loop can’t stop at my wanted data but continue until get ‘ null ‘ value to defined ‘ none Null ‘ variable in my database so that this error :

    Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘equipserial’ /*defined ‘ none Null ‘ variable*/ cannot be null in /opt/lampp/htdocs/HMIS/admin/insertrecemp.php:323 Stack trace: #0 /opt/lampp/htdocs/HMIS/admin/insertrecemp.php(323): PDOStatement->execute(Array) #1 {main} thrown in /opt/lampp/htdocs/HMIS/admin/insertrecemp.php on line 323

    i want to stop upload until reach empty ‘ null ‘ row ?????
    my regards

  101. thanks my problem has been solved successfully
    more thanks to my GOD
    reason of error is that i wrote ‘false’ instead of false @ while(($filesop = fgetcsv($handle, 1000, “,”)) !== false)

  102. Sir, where specifically we add this code?
    if($row == 1){ $row++; continue; }
    $num = count($filesop);
    and $row = 1; before while loop

    because i dont my first row to add to database.

    and another question, why when i upload xls file, the database only add up on first column? what should i do if i want to upload excel file (except csv)?

  105. this code is working fine for me but when i am uploading date format of mysql database it’s getting 0000-00-00, how can i upload date in mysql with excel.cvs with this (Y/m/d) format

  107. Thanks. Appreciate it. I want to comment on the following:
    “and the important thing to note is that when you are making .csv file make it according to your MySQL database pattern. For example if you have name first in your query then make first column of excel sheet for name and up-to soon.”

    It doesn’t really have to be that way. Because you can do something like this:
    $name = $filesop[5];
    $email = $filesop[13];
    $address = $filesop[0];

    So if name is in the 6th column of your CSV file, you can write
    $name = $filesop[5];

  110. No need now i have studied about html5 upload 🙂 i just have an issue that i have added columns and its not getting all the columns its just taking 2 from the start like yours.

  113. Can you update mysql with excel using this code, but switch insert to update or may duplicate key? I tried both but can’t seem to make it work.

  114. why the loop is until 512?
    how to limit the loop ?
    for example my data is just 2 rows
    but the loop is sending empty data for 500 times
    how to overcome this?

  118. I uploaded excel(.xls) to demo.
    It cannot stop to add my database. (adding blank data).
    How can I stop it?

  122. Thank u Abdullah. @ Den NaKaya “r” mean open file for reading only (I.e. u don’t want to open z file for writing). @Simon Lau fgetcsv(opened file pointer,length, delimiter string); as u see it needs a delimiter but optional. If u didn’t pass a delimiter the function uses “,” comma as a default delimiter. So if u use a CSV file there is “,” b/n each cell as a delimiter so fgetcsv metho easily differentiate each cell data. But its easier if u use already available excel file handling libraries.

  123. OK after directing to the home page how am I going to direct user to other pages to continue their session or is that going to continue automatically itself you know am a leaner

  127. when i use $_FILES[‘file’][‘tmp_name’] then give me error failed to open stream and no such file and directrory like this.

  129. For Example :I have two tabs in excel file ID and Name tabs, I also create ID and Name tables in the DB. In these two tabs, also have associated Columns and Data. Now I would like to import this excel file through file upload UI file upload using PHP function.

    How can I do for that please help me to suggest?

Leave a Reply