Search This Blog

Saturday, October 19, 2013

IMPORTING DATA WITH CSV

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

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.
1CREATE TABLE IF NOT EXISTS `csv_data` (
2  `id` int(11) NOT NULL AUTO_INCREMENT,
3  `product_title` varchar(255) NOT NULL,
4  `product_price` varchar(255) NOT NULL,
5  PRIMARY KEY (`id`)
6);


Step 2: Create Database Connection

Create file name ‘database.php’ to connect from the database we have assigning the database credentials, MySQL host, user, password and database name the ‘import_csv’.
1<?php
2$db = mysql_connect('localhost', 'root', '');
3    if(!$db) { echo mysql_error(); }
4$select_db = mysql_select_db('import_csv'); // db name
5    if(!$select_db) { echo mysql_error(); }
6?>


Step 3: Creating the Page Template

Create a file name ‘index.php’ and copy the code below. In the index page, first require the database connection and query the added sample product in the table, and upload form set the attribute enctype multipart for upload handling.
index.php
1<?php require_once("database.php"); ?>
2<!DOCTYPE html>
3<html>
4<head>
5<meta charset="utf-8">
6<title>Importing Data with CSV | istockphp.com</title>
7<link href="style/style.css" rel="stylesheet" type="text/css" media="all" />
8</head>
9
10<body>
11    <div id="wrap" align="center"> <!--wrap start-->
12        <h1>Importing Data with CSV</h1>
13        <table id="table_data">
14            <tr>
15                <td>id</td>
16                <td>Product name</td>
17                <td>Price</td>
18            </tr>
19            <?php $query = mysql_query("SELECT `id`, `product_title`, `product_price` FROM `csv_data`");
20                while($row = mysql_fetch_array($query)) {
21            ?>
22            <tr>
23                <td><?php echo $row['id']; ?></td>
24                <td><?php echo $row['product_title']; ?></td>
25                <td><?php echo $row['product_price']; ?></td>
26            </tr>
27            <?php } unset($row); ?>
28        </table>
29        <br />
30        <form action="import.php" method="post" enctype="multipart/form-data">
31            <input name="cvs" type="file" />
32            <input name="upload" type="submit" value="Upload CSV"/>
33        </form>
34        <?php
35            if(isset($_GET['success'])) {
36                echo '<span class="msg">' . $_GET['count'] . '  Data Successfully Imported.' . '</span>';
37            }
38        ?>
39    </div> <!--wrap end-->
40</body>
41</html>
A simple style for the page.
style.css
1body {
2    font-family: Arial, Helvetica, sans-serif;
3    font-size:13px;
4    color:#222222;
5}
6h1 {
7    color:#000000;
8    font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;
9}
10p {
11    margin:10px;
12    padding:10px;
13    color:#000000;
14}
15table#table_data {
16  width: 325px;
17}
18table#table_data tr:first-child {
19    font-weight:bold;
20    text-transform:uppercase
21}
22table#table_data tr td {
23    border-bottom: 1px solid #CCCCCC;
24    text-align:center
25}
26table#table_data tr:first-child {
27    text-align:center;
28    border:1px solid #999
29}
30span.msg {
31  background: url(correct.png) no-repeat right;
32  display: block;
33  font-style: italic;
34  margin-top: 10px;
35  width: 210px;
36}

Step 4: The Import PHP Script

import.php
1<?php
2require_once("database.php");
3
4if(@$_POST['upload']) {
5
6    $file_name      = $_FILES['cvs']['name'];
7    $file_type      = $_FILES['cvs']['type'];
8    $file_temp_loc  = $_FILES['cvs']['tmp_name'];
9    $file_error_msg = $_FILES['cvs']['error'];
10    $file_size      = $_FILES['cvs']['size'];
11
12    /* 1. file upload handling */
13    if(!$file_temp_loc) { // if not file selected
14        echo "Error: please browse for a file before clicking the upload button.";
15        exit();
16    }
17    if(!preg_match("/\.(csv)$/i", $file_name)) { // check file extension
18        echo 'Error: your file is not CSV.';
19        @unlink($file_temp_loc); // remove to the temp folder
20        exit();
21    }
22    if($file_size > 5242880) { // file check size
23        echo "Error: you file was larger than 5 Megabytes in size.";
24        exit();
25    }
26    if($file_error_msg == 1) { //
27        echo "Error: an error occured while processing the file, try agian.";
28        exit();
29    }
30
31    $move_file = move_uploaded_file($file_temp_loc, "upload/{$file_name}"); // temp loc, file name
32    if($move_file != true) { // if not move to the temp location
33        echo 'Error: File not uploaded, try again.';
34        @unlink($file_temp_loc); // remove to the temp folder
35        exit();
36    }
37
38    $csvFile  = 'upload/'.$file_name;
39    $csvFileLength = filesize($csvFile);
40    $csvSeparator = ",";
41    $handle = fopen($csvFile, 'r');
42
43    $count = '';
44    while($data = fgetcsv($handle, $csvFileLength, $csvSeparator)) { // while for each row
45        $count += count($data[0]); // count imported
46        mysql_query("INSERT INTO `csv_data` (`product_title`, `product_price`) VALUES ( '$data[0]', '$data[1]' )");
47    }
48
49    fclose($handle);
50    unlink($csvFile); // delete cvs after imported
51    header('Location: index.php?success=1&count='.$count);
52    exit();
53}
54?>
In the php import script we require the database connection 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 query, after performing the query we put in the unlink to delete the cvs file in server for saving space.

Step 5: Complete

Were done, we created a php web application can be able to import data to the database using csv. Let’s have a look at what we’ve achieved:
  • We’ve set up a database.
  • We’ve create page to fetch the data.
  • We’ve create a file upload handling with validation.
  • We’ve able to count the uploaded data.
  • We’ve able to delete the cvs after imported.


Posted by at
Labels:
Location: Rajkot, Gujarat, India

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)