Georgi Mitev's blog for scripts, tutorials and something else

Hello everybody,

today i’ll be talking about how to export / import your Excel file into you your database using the PHP and the CSV file format.
First of all we need an Excel file with our data.

Every row of the excel file should be the same format as the previous row (so that we can make our life easier), for example if we want to import a excel file with name/url addres corresponding to this name we should have our excel file in a format like this:

After u have the file in this excel format u should hit -> File -> Save as -> CSV (Comma Delimited) or more ofter its Called “Comma Separated Values”. For the comma separator use “;” if it asks you. So after you save the file in the CSV file format you should have something like this after you open the file with notepad etc.:

in this case we use “;” for comma separator.

ok lets get now to the Databse – in my case i will be using mysql, so for the sample excel file above you can use this code to create your database:

CREATE DATABASE MerchantManager;
USE MerchantManager;
CREATE TABLE IF NOT EXISTS `merchants` (
`MerchantID` int(11) NOT NULL AUTO_INCREMENT,
`MerchantTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`MerchantName` varchar(256) NOT NULL,
`MerchantURL` varchar(256) NOT NULL,
PRIMARY KEY (`MerchantID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=185 ;

after we create the database we are going to write some function in php that can import the data into our Database:

we can create now a new file – “import.php”

<?

function AddMerchant($name, $url){
$host = “localhost”;
$user = “root”;
$pass = “”; // use your mysql password
$db_name = “merchantmanager”;
$table = “merchants”;

mysqli_query(new mysqli($host, $user, $pass, $db_name), “INSERT INTO “.$table.” (`MerchantName`, `MerchantURL`) VALUES (‘$name’, ‘$url’)”);

}
?>

okz i hope it’s all clear for now, so let’s get to the php now
To export the CSV file into our database we are going to use:

“file_get_contents” function – http://bg.php.net/file_get_contents
and “explode” function – http://bg.php.net/manual/en/function.explode.php

so lets get started, after the “AddMerchant()” function that we wrote above, we will add the following code into the file “import.php”

$file = file_get_contents(“file.csv”); //we load the CSV file and put it in a variable

$rows = explode(“\n”, $file); //we brake the variable into array, using the new line as a condition

foreach($rows as $row){

$items = explode(“;”, $row); //we brake it again into another array using the “;” sign in our case
AddMerchant($items[0],$items[1]);// we get the first and the second value of the array (name and url)
echo “success”;

}

?>

Can you believe it’s that simple 3 lines of code and we managed to put the information from the excel file into the database.

if you want to get all the source code from this simple tutorial
download it here: export_csv_to_mysql.rar