Sometimes you need to put huge excel files into a database. People say use data infile with a delimited excel file saved as .txt. To do this you have to lower security on the server which servers won't do (as I've found out over the week). Here is an alternate way to put excel files into a mysql database using good ole PHP.
Save the excel file as a CSV file. This makes all the fields seperated by comma-space (, ). Next make a form to upload the file to the server or put it there manually. I put it there with ftp and then just used a text-field in the form to type in the file name.
Here is the code to put it into the DB in correct format:
if ($form == "yes")
{
$num = 0;
// place the results of a file into the $filearray array
//$thefile is the name of the file from the form
$filename = "$thefile";
if (!($filearray = file ($filename))) {
print "Can't open file $filename";
}
else {
$link = mysql_connect("localhost","username","pass") or die("Link Failed");
mysql_select_db("database",$link);
mysql_query("delete from TABLE");
while (list ($line_number, $line_contents) = each ($filearray)) {
// replaces the commas with apostrophes for correct mysql format
$filearray[$num] = str_replace(",", "', '", "$filearray[$num]");
// the two apostrophes surrounding $filearray[$num] are for the
// first and last apostrophes in the line so its not
// value', 'value', 'value
$query = "INSERT into TABLE(column, column, column)
VALUES('$filearray[$num]')";
mysql_query($query);
$num++;
}
echo "Completed";
}
}
