Solved

PHP CSV multiple table import into MYSQL

Posted on 2011-03-17
8
809 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

786 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