Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Form for PHP with data coming from excel spreadsheet

Options
  • 23-05-2007 12:32pm
    #1
    keithrus
    Registered Users Posts: 91 ✭✭


    Hi all,

    Im wondering if a PHP form can be created, that can take pasted data from an excel spreadsheet and write it to a MYSQL database.

    it would have a layout like the form of writing this thread.hah wouldnt it be gr8 to view source..... and create from there ;)

    Maybe when the user clicks submit it can go to a txt file and this can be imported to the database.....

    Any ideas would be grateful.

    Cheers


Comments

  • Options
    #2
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    Easier to try use some other format than excel. CSV would work, but it can be a bit of a pain. XML would be ideal.

    If someone wants to copy the contents of an excel file into a MYSQL database, they save the file in XML format, and then you upload that XML file using the form. PHP then parses the XML file and loads the info into the database.


  • Options
    #3
    ron_darrell
    Users Awaiting Email Confirmation Posts: 351 ✭✭ron_darrell


    Completely agree with Seamus. However, if, (and it's a big if) the CSV file is in a standardised format (i.e. fixed number of fields, comma delimited, all text enclosed in quotes and no extreneous chars) then it is possible to work with a CSV file and can be easier for not hugely computer literate people to export to (less options asked for). All that said XML is a better way to go as if the user inadvertently adds/removes a column your code will still work. CSV is definitely a pain in the butt.

    -RD


  • Options
    #4
    keithrus
    Registered Users Posts: 91 ✭✭keithrus


    all complete and not hard to do folks. The user has an formatted excel sheet for all the required fields. This is pasted into the form.

    here is my code :)

    if (isset($_POST)) {

    // array 1, every line is seperated by \n
    $eachline = explode("\n", $_POST);

    /*
    for($i=0;$i<sizeof($eachline);$i++) {
    $singleline = explode("\t", $eachline[$i]);
    print_r($singleline);
    echo "<br>=======<br>";
    }
    */
    // loop each line
    for($i = 0; $i < sizeof($eachline); $i++) {

    // array 2, every part of the line is sepearted by tabs \t
    $singleline = explode("\t", $eachline[$i]);

    //print_r($singleline);

    //for ($j =0; $j < sizeof($singleline); $j++){
    //echo "<br>/////<br>";
    //echo $singleline[$j];
    //echo "<br>/////<br>";

    // define where each single piece of data goes
    /*
    print_r($singleline[$j]);
    echo "<br>********<br>";
    */
    $deptID = $singleline[0];
    $name = $singleline[1];
    // divide the name into firstname & last name
    list ($firstname, $lastname) = explode(' ',$name);
    $ext = $singleline[2];
    $position = $singleline[3];
    $mobile = $singleline[4];
    $alt_contact = $singleline[5];
    $email = $singleline[6];
    $fax = $singleline[7];


    //}
    /*
    echo "<br>=======<br>";
    for ($j =0; $j < sizeof($singleline); $j++){
    echo $deptID;
    echo "<br>&&&&&&&&&&&&&&<br>";
    }
    */

    if ($deptID!= ''){
    $insertSQL = "INSERT INTO employee (department_id, employee_firstname, employee_surname, employee_ext, employee_position, employee_mobile, employee_contact2, employee_email, employee_fax) VALUES (" ;

    $values = "'" . $deptID ."'," .
    "'" .$firstname . "'," .
    "'" .$lastname . "'," .
    "'" .$ext ."'," .
    "'" .$position ."'," .
    "'" .$mobile ."'," .
    "'" .$alt_contact ."'," .
    "'" .$email ."'," .
    "'" .$fax .
    "')";


    $insertSQL .= $values;

    $resource= mysql_query($insertSQL,$link) or die(mysql_error());
    if (! mysql_insert_id()) {
    echo 'Impossible to insert the new record : ' . mysql_error();
    exit;
    }

    if (isset($_SERVER)) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER;
    }

    }


    }




    }


    ?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
    <html xmlns="http://www.w3.org/1999/xhtml"&gt;
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Multiple user entry form</title>
    <LINK REL="stylesheet" type="text/css" href="css/style.css" />
    <script type="text/javascript" src="images/globalpb.js"></script>
    </head>

    <body>

    <form name="insert" method="post" action="phonescript.php">
    <table cellpadding="0" cellspacing="0" border="0" width="100%">
    <tr style="background-color:#0D5C09">
    <td style="background-color:#0D5C09;padding:25px 0 0 0; font-size:34px; color:#fff"></td>
    <td style="height:40px" align="right"><img src="images/aff_logo.jpg" style="float:right"></td>
    </tr>

    </table>
    <table width="100%">
    <tr>

    <td align="left"> <h1><font color="#0033FF"> Employee data entry to to database</font></h1> </td>
    </tr>
    <tr>
    <td align="left"> <h3><font color="#000000"> Paste your data into the box below</font></h3> </td>
    </tr>
    <tr>
    <td align="left"><textarea name="txtbox" cols="100" rows="20" wrap="off" width="500" height="500" border="1"></textarea></td>
    </tr>
    </table>
    <table width="100%">
    <tr>
    <td width="40%" align="left"><input type="submit" name="submit" value="Submit" /></td>


    </tr>
    <?php echo("$message"); ?>
    </table>
    </form>
    </body>
    </html>


Advertisement