Import excel file to Mysql with PHP

Recently I was asked to upload a whole Excell database into an existing Mysql database. Looking online I found several responses and they had most of the answers I needed but also I had to improvise some coding. Here are the steps I took.

1 – Make the Excell file into a CSV. To avoid problems I used “;” instead of “,” as MYSQL also uses commas to separate fields on the command line.

2 – Opened the .csv file with a text editor (I used Notepad++) and did a search and replace ‘ = \’ and ” = \” to avoid problems when populating the database.

3 – Used the PHP script and guidelines from this site http://www.modwest.com/help/kb6-253.html , remember to change “\t” to “;” as the field delimiter.

4 – Save the .csv file and script on the same directory.

5 – To avoid messing up my existing database I created an empty table (csvtemp) with just anough fields for the transfer, just in case something went wrong. In my case I only needed 5 fields so f1, f2, f3, f4 , f5 all TEXT.

6 – Run script (I had problems – missed steps 1 & 2, so had to empty the table, fix script and restart)

7 – Create a script to read one table (csvtemp) and pass the information to the final database (as a preacaution I got it to first write the contents of (csvtem) to the screen and checked my script was correct, all fine so I implemented the function to populate the desired table.

This is my script, there’s probably a cleaner way of doing this so feel free to comment.

<?php
$dbConn = mysql_connect (Host, User, Password) or die (‘MySQL connect failed. ‘ . mysql_error());
mysql_select_db(database_name) or die(‘Cannot select database. ‘ . mysql_error());

function db_admin($query) {
global $dbConn;
$result = mysql_query($query) or die(mysql_error());
return $result;
}

$today = date(‘Y-m-d H:m:s’);
$query = “SELECT * FROM csvtemp”;
$result = db_admin($query);

$num=mysql_numrows($result);

$i=0;
while ($i < $num) {
$f1=mysql_result($result,$i,”f1″);
$f2=mysql_result($result,$i,”f2″);
$f3=mysql_result($result,$i,”f3″);
$f4=mysql_result($result,$i,”f4″);
$f5=mysql_result($result,$i,”f5″);
$f6=mysql_result($result,$i,”f6″);
$f7=mysql_result($result,$i,”f7″);
$f8=mysql_result($result,$i,”f8″);
$f9=mysql_result($result,$i,”f9″);

# this could be useful if you have ‘ in your database
$f1 = str_replace(“‘”, “\’”, $f1);
$f2 = str_replace(“‘”, “\’”, $f2);
$f3 = str_replace(“‘”, “\’”, $f3);
$f4 = str_replace(“‘”, “\’”, $f4);
$f5 = str_replace(“‘”, “\’”, $f5);
$f6 = str_replace(“‘”, “\’”, $f6);
$f7 = str_replace(“‘”, “\’”, $f7);
$f8 = str_replace(“‘”, “\’”, $f8);
$f9 = str_replace(“‘”, “\’”, $f9);

#insert into desired table which in this case is called PRODUCTS
$query2 = “INSERT INTO PRODUCT VALUES (‘ ‘, ‘Books’, ‘ ‘, ‘ ‘, ‘$f4’, ‘$f5’, ‘$f2’, ‘$f3’, ‘$f7’, ‘$f1’, ‘$f6’, ‘$f8’, ‘ ‘, ‘$f9’, ‘0’, ‘ ‘, ‘ ‘, ‘$today’, ‘$today’)”;
db_admin($query2);

$i++;

}

?>