Solved

PHP CSV multiple table import into MYSQL

Posted on 2011-03-17
8
793 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
 

Author Comment

by:TomCatEL
ID: 35166413
Will this import each line into a new table
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now