how to save data from csv file to database using php

Question

I want to save my csv file into database Here is my code:

<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 "e2.php"; //Connect to Database

            $deleterecords = "TRUNCATE TABLE books"; //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 books (BookID,Title,Author,PublisherName,CopyrightYear) 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='index.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>

Here is e2.php file:

$db = mysql_connect("localhost","root","") or die("Could not connect.");

if(!$db)

    die("no db");

if(!mysql_select_db("books",$db))

    die("No database selected.");

Problem is how to save data in two table simultaneously, in csv file there is link given to every cell, eg: A1 is hyperlink to sheet2, i want to save sheet 2 data also like A1 is primary key.

here is book.csv sheet1 to save in "books" table.

1   Geography Namrata Harshal 01-04-14
2   Geography Namrata Harshal 02-04-14
3   Geography Namrata Harshal 03-04-14
4   Geography Namrata Harshal 04-04-14
5   Geography Namrata Harshal 05-04-14
6   Hindi   Namrata Harshal 06-04-14
7   Hindi   Namrata Harshal 07-04-14
8   Hindi   Namrata Harshal 08-04-14
9   Hindi   Namrata Harshal 09-04-14
10  Hindi   Namrata Harshal 10-04-14

here is book.csv sheet2 to save in "details" table.

Geography   Namrata Harshal 02-04-14
Geography   Namrata Harshal 03-04-14
Geography   Namrata Harshal 04-04-14
Geography   Namrata Harshal 05-04-14

Normally you can directly IMPORT CVS into the database via phpmyadmin. — underscore
– underscore, Commented Apr 1, 2014 at 10:42
Besides you shouldn't use mysql_* functions - why don't get the last insert id and insert the 2nd data set in table 2? — Petra
– Petra, Commented Apr 1, 2014 at 10:42
i want to give this feature to client , he cant use phpmyadmin. — Namrata Shrivas
– Namrata Shrivas, Commented Apr 1, 2014 at 10:57

Sahil Mittal · Accepted Answer · 2014-04-01 11:13:59Z

4

Get Data from CSV File use fgetcsv function.

$row = 1;
if (($openfile = fopen("customer.csv", "r")) !== FALSE) {
   while ($getdata = fgetcsv($openfile, 1000, ",")) {
       $total = count($getdata);
       echo "<b>Row no:-</b>$row\n";   
       echo "<b>Total fields in this row:-</b>$total\n";
       $row++;
       for ($c=0; $c < $total; $c++) {
          $csvdata = implode(";", $getdata);
          $fncsvdata = explode(";", $csvdata);
       }
       var_dump($fncsvdata);
   }
}

Here You can see your CSV file data than you want to use INSERT query for insert data.
For insert frist colum use $fncsvdata[0].
Here in fgetcsv 1000 = "Must be greater than the longest line (in characters) to be found in the CSV file".

edited Apr 1, 2014 at 11:13

Sahil Mittal

20.7k12 gold badges68 silver badges91 bronze badges

answered Apr 1, 2014 at 10:57

himansu

1,9471 gold badge14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Namrata Shrivas Over a year ago

i am saving my csv data to one table, but how to save in two table through one csv file,

himansu Over a year ago

Below var_dump() you can use mysql_query('INSERT INTO TABLE1() VALUES()'); mysql_query('INSERT INTO TABLE2() VALUES()');

himansu Over a year ago

$import="INSERT INTO books (BookID,Title,Author,PublisherName,CopyrightYear) VALUES('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')"; $import1="INSERT INTO details (Title,Author,PublisherName,CopyrightYear) VALUES('$data[1]','$data[2]','$data[3]','$data[4]')"; mysql_query($import) or die(mysql_error()); mysql_query($import1) or die(mysql_error()); Put in While loop.

Sahil Mittal · Accepted Answer · 2014-04-01 11:12:42Z

1

I would like to suggest this code-

<?php 
$tmpfile=books.csv;
$csv=fopen($tmpfile,"r");
$i=0; 
while(!feof($csv))
{
   $data=fgetcsv($csv);
   if($i>=1)
   {
      $catagory=addslashes($data[0]);
      if($i==0||$data[0]=='')
      {
         // Here Your Insert Code
      }
   }
   $i++;
}
fclose($csv);
?>

edited Apr 1, 2014 at 11:12

Sahil Mittal

20.7k12 gold badges68 silver badges91 bronze badges

answered Apr 1, 2014 at 10:51

Jahangir Ansari

611 silver badge10 bronze badges

Collectives™ on Stack Overflow

how to save data from csv file to database using php

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related