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.
89 Comments on Import CSV/Excel data into MYSQL database via PHP
-
i want to import excel data with PHPcode and save it in database pleas send me proper code for that
Thanx
-
Thank you so much. Very useful code. Works very well for CSV files.
Could you please tell me the changes to be made in this code so that it can import EXCEL files as well? When I tried using the same code, some junk data is added to the database.
Thanks again. Waiting for your response.
Ajna
- Pingback: It is the digital spotlight forum
-
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
-
Thanks a lot, this is what I am looking for-)
-
Oops i was doing the same silly mistake of not adding the enctype=”multipart/form-data”.
This code is working properly
-
Thank You sir! Now I’m using this
-
Thank you for sharing your thoughts here. I really appreciate it.
-
@mahendra I have checked this script only two little mistake on this script. Just replace
to
And
to
-
@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’
-
thanx for the code
-
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
- Pingback: wso reviews
- Pingback: Photostand, image host, image hosting
- Pingback: Series Gratis Online
- Pingback: facebook profiles
- Pingback: Arvixe Review
- Pingback: Small Business SEO Service
- Pingback: cms dle
-
thanks for sharing the code sir
- Pingback: kho phim vip
-
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
-
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”)
- Pingback: email addresses
-
How can I skip the first line in a csv file using the code above?
-
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 : <!—->
-
the code is working
-
I want information about only for .xls sheets to import data into mysql database. if any one knows please help me
-
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 :
-
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
- Pingback: φωτοβολταικά κόστος
-
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.
- Pingback: trade show booth design
-
Import CSV/Excel data into ORACLE database via PHP
-
Thank you. Very important to me. I will not sleep through the night.
Leave a Reply
Sponsors
About Author
PHP Freelancer
I am PHP Freelancer, Rakshit Patel working as PHP Programmer India. I am here to share all my developing knowledge with you people. For Hire PHP Developer India, contact me.Follow on twitter: @raxit4u2
Get Free Programming Tutorials:
October 11, 2011 at 2:36 pm
idiot ho kya ekdum!!!
$_FILES[][] usekar liya and form me koi file control hai hin nahi!!!
kya hai yaar!!! Gahnta upload hogi file kya???