Form for PHP with data coming from excel spreadsheet

keithrus · 23-05-2007 11:32am #1

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

seamus · 23-05-2007 12:50pm

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.

ron_darrell · 23-05-2007 1:22pm

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

keithrus · 24-05-2007 12:45pm

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">
<html xmlns="http://www.w3.org/1999/xhtml">
<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>

Form for PHP with data coming from excel spreadsheet

Comments