Below is the code to import csv file to MySQL .
It allows you to specify the delimiter in this csv file, whether it is a comma, a tab etc. It also allows you to chose the line separator.
It also permits you to include an empty field at the beginning of each row, which is usually an auto increment integer primary key.
This script is useful mainly if you don't have or you are a command prompt guy.
Just make sure the table is already created before trying to dump the data.
Kindly post your comments if you got any bug report.
/* Created By :
Mayurika BhattCreated On : 25th Oct, 2007
Functionality : Add News User.
*/
@ob_start();
@session_start();
//include dbconnection and query class
include_once('include/userfunction.php');
include('include/dbconfig.php');
//Database connection
$dbconnect = new DbConnect(HOST,USER,PASS,DATABASE);
$dbconnect->open();
$databasetable = NEWSUSER;
$fieldseparator = ",";
$lineseparator = "\n";
//change the name of the file you want
$csvfile = 'filename';
/********************************************************************************************/
/* Would you like to add an empty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************************************************************************/
$addauto = 0;
/********************************************************************************************/
if(!file_exists($csvfile)) {
$msg = "File not found. Make sure you specified the correct path.
";
}
$file = fopen($csvfile,"r");
if(!$file) {
$msg = "Error opening data file.
";
}
$size = filesize($csvfile);
if(!$size) {
$msg = "File is empty.
";
}
$csvcontent = fread($file,$size);
fclose($file);
$lines = 0;
$queries = "";
$inserted = 0;
$linearray = array();
foreach(split($lineseparator,$csvcontent) as $line) {
$lines++;
$line = trim($line," \t");
$line = str_replace("\r","",$line);
/************************************************************************************************************
This line escapes the special character. remove it if entries are already escaped in the csv file
************************************************************************************************************/
$line = str_replace("'","\'",$line);
/***********************************************************************************************************/
$linearray = explode($fieldseparator,$line);
if($lines == 1)
{
//first line as the name of the fields
$first = explode($fieldseparator,$line);
}
if($lines !=1)
{
$linemysql = implode("','",$linearray);
if($addauto)
$query = "insert into $databasetable ($user) values('','$linemysql');";
else
$query = "insert into $databasetable ($user) values('$linemysql');";
$queries .= $query . "\n";
$dbquery = new DbQuery($query);
$resinsert = $dbquery->insertquery();
if($resinsert)
{
$inserted ++;
}
}
}
$rec = $lines-1;
if($inserted == $rec)
{
$msg = "Found a total of $rec records in this csv file.
Data inserted successfully";
header('location:addnewsuser.php?msg='.urlencode($msg));
exit;
}
else
{
$msg .= " Data not inserted successfully";
header('location:addnewsuser.php?msg='.urlencode($msg));
exit;
}
?>
