Import CSV File using PHP

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 : Parth Trivedi
Created On : 11th May, 2010
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;
}
?>

Database connection Class

Sometimes i was looking for common database layer or common database class for php.I decided to make some common pages that i can reuse in every project and acts as common module to interact with database.

Put below classes in your includes folder of your project and use them.

//include dbconnection and query class
include_once('include/connection.php');
//Database connection
$dbconnect = new DbConnect(HOST,USER,PASS,DATABASE);
$dbconnect->open();

$sql = 'select * from '.TABLENAME;
//instantiate object of query class
$dbquery = new DbQuery($sql);
$result = $dbquery->query();
$fetchrow = $dbquery->fetcharray();

and so on.
If any bugs please contact me.....
/*
Created By : Parth Trivedi
Created On : 11th May,2010
Name : connection.php
Functionality : Connection and query class.
*/

Class DbConnect
{
var $host = '' ;
var $user = '';
var $password = '';
var $database = '';
var $persistent = false;
var $conn;

var $error_reporting = false;

/*constructor function this will run when we call the class */

function DbConnect ($host1, $user1, $password1,$database1, $error_reporting=true, $persistent=false) {


//pass the hostname, user, password, database names here if static
$this->host = $host1;
$this->user = $user1;
$this->password = $password1;
$this->database = $database1;
$this->persistent = $persistent;
$this->error_reporting = $error_reporting;
}

function open(){
if ($this->persistent) {
$func = 'mysql_pconnect';
} else {
$func = 'mysql_connect';
}

/* Connect to the MySQl Server */

$this->conn = $func($this->host, $this->user, $this->password);
if (!$this->conn) {
return false;
}
/* Select the requested DB */

if (@!mysql_select_db($this->database, $this->conn)) {
return false;
}
return true;
}

/*close the connection */

function close() {
return (@mysql_close($this->conn));
}

/* report error if error_reporting set to true */

function error() {
if ($this->error_reporting) {
return (mysql_error()) ;
}

}
}
/* Class to perform query*/
class DbQuery extends DbConnect
{
var $result = '';
var $sql;
function DbQuery($sql1)
{
$this->sql = $sql1;
}

function query() {

return $this->result = mysql_query($this->sql);
//return($this->result != false);
}

function affectedrows() {
return(@mysql_affected_rows($this->conn));
}

function numrows() {
return(@mysql_num_rows($this->result));
}
function fetchobject() {
return(@mysql_fetch_object($this->result, MYSQL_ASSOC));
}
function fetcharray() {
return(@mysql_fetch_array($this->result));
}

function fetchassoc() {
return(@mysql_fetch_assoc($this->result));
}

function freeresult() {
return(@mysql_free_result($this->result));
}

}