Working with CSV files is very common practice, as such you'll want to add data from spreadsheets into a database, this tutorial will show you how to do just that.
For this tutorial where going to work with a sample CSV file, which has the following fields: First Name, Last Name, Email.
You can download the sample CSV file. Before we start writing the script we first need a database table to store the data.
Create the following table you can do this via phpmyadmin or run the following code in a php page (remember to connect to the database beforehand).
mysql_query("CREATE TABLE IF NOT EXISTS contacts (
recordID int(11) NOT NULL auto_increment,
firstname varchar(255) NOT NULL,
lastname varchar(255) NOT NULL,
email varchar(255) NOT NULL,
telephone varchar(255) NOT NULL,,
PRIMARY KEY ('recordID')
) ENGINE=MyISAM")or die(mysql_error());
Now we have a CSV file to work with and a database table to save the data to it's time to write the script that will do all the heavy lifting for us.
The first thing to do is connect to your database, supply the host, database username, password and the database name your going to use.
define('DB_SERVER', 'localhost');
define('DB_USER', 'database-username');
define('DB_PASSWORD', 'password');
define('DB_NAME', 'database-name');
@$conn = mysql_connect (DB_SERVER, DB_USER, DB_PASSWORD);
mysql_select_db (DB_NAME,$conn);
if(!$conn){
die( "Sorry! There seems to be a problem connecting to our database.");
}
Next we create a function to detect the file extension as we only want to allow uploads of .CSV files, This function returns the file extension for us to work with later in the script.
function get_file_extension($file_name) {
return end(explode('.',$file_name));
}
The next function fetching any errors that have been created that are stored in an array called $error, the function loops through all errors in the array, add them to a variable called $showError and then returns the errors to be used when the function is called.
function errors($error){
if (!empty($error))
{
$i = 0;
while ($i < count($error)){
$showError.= '<div class="msg-error">'.$error[$i].'</div>';
$i ++;}
return $showError;
}// close if empty errors
} // close function
Next we detect if the upload form has been submitted by checking the name of the submit field in a $_POST next a check is down to determine the file extension and if the extension is not csv an error is created, which will stop the upload and show the error to the user.
If the file is a csv file the script carries on.
if (isset($_POST['upfile'])){
if(get_file_extension($_FILES["uploaded"]["name"])!= 'csv')
{
$error[] = 'Only CSV files accepted!';
}
if (!$error){
Next a variable called $tot is created with a value of 0 this will increment on each loop to give us the total number of rows inserted into the database.
The next step is to open the uploaded file using fopen() passing it the uploaded file and set the mode, in this case 'r' for read. Then loop through all the data.
The easiest way of doing this is by making use of php's fgetcsv function. Pass the function the handle (references fopen) how many bites to read and the separated for csv's this will be a comma.
$tot = 0;
$handle = fopen($_FILES["uploaded"]["tmp_name"], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
Next we perform a for loop then you can determine what data you have by using pre tags and print_r($data) which will print out the array indexes, this is commented out as we're not using it, to see the array's index uncomment the 2 lines.
Next insert the data into a databsase, select the table to use then the fields to insert into along with their value, I've escaped the data using a function called mysql_real_escape_string() to prevent any security concerns, for each piece of data you need to specify the index in the array $data[0], $date[1]..ect
To have the query only run for the csv data and not the headers an if statemebt can help by only allowing thw query to run where the $data[0] is not equal to the contents of the header such as firstname
At the end of the loop the $tot increments
for ($c=0; $c < 1; $c++) {
//only run if the first column if not equal to firstname
if($data[0] !='firstname'){
mysql_query("INSERT INTO contacts(
firstname,
lastname,
email,
telephone
)VALUES(
'".mysql_real_escape_string($data[0])."',
'".mysql_real_escape_string($data[1])."',
'".mysql_real_escape_string($data[2])."',
'".mysql_real_escape_string($data[3])."'
)")or die(mysql_error());
}
$tot++;}
Next close the while loop and close the handle for fopen, then create a success message including the total number of rows added.
Close the if no errors if statement and if the if statement for if the form has been submitted.
}
fclose($handle);
$content.= "<div class='success' id='message'> CSV File Imported, $tot records added </div>";
}// end no error
}//close if isset upfile
Next call the errors function passing it the error array add this to variable then start forming the display area of the form, make sure the form is using enctype="multipart/form-data" and it's method is set to post, then create the upload field in the form and a submit button.
Then finally output the contents of the variable $content.
$er = errors($error);
$content.= <<<EOF
<h3>Import CSV Data</h3>
$er
<form enctype="multipart/form-data" action="" method="post">
File:<input name="uploaded" type="file" maxlength="20" /><input type="submit" name="upfile" value="Upload File">
</form>
EOF;
echo $content;
Here's the full script:
<?php
define('DB_SERVER', 'localhost');
define('DB_USER', 'database-username');
define('DB_PASSWORD', 'password');
define('DB_NAME', 'database-name');
@$conn = mysql_connect (DB_SERVER, DB_USER, DB_PASSWORD);
mysql_select_db (DB_NAME,$conn);
if(!$conn){
die( "Sorry! There seems to be a problem connecting to our database.");
}
function get_file_extension($file_name) {
return end(explode('.',$file_name));
}
function errors($error){
if (!empty($error))
{
$i = 0;
while ($i < count($error)){
$showError.= '<div class="msg-error">'.$error[$i].'</div>';
$i ++;}
return $showError;
}// close if empty errors
} // close function
if (isset($_POST['upfile'])){
// check feilds are not empty
if(get_file_extension($_FILES["uploaded"]["name"])!= 'csv')
{
$error[] = 'Only CSV files accepted!';
}
if (!$error){
$tot = 0;
$handle = fopen($_FILES["uploaded"]["tmp_name"], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
for ($c=0; $c < 1; $c++) {
//only run if the first column if not equal to firstname
if($data[0] !='firstname'){
mysql_query("INSERT INTO contacts(
firstname,
lastname,
email,
telephone
)VALUES(
'".mysql_real_escape_string($data[0])."',
'".mysql_real_escape_string($data[1])."',
'".mysql_real_escape_string($data[2])."',
'".mysql_real_escape_string($data[3])."'
)")or die(mysql_error());
}
$tot++;}
}
fclose($handle);
$content.= "<div class='success' id='message'> CSV File Imported, $tot records added </div>";
}// end no error
}//close if isset upfile
$er = errors($error);
$content.= <<<EOF
<h3>Import CSV Data</h3>
$er
<form enctype="multipart/form-data" action="" method="post">
File:<input name="uploaded" type="file" maxlength="20" /><input type="submit" name="upfile" value="Upload File">
</form>
EOF;
echo $content;
?>