Insert a CSV file into a MySQL Database
This is PHP code to upload and parse a CSV file into a MySQL database. It uses a simple web page for upload and confirmation.
For this example, we will load some business information. We’ll only use 3 columns for simplicity. They are: Name, Address, and Phone (We are assuming that your database has a table named company_info and that this table has these same column names). Your file may look like this:
"Burger King", "1234 5th street", "(800) 555-king" "website-whisperer", "the Internet", "(701) 297-5143" "Super 8 Motel", "Everywhere", "1-800-800-8000"
First we’ll need a form from which to send the CSV file. The form needs the enctype set to “multipart/form-data” or your server may not accept it.
<form id="form1" name="form1" enctype="multipart/form-data" method="post" action=""> <input type="file" name="importcsv" id="importcsv" /> <input type="submit" name="uploadcsv" id="uploadcsv" value="Submit" /> </form>
Next we need to put the data in the database. In this example, we want to keep duplicate entries from being inserted.
<?php
if ($_POST['uploadcsv'] == "Insert"){
if ($_FILES["importcsv"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "<br />";
}
else
{
//the following is for debugging: un-comment to see what happened to the uploaded file
/*
echo "Upload: " . $_FILES["importcsv"]["name"] . "<br />";
echo "Type: " . $_FILES["importcsv"]["type"] . "<br />";
echo "Size: " . ($_FILES["importcsv"]["size"] / 1024) . " Kb<br />";
echo "Stored in: " . $_FILES["importcsv"]["tmp_name"];
*/
$count_inserts=0;
ini_set('auto_detect_line_endings',1);
$handle = fopen($_FILES["importcsv"]["tmp_name"], 'r');
while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
$name = mysql_real_escape_string($data[0]);
$address = mysql_real_escape_string($data[1]);
$phone = mysql_real_escape_string($data[2]);
//////you should connect to your database here///////
//do not import duplicates
$check_for_previous = "select * from company_info where name like '".$name."' AND address like '".$address."' and phone like '".$phone."'";
$cfp = mysql_query($check_for_previous);
$row_cfp = mysql_fetch_assoc($cfp);
$totalRows_cfp = mysql_num_rows($cfp);
if ($totalRows_cfp < 1){
$query = "INSERT INTO `company_info` (`name`, `address`, `phone`) VALUES ('{$name}', '{$address}', '{$phone}')";
mysql_query($query);
$count_inserts ++;
}
}
}
}
echo "$count_inserts were inserted";
?>