Mysql import csv file using database query LOAD DATA INFILE

From Code Trash
Jump to: navigation, search

Table Structure

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `fname` varchar(255) NOT NULL,
  `lname` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(100) NOT NULL,
  `state` varchar(5) NOT NULL,
  `country` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `zip` varchar(6) NOT NULL,
  `xdate` datetime NOT NULL,
  `activestatus` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

PHP and SQL script

here is the query and the php script of it 

	function import()
	{
		if( isset($_FILES['csvfile']) &&  ($_FILES['csvfile']['error']*1)!=0)
		{
			$this->session->set_flashdata('err','Unable to upload file');
			redirect(comurl.$this->cwhich);
		}
		$name = $_FILES['csvfile']['tmp_name'];
		$name = str_replace("\\","\\\\",$name);
		$cid=$this->coid;
		$query = "LOAD DATA INFILE '$name' IGNORE INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (email,fname,lname,password,address,city,state,zip,phone) set id=null, cid=$cid, xdate=now(), password='' ";
 
		$ret = $this->db->query($query);
		if(!$ret)$msg = addslashes($this->db->_error_message());
		else $msg = $this->db->affected_rows()." Users imported successfully";
		$this->session->set_flashdata('err',$msg);
		redirect(comurl.$this->cwhich);
	}


Sample CSV

Email,First Name,Last Name,Password,Address,City,State,Zip,Phone
a@a.com,aaa,zzz,Password,Update Address,Update,CA,99999,999-999-9999
b@b.com,bbb,xxx,Password,Update Address,Update,CA,99999,999-999-9999
c@c.net,ccc,yyy,Password,Update Address,Update,CA,99999,999-999-9999

Reference

Retrieved from "http://vikku.info/code-trash/index.php?title=Mysql_import_csv_file_using_database_query_LOAD_DATA_INFILE&oldid=1017"