Creating PHP CSV Import to MySQL – Simple Script

In the previous tutorial we created the How to export mysql to csv using php, and now as continue of this tutorial we create also on how to import csv to mysql.

A table contains a listed of data and upload form, user can be able to upload the file, and also we create a php function for the file upload handling, validation and etc.

You can view the live demo and test the upload function, the test cvs file are available in the previous tutorial How to export mysql to csv using php.

Step 1: Create a Database

First create a database named ‘import_csv’ you can change if you want, and create a table named ‘csv_data’ and fields, and insert some data.

CREATE TABLE IF NOT EXISTS `csv_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_title` varchar(255) NOT NULL,
  `product_price` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Step 2: Create Database Connection

Create file name ‘database.php’ to connect from the mysql database we have assigning the database credentials, MySQL host, user, password and database name the ‘import_csv’.

<?php 
$db = mysql_connect('localhost', 'root', ''); 	
	if(!$db) { echo mysql_error(); 
} 
$select_db = mysql_select_db('import_csv'); // db name 	
if(!$select_db) { 
	echo mysql_error(); 
} 
?>

Step 3: Creating the Page Template

Create a file name ‘index.php’ and copy the code below. In the index page, first we connect to mysql database connection and query the added sample product in the table, and upload form set the attribute enctype multipart for upload handling.

index.php

<?php require_once("database.php"); ?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Demo</title>
<link href="style/style.css" rel="stylesheet" type="text/css" media="all" />
</head>

<body>

<div id="wrap" align="center"> <!--wrap start-->

<h1>Importing Data with CSV</h1>

<table id="table_data">
    <tr>
        <td>id</td>
        <td>Product name</td>
        <td>Price</td>
    </tr>

    <?php $query = mysql_query("SELECT `id`, `product_title`, `product_price` FROM `csv_data`");                 
		while($row = mysql_fetch_array($query)) {             
	?>

    <tr>
        <td><?php echo $row['id']; ?></td>
        <td><?php echo $row['product_title']; ?></td>
        <td><?php echo $row['product_price']; ?></td>
    </tr>

    <?php } unset($row); ?>
</table>

<form action="import.php" method="post" enctype="multipart/form-data">
            <input name="cvs" type="file" />
            <input name="upload" type="submit" value="Upload CSV"/>
</form>

        <?php
            if(isset($_GET['success'])) {
                echo '<span class="msg">' . $_GET['count'] . '  Data Successfully Imported.' . '</span>';
            }
        ?>
</div> <!--wrap end-->
</body>
</html>

A simple style for the page.

style.css

body {
	font-family: Arial, Helvetica, sans-serif;
	font-size:13px;
	color:#222222;
}
h1 {
	color:#000000;
	font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;
}
p {
	margin:10px;
	padding:10px;
	color:#000000;
}
table#table_data {
  width: 325px;
}
table#table_data tr:first-child {
	font-weight:bold;
	text-transform:uppercase
}
table#table_data tr td {
    border-bottom: 1px solid #CCCCCC;
	text-align:center
}
table#table_data tr:first-child {
	text-align:center;
	border:1px solid #999
}
span.msg {
  background: url(correct.png) no-repeat right;
  display: block;
  font-style: italic;
  margin-top: 10px;
  width: 210px;
}

Step 4: The Import PHP Script

import.php

<?php require_once("database.php"); 

if(@$_POST['upload']) { 	

	$file_name 		= $_FILES['cvs']['name']; 	
	$file_type 		= $_FILES['cvs']['type']; 	
	$file_temp_loc 	= $_FILES['cvs']['tmp_name']; 	
	$file_error_msg = $_FILES['cvs']['error']; 	
	$file_size 		= $_FILES['cvs']['size']; 	/* 1. file upload handling */ 	

	if(!$file_temp_loc) { // if not file selected 		
		echo "Error: please browse for a file before clicking the upload button."; 		
		exit(); 	
	} 	

	if(!preg_match("/\.(csv)$/i", $file_name)) { // check file extension 		
		echo 'Error: your file is not CSV.'; 		
		@unlink($file_temp_loc); // remove to the temp folder 		
		exit(); 
	} 	

	if($file_size > 5242880) { // file check size
		echo "Error: you file was larger than 5 Megabytes in size.";
		exit();
	}
	
	if($file_error_msg == 1) { //
		echo "Error: an error occured while processing the file, try agian.";
		exit();
	}

	$move_file = move_uploaded_file($file_temp_loc, "upload/{$file_name}"); // temp loc, file name
	if($move_file != true) { // if not move to the temp location
		echo 'Error: File not uploaded, try again.';
		@unlink($file_temp_loc); // remove to the temp folder
		exit();
	}

	$csvFile  = 'upload/'.$file_name;
	$csvFileLength = filesize($csvFile);
	$csvSeparator = ",";
	$handle = fopen($csvFile, 'r');

	$count = '';
	while($data = fgetcsv($handle, $csvFileLength, $csvSeparator)) { // while for each row
		$count += count($data[0]); // count imported
		mysql_query("INSERT INTO `csv_data` (`product_title`, `product_price`) VALUES ( '$data[0]', '$data[1]' )");
	}

	fclose($handle);
	unlink($csvFile); // delete cvs after imported
	header('Location: index.php?success=1&count='.$count);
	exit();
}
?>

In the php import script we connect to mysql database first. If the user upload the file, the $_FILES assigns the variables, and we put in the upload validation. After the validation we put in the move uploaded file function to transferred the csv to the server, we will use the fopen function for open the file and get contents and then perform a while loop with mysql queries, after performing the query we put in the unlink to delete the cvs file in server for saving space.

If you enjoyed this article, please consider sharing it!




Leave a Reply

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