Solved

PHP CSV multiple table import into MYSQL

Posted on 2011-03-17
8
824 Views
Last Modified: 2012-05-11
HI All

I need a php script to import a csv file or excel file into mysql database

Each line will be a different table for example:

Bakery, Sales, Purchases
Butchery,Sales,Purchases

Bakery is a table in databse and butchery is table

Thank you
0
Comment
Question by:TomCatEL
  • 5
  • 3
8 Comments
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35159634
What about data for the table ?
0
 

Author Comment

by:TomCatEL
ID: 35159952
Bakery has to be the table sales and purchases will be the data
0
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 500 total points
ID: 35160016
So if the input data would be in the format of
Bakery, 100, 200
(example)

then you would have to do it like: (not tested and taken from one of the last solutions)

<html>
<body>

<?php
    if(!empty($_POST)){
        if ($_FILES["file"]["error"] > 0){
          echo "Error: " . $_FILES["file"]["error"] . "<br />";
        }
        else{
          $f = file($_FILES["file"]["tmp_name"]);
          foreach($f as $key => $value){
            // cut the whitespaces from the beginning and end
            $value = trim($value);
            $array = explode(",",$value);
            
            // trim the values for the database
            array_walk($array,trim);
            $query = "insert into $array[0] values ('$array[1]','$array[2]')";
            echo"$query<br />";
            $result = mysql_query($query);
          }
        }    
    }
?>

<form action="index.php" method="post" enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file" /> 
<input type="submit" name="submit" value="Submit" />
</form>

</body>
</html>

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:TomCatEL
ID: 35166413
Will this import each line into a new table
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35167001
Assuming the lines are TABLENAME, value1, value2 - yes
0
 

Author Comment

by:TomCatEL
ID: 35168251
I get the following errors


 $f = file($_FILES["file"]["tmp_name"]);
          foreach($f as $key => $value){

Warning: file() [function.file]: Filename cannot be empty in /home/tradeben/public_html/admin/csv test.php on line 43

Warning: Invalid argument supplied for foreach() in /home/tradeben/public_html/admin/csv test.php on line 44
0
 

Author Comment

by:TomCatEL
ID: 35171446
Here is the final script that worked.Thanks

<html>
<body>

<?php

function connect_demo() {
    // These four parameters must be changed dependent on your MySQL settings
    $hostdb = 'value'; // MySQl host
    $userdb = 'value';  // MySQL username
    $passdb = 'value';  // MySQL password
    $namedb = 'value'; //Mysql Database
      

// Please uncomment the appropriate statement
    //$link = mysql_connect ("localhost:3306", "root", "");
    $link = mysql_connect ($hostdb, $userdb, $passdb);
      //$link = mysql_connect ();

    if (!$link) {
        // we should have connected, but if any of the above parameters
        // are incorrect or we can't access the DB for some reason,
        // then we will stop execution here
        die('Could not connect: ' . mysql_error());
    }

    $db_selected = mysql_select_db($namedb);
    if (!$db_selected) {
        die ('Can\'t use database : ' . mysql_error());
    }
    return $link;
}

$link = connect_demo();
$namedb=connect_demo();
mysql_select_db("$namedb",$link);


    if(!empty($_POST)){
        if ($_FILES["file"]["error"] > 0){
          echo "Error: " . $_FILES["file"]["error"] . "<br />";
        }
        else{
          $f = file($_FILES["file"]["tmp_name"]);
          foreach($f as $key => $value){
            // cut the whitespaces from the beginning and end
            $value = trim($value);
            $array = explode(",",$value);
           
            // trim the values for the database
            array_walk($array,trim);
            $query = "insert into $array[0] values ('$array[1]','$array[2]')";
            mysql_query($query);
                  echo"$query <br />";
           
          }
        }    
    }
?>
<form  action="<?php echo ($_SERVER['PHP_SELF']);?>"   method="post" enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file" />
<input type="submit" name="submit" value="Submit" />
</form>

</body>
</html>
0
 

Author Closing Comment

by:TomCatEL
ID: 35171448
Just one problem but resolved issue
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
length of the password hash sha1:64000 to set sql field property. 13 66
Wordpress Pagination 1 29
Extracting content from meta tag PHP MYHTML DOM 3 21
issue with DB import 1 18
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question