client excel upload & import

[neonpie]

I have a client that want to be able to update a data page on there website. No problem, but they want to be able to upload there latest data from an excel document. I’ve had a look at this doc and have noticed it has commas and single and double quotes. These always break when I try to import through phpmyadmin. However this time I will have to create this import myself as I don’t allow clients access to phpmyadmin in case they accidentally delete any data.

So is there some way, once the file is uploaded, the file can then update the database without breaking - also is it best to drop any current data when this happens.

Thanks in advance

[henry0]

You may modify to fit your needs the following:

// 1) (save an XL doc as a CSV): Select it from XL (Save as type) CSV (comma delimited)

// 2) THE FORM

<form name="profile" method="POST" action="xl_to_db.php" enctype="multipart/form-data" >

<p align ="center"><input type="hidden" name="MAX_FILE_SIZE" value="50000">
<input type="file" name="file1" value="AttachFile" device="files" accept="text/*" tabindex=18 >
<?php echo"<input type='text' name='xls' value='$csv'>"; ?>
<input type="submit" name="submit1" value="Submit" />
</p>

</form>

// 3) LANDING SCRIPT (xl_to_db.php)

<?php

function db_connect()
{
$result = @mysql_pconnect("localhost", "root", "");
if (!isset($result) && empty($result))
{echo "can't connect!"; }
if (!@mysql_select_db("YOUR DB"))
return false;
return $result;
}
$conn = db_connect();

$result = mysql_query($query);

$csv=$_POST['csv'];
$csv="$csv.csv";
$fcontents = file ($csv);
# expects the csv file to be in the same dir as this script
# we start counter at 1 to avoid first row of csv which only is there to force a correct col number
# ref to col title

for($i=1; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode(",", $line); // $arr=print_r($arr);echo"$arr";
#if your data is comma separated
# instead of tab separated,
# change the '\t' above to ','

$table="YOUR TABLE";
$sql = "insert into $table values ('".
implode("','", $arr) ."')";
mysql_query($sql);
echo $sql ."<br>\n";
if(mysql_error()) {
echo mysql_error() ."<br>\n";
}
else
{
echo"<h3>You have successfuly updated the whatever ..?<br>
<a href='#'>Go back to .... admin</a><br>
<a href='#'>Go back to main admin</a>";
}
}
?>
<?php
/*
Upload a spreadsheet file into the same directory as this script. Then
you edit this script to put in the correct table name instead of
"TABLENAME".

So long as your xls file is tab delimited and has 1 row per line with
all columns in the same order that your mysql database columns are, then
this script will pull all the data out of your XLS file and insert it
all into mysql.
*/
?>

[neonpie]

i have done a quick test.
had problems with commas in the cells. i didnt know at the time, but realised you can export as tab seperated and obviously import that into mysql. that seems to work ok, but will need to do some more testing

thanks for your help