How to Import CSV File Data into Mysql

over 8 years ago

0
0
Positive Vote
0
Negative Vote
0
Save Favourite
0
0
1
0
698

Comment on it

Hi,

In my previous post I have explained about How to Export MySQL table data to CSV File using PHP.
Now in this tutorial I am going to explain how to import CSV data file into mysql. Suppose that we have a CSV file with 3-4 entry, we can easily upload data into mysql manually, If we have a large CSV(Comma Separated Value) data file sheet and we want to upload CSV file data manually in the mysql table then it will be a very time consuming task.

I am going to explain in very easy way how to import CSV file data easily and without consuming time, You just need to follow all steps

Step 1- (Connection.php)

First of all we need to create database and connect . Below is PHP database connection file.

<?php
$db = mysql_connect("Database", "username", "password") or die("Could not connect.");
if(!$db) 
    die("no db");
if(!mysql_select_db("Databasename",$db))
    die("No database selected.");
?>

Step 2-

Now as you know that we want to upload CSV data file for that first we need to create upload page. i.e (upload.php).

Then use the below code to upload your CSV data to upload.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Upload page</title>
<style type="text/css">
body {
    background: #E3F4FC;
    font: normal 14px/30px Helvetica, Arial, sans-serif;
    color: #2b2b2b;
}
a {
    color:#898989;
    font-size:14px;
    font-weight:bold;
    text-decoration:none;
}
a:hover {
    color:#CC0033;
}
h1 {
    font: bold 14px Helvetica, Arial, sans-serif;
    color: #CC0033;
}
h2 {
    font: bold 14px Helvetica, Arial, sans-serif;
    color: #898989;
}
#container {
    background: #CCC;
    margin: 100px auto;
    width: 945px;
}
#form           {padding: 20px 150px;}
#form input     {margin-bottom: 20px;}
</style>
</head>
<body>
<div id="container">
<div id="form">
<?php
include "connection.php"; //Connect to Database
$deleterecords = "TRUNCATE TABLE tablename"; //empty the table of its current records
mysql_query($deleterecords);
//Upload File
if (isset($_POST['submit'])) {
    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
        echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
        echo "<h2>Displaying contents:</h2>";
        readfile($_FILES['filename']['tmp_name']);
    }
    //Import uploaded file to Database
    $handle = fopen($_FILES['filename']['tmp_name'], "r");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $import="INSERT into tablename(item1,item2,item3,item4,item5) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
        mysql_query($import) or die(mysql_error());
    }
    fclose($handle);
    print "Import done";
    //view upload form
}else {
    print "Upload new csv by browsing to file and clicking on Upload<br />\n";
    print "<form enctype='multipart/form-data' action='upload.php' method='post'>";
    print "File name to import:<br />\n";
    print "<input size='50' type='file' name='filename'><br />\n";
    print "<input type='submit' name='submit' value='Upload'></form>";
}
 ?>
</div>
</div>
</body>
</html>

How to Import CSV File Data into Mysql

0 Comment(s)

Comment on it

Unable to start Java!! Mr. Nerd figure out why..

Positive Votes

Negative Votes

Delete Comment

Post Projects

Manage Company