Solved

PHP CSV multiple table import into MYSQL

Posted on 2011-03-17
8
879 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

718 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