Export simple excel data into MySQL using PHP

Question

My client got a excel file with the following structure

name     |     email
----------------------------
Name     |   email here
Name     |   email here
Name     |   email here
Name     |   email here
Name     |   email here
Name     |   email here

I would likes to make a MySQL database table according to this pattern and save the data into MySQL.

I am wonder how to do this. Also there is an option needed

We have to check that if that corresponding user had a correct email address, ie of the form @ .

Can we check the data as a loop while importing ?

Also how to convert this data to MySQL ?

As @Asad said before, first convert to CSV and then use mysql to import the file into the database. — GregD
– GregD, Commented Nov 5, 2012 at 11:54
as @Asad, i would definetly save excel sheet as csv first, then parse and validate it with PHP and make one multi-insert query from it — Tom
– Tom, Commented Nov 5, 2012 at 11:58

Sohail Ahmed · Accepted Answer · 2012-11-05 11:56:03Z

10

Save this excel file as csv and run the following code with add your changings

$source = fopen('email.csv', 'r') or die("Problem open file");
    while (($data = fgetcsv($source, 1000, ",")) !== FALSE)
    {
        $name = $data[0];
        $email = $data[1];


        mysql_query("INSERT INTO `table` (`name`,`email`) VALUES ('".$name."','".$email."') ");


    }
    fclose($source);

answered Nov 5, 2012 at 11:56

Sohail Ahmed

1,66714 silver badges23 bronze badges

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

2 Comments

Jestin Over a year ago

With this code, I am able to get only the first row of the file.

reformed Over a year ago

This code is open to SQL injection.

slash28cu · Accepted Answer · 2012-11-05 11:53:24Z

3

There is a Iibrary named PHPExcel. With this library you can easily parse any excel file. Or you can export your file as csv and will be easier for you. php has native functions to handle csv files. You can use fgetcsv() or str_getcsv().

answered Nov 5, 2012 at 11:53

slash28cu

1,63412 silver badges23 bronze badges

Comments

Bajrang · Accepted Answer · 2012-11-05 12:03:17Z

2

Goto this link and download the php class that will read the excel file and return the array. This array will hold the all data written in excel file.

php excel reader

Its free...

You can also see the demo there.

I am already using it. Its definitely good.

Any other help on this you can freely ask to me.

edited Nov 5, 2012 at 12:03

answered Nov 5, 2012 at 11:55

Bajrang

8,6795 gold badges29 silver badges40 bronze badges

2 Comments

ramesh Over a year ago

is there any file size limitation ?

Mark Baker Over a year ago

Except the limitation of PHP memory

SagarPPanchal · Accepted Answer · 2013-05-11 11:55:20Z

<table>
                    <form enctype="multipart/form-data" action="" method="post">
                      <input type="hidden" name="MAX_FILE_SIZE" value="2000000" />
                            <tr>
                            <td><h5><b>Select Standared</b></h5></td>
                            <td><select name="chap_sel_std" id="chap_sel_std">
                                                        <option>Select Standared</option>
                                                <?php
                                                    $exe_sel_std = mysql_query("SELECT * from s_standared");
                                                    while($r_sel_std = mysql_fetch_array($exe_sel_std)){
                                                        $sel_stdid = $r_sel_std['std_id'];
                                                        $sel_std = $r_sel_std['std'];?>

                                                        <option value="<?php echo $sel_stdid; ?>"><?php echo $sel_std;?></option>
                                                        <?php } ?>
                                </select></td>
                            </tr>
                            <tr>
                                <td><h5><b>Select Font</b></h5></td>
                                <td><select name="sel_f_gn_que">
                                    <option>Select Font</option>
                                        <?php
                                            $xf = mysql_query("SELECT * from s_font");
                                            while($rquef = mysql_fetch_array($xf)){
                                                $f_id = $rquef['f_id'];
                                                $f_name = $rquef['f_name'];  ?>
                                    <option value="<?php echo $f_id; ?>"><?php echo $f_name; }?>  </option>
                                </select></td>
                            </tr>
                            <tr>
                                <td><h5><b>Upload Question<h5><b></td>
                                <td>
                                    <input type="file" name="file" id="file" class="btn">
                                </td>
                            </tr>
                            <tr>
                                <td></td>
                                <td colspan="2"><input type="submit" class="btn btn-green big" name="add_que" value="Add Questions"></td>
                                <td><input type="submit" name="saveandexit" class="" value="Finish" onclick="close();"></td>
                            </tr>
                    </form>
                    </table>
                    </div>                   

    <?php

            $data = array();

    //$db =& DB::connect("mysql://root@localhost/names", array());
    //if (PEAR::isError($db)) { die($db->getMessage()); }
      //quetype    difficulty    standard    subject    chap    que    marks

    function add_person($quetype,$dif, $subject,$chap_name,$que,$marks)
    {
     global $data, $db;

     //$sth = $db->prepare( "INSERT INTO names VALUES( 0, ?, ?, ?, ? )" );
    // $db->execute( $sth, array( $first, $middle, $last, $email ) );

     $data []= array(
       'quetype' => $quetype, 
       'difficulty' => $dif,
       'subject' => $subject,
       'chap' => $chap_name,
       'que' => $que,
       //'ans' => $ans,
       'marks' => $marks

     );
    }

    if(!isset($_FILES['file']['tmp_name'])){
        echo "";
    }elseif($_FILES['file']['tmp_name'])
    {
     $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );
     $rows = $dom->getElementsByTagName( 'Row' );
     $first_row = true;
     foreach ($rows as $row)
     {
       if ( !$first_row )
       {
         $quetype = "";
         $dif = "";
         $subject = "";
         $chap_name = "";
         $que = "";
         //$ans = "";
         $marks = "";

         $index = 1;
         $cells = $row->getElementsByTagName( 'Cell' );
         foreach( $cells as $cell )
         {
           $ind = $cell->getAttribute( 'Index' );
           if ( $ind != null ) $index = $ind;

           if ( $index == 1 ) $quetype = $cell->nodeValue;
           if ( $index == 2 ) $dif = $cell->nodeValue;
           if ( $index == 4 ) $subject = $cell->nodeValue;
           if ( $index == 6 ) $chap_name = $cell->nodeValue;
           if ( $index == 8) $que = $cell->nodeValue;
           //if ( $index == 9) $ans = $cell->nodeValue;
           if ( $index == 9) $marks = $cell->nodeValue;

           $index += 1;
         }
         add_person($quetype,$dif, $subject,$chap_name,$que,$marks);

         if(isset($_POST['add_que'])){    

                 $chap_sel_std = $_POST['chap_sel_std'];
                 echo $simquefnt = $_POST['sel_f_gn_que'];

                    //que_id    quetype_id    chap_id    sub_id    std_id    que    dif_id    marks    que_cdate
             //$chap_sel_std = $_POST['chap_sel_std']; //que_id    quetype_id    chap_id    sub_id    std_id    que    dif_id    marks    que_cdate
             mysql_query("INSERT INTO 
                          s_question
                          VALUES (null,'$quetype','$chap_name','$subject','$chap_sel_std','$que','NO IMAGE','$dif','$marks','$simquefnt','$current')");                                                        
    //         header("location:../admin/quetionaris.php#tabs-que"); 
         echo "Successfully Added";
          }
       }
       $first_row = false;
     }
    }
    ?>

Collectives™ on Stack Overflow

Export simple excel data into MySQL using PHP

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related