mysql Archives -

Being a PHP developer you would have been consulted Data entry person for manual uploading big CSV files into your MySQL database, he may take 2-3 days to entry the data, to be a developer it’s your responsibility to reduce the man work, fortunately PHP support easy way to upload your big excel files into your database within one click .Here am going to share you the code.

Let’s Get Started

1) Copy below class and save as parseCSV.php

This is main function to parse csv into PHP array format

<?php
class parseCSV {
 
 var $heading = true;
 var $fields = array();
 var $sort_by = null;
 var $sort_reverse = false;
 var $sort_type = null;
 var $delimiter = ',';
 var $enclosure = '"';
 var $conditions = null;
 var $offset = null;
 var $limit = null;
 var $auto_depth = 15;
 var $auto_non_chars = "a-zA-Z0-9\n\r";
 var $auto_preferred = ",;\t.:|";
 var $convert_encoding = false;
 var $input_encoding = 'ISO-8859-1';
 var $output_encoding = 'ISO-8859-1';
 var $linefeed = "\r\n";
 var $output_delimiter = ',';
 var $output_filename = 'data.csv';
 var $keep_file_data = false;
 var $file;
 var $file_data;
 var $error = 0;
 var $error_info = array();
 var $titles = array();
 var $data = array();
 
 function parseCSV ($input = null, $offset = null, $limit = null, $conditions = null) {
 if ( $offset !== null ) $this->offset = $offset;
 if ( $limit !== null ) $this->limit = $limit;
 if ( count($conditions) > 0 ) $this->conditions = $conditions;
 if ( !empty($input) ) $this->parse($input);
 }
 

 function parse ($input = null, $offset = null, $limit = null, $conditions = null) {
 if ( $input === null ) $input = $this->file;
 if ( !empty($input) ) {
 if ( $offset !== null ) $this->offset = $offset;
 if ( $limit !== null ) $this->limit = $limit;
 if ( count($conditions) > 0 ) $this->conditions = $conditions;
 if ( is_readable($input) ) {
 $this->data = $this->parse_file($input);
 } else {
 $this->file_data = &$input;
 $this->data = $this->parse_string();
 }
 if ( $this->data === false ) return false;
 }
 return true;
 }
 
 function save ($file = null, $data = array(), $append = false, $fields = array()) {
 if ( empty($file) ) $file = &$this->file;
 $mode = ( $append ) ? 'at' : 'wt' ;
 $is_php = ( preg_match('/\.php$/i', $file) ) ? true : false ;
 return $this->_wfile($file, $this->unparse($data, $fields, $append, $is_php), $mode);
 }

 function output ($filename = null, $data = array(), $fields = array(), $delimiter = null) {
 if ( empty($filename) ) $filename = $this->output_filename;
 if ( $delimiter === null ) $delimiter = $this->output_delimiter;
 $data = $this->unparse($data, $fields, null, null, $delimiter);
 if ( $filename !== null ) {
 header('Content-type: application/csv');
 header('Content-Disposition: attachment; filename="'.$filename.'"');
 echo $data;
 }
 return $data;
 }

 function encoding ($input = null, $output = null) {
 $this->convert_encoding = true;
 if ( $input !== null ) $this->input_encoding = $input;
 if ( $output !== null ) $this->output_encoding = $output;
 }
 
 function auto ($file = null, $parse = true, $search_depth = null, $preferred = null, $enclosure = null) {
 
 if ( $file === null ) $file = $this->file;
 if ( empty($search_depth) ) $search_depth = $this->auto_depth;
 if ( $enclosure === null ) $enclosure = $this->enclosure;
 
 if ( $preferred === null ) $preferred = $this->auto_preferred;
 
 if ( empty($this->file_data) ) {
 if ( $this->_check_data($file) ) {
 $data = &$this->file_data;
 } else return false;
 } else {
 $data = &$this->file_data;
 }
 
 $chars = array();
 $strlen = strlen($data);
 $enclosed = false;
 $n = 1;
 $to_end = true;
 
 
 for ( $i=0; $i < $strlen; $i++ ) {
 $ch = $data{$i};
 $nch = ( isset($data{$i+1}) ) ? $data{$i+1} : false ;
 $pch = ( isset($data{$i-1}) ) ? $data{$i-1} : false ;
 
 
 if ( $ch == $enclosure ) {
 if ( !$enclosed || $nch != $enclosure ) {
 $enclosed = ( $enclosed ) ? false : true ;
 } elseif ( $enclosed ) {
 $i++;
 }
 
 
 } elseif ( ($ch == "\n" && $pch != "\r" || $ch == "\r") && !$enclosed ) {
 if ( $n >= $search_depth ) {
 $strlen = 0;
 $to_end = false;
 } else {
 $n++;
 }
 
 
 } elseif (!$enclosed) {
 if ( !preg_match('/['.preg_quote($this->auto_non_chars, '/').']/i', $ch) ) {
 if ( !isset($chars[$ch][$n]) ) {
 $chars[$ch][$n] = 1;
 } else {
 $chars[$ch][$n]++;
 }
 }
 }
 }
 
 
 $depth = ( $to_end ) ? $n-1 : $n ;
 $filtered = array();
 foreach( $chars as $char => $value ) {
 if ( $match = $this->_check_count($char, $value, $depth, $preferred) ) {
 $filtered[$match] = $char;
 }
 }
 
 
 ksort($filtered);
 $this->delimiter = reset($filtered);
 
 
 if ( $parse ) $this->data = $this->parse_string();
 
 return $this->delimiter;
 
 }
 

 function parse_file ($file = null) {
 if ( $file === null ) $file = $this->file;
 if ( empty($this->file_data) ) $this->load_data($file);
 return ( !empty($this->file_data) ) ? $this->parse_string() : false ;
 }
 
 
 function parse_string ($data = null) {
 if ( empty($data) ) {
 if ( $this->_check_data() ) {
 $data = &$this->file_data;
 } else return false;
 }
 
 $white_spaces = str_replace($this->delimiter, '', " \t\x0B\0");
 
 $rows = array();
 $row = array();
 $row_count = 0;
 $current = '';
 $head = ( !empty($this->fields) ) ? $this->fields : array() ;
 $col = 0;
 $enclosed = false;
 $was_enclosed = false;
 $strlen = strlen($data);
 
 
 for ( $i=0; $i < $strlen; $i++ ) {
 $ch = $data{$i};
 $nch = ( isset($data{$i+1}) ) ? $data{$i+1} : false ;
 $pch = ( isset($data{$i-1}) ) ? $data{$i-1} : false ;
 
 
 if ( $ch == $this->enclosure ) {
 if ( !$enclosed ) {
 if ( ltrim($current, $white_spaces) == '' ) {
 $enclosed = true;
 $was_enclosed = true;
 } else {
 $this->error = 2;
 $error_row = count($rows) + 1;
 $error_col = $col + 1;
 if ( !isset($this->error_info[$error_row.'-'.$error_col]) ) {
 $this->error_info[$error_row.'-'.$error_col] = array(
 'type' => 2,
 'info' => 'Syntax error found on row '.$error_row.'. Non-enclosed fields can not contain double-quotes.',
 'row' => $error_row,
 'field' => $error_col,
 'field_name' => (!empty($head[$col])) ? $head[$col] : null,
 );
 }
 $current .= $ch;
 }
 } elseif ($nch == $this->enclosure) {
 $current .= $ch;
 $i++;
 } elseif ( $nch != $this->delimiter && $nch != "\r" && $nch != "\n" ) {
 for ( $x=($i+1); isset($data{$x}) && ltrim($data{$x}, $white_spaces) == ''; $x++ ) {}
 if ( $data{$x} == $this->delimiter ) {
 $enclosed = false;
 $i = $x;
 } else {
 if ( $this->error < 1 ) {
 $this->error = 1;
 }
 $error_row = count($rows) + 1;
 $error_col = $col + 1;
 if ( !isset($this->error_info[$error_row.'-'.$error_col]) ) {
 $this->error_info[$error_row.'-'.$error_col] = array(
 'type' => 1,
 'info' =>
 'Syntax error found on row '.(count($rows) + 1).'. '.
 'A single double-quote was found within an enclosed string. '.
 'Enclosed double-quotes must be escaped with a second double-quote.',
 'row' => count($rows) + 1,
 'field' => $col + 1,
 'field_name' => (!empty($head[$col])) ? $head[$col] : null,
 );
 }
 $current .= $ch;
 $enclosed = false;
 }
 } else {
 $enclosed = false;
 }
 
 
 } elseif ( ($ch == $this->delimiter || $ch == "\n" || $ch == "\r") && !$enclosed ) {
 $key = ( !empty($head[$col]) ) ? $head[$col] : $col ;
 $row[$key] = ( $was_enclosed ) ? $current : trim($current) ;
 $current = '';
 $was_enclosed = false;
 $col++;
 
 
 if ( $ch == "\n" || $ch == "\r" ) {
 if ( $this->_validate_offset($row_count) && $this->_validate_row_conditions($row, $this->conditions) ) {
 if ( $this->heading && empty($head) ) {
 $head = $row;
 } elseif ( empty($this->fields) || (!empty($this->fields) && (($this->heading && $row_count > 0) || !$this->heading)) ) {
 if ( !empty($this->sort_by) && !empty($row[$this->sort_by]) ) {
 if ( isset($rows[$row[$this->sort_by]]) ) {
 $rows[$row[$this->sort_by].'_0'] = &$rows[$row[$this->sort_by]];
 unset($rows[$row[$this->sort_by]]);
 for ( $sn=1; isset($rows[$row[$this->sort_by].'_'.$sn]); $sn++ ) {}
 $rows[$row[$this->sort_by].'_'.$sn] = $row;
 } else $rows[$row[$this->sort_by]] = $row;
 } else $rows[] = $row;
 }
 }
 $row = array();
 $col = 0;
 $row_count++;
 if ( $this->sort_by === null && $this->limit !== null && count($rows) == $this->limit ) {
 $i = $strlen;
 }
 if ( $ch == "\r" && $nch == "\n" ) $i++;
 }
 
 
 } else {
 $current .= $ch;
 }
 }
 $this->titles = $head;
 if ( !empty($this->sort_by) ) {
 $sort_type = SORT_REGULAR;
 if ( $this->sort_type == 'numeric' ) {
 $sort_type = SORT_NUMERIC;
 } elseif ( $this->sort_type == 'string' ) {
 $sort_type = SORT_STRING;
 }
 ( $this->sort_reverse ) ? krsort($rows, $sort_type) : ksort($rows, $sort_type) ;
 if ( $this->offset !== null || $this->limit !== null ) {
 $rows = array_slice($rows, ($this->offset === null ? 0 : $this->offset) , $this->limit, true);
 }
 }
 if ( !$this->keep_file_data ) {
 $this->file_data = null;
 }
 return $rows;
 }
 

 function unparse ( $data = array(), $fields = array(), $append = false , $is_php = false, $delimiter = null) {
 if ( !is_array($data) || empty($data) ) $data = &$this->data;
 if ( !is_array($fields) || empty($fields) ) $fields = &$this->titles;
 if ( $delimiter === null ) $delimiter = $this->delimiter;
 
 $string = ( $is_php ) ? "<?php header('Status: 403'); die(' '); ?>".$this->linefeed : '' ;
 $entry = array();
 
 
 if ( $this->heading && !$append && !empty($fields) ) {
 foreach( $fields as $key => $value ) {
 $entry[] = $this->_enclose_value($value);
 }
 $string .= implode($delimiter, $entry).$this->linefeed;
 $entry = array();
 }
 
 
 foreach( $data as $key => $row ) {
 foreach( $row as $field => $value ) {
 $entry[] = $this->_enclose_value($value);
 }
 $string .= implode($delimiter, $entry).$this->linefeed;
 $entry = array();
 }
 
 return $string;
 }
 

 function load_data ($input = null) {
 $data = null;
 $file = null;
 if ( $input === null ) {
 $file = $this->file;
 } elseif ( file_exists($input) ) {
 $file = $input;
 } else {
 $data = $input;
 }
 if ( !empty($data) || $data = $this->_rfile($file) ) {
 if ( $this->file != $file ) $this->file = $file;
 if ( preg_match('/\.php$/i', $file) && preg_match('/<\?.*?\?>(.*)/ims', $data, $strip) ) {
 $data = ltrim($strip[1]);
 }
 if ( $this->convert_encoding ) $data = iconv($this->input_encoding, $this->output_encoding, $data);
 if ( substr($data, -1) != "\n" ) $data .= "\n";
 $this->file_data = &$data;
 return true;
 }
 return false;
 }
 
 
 

 function _validate_row_conditions ($row = array(), $conditions = null) {
 if ( !empty($row) ) {
 if ( !empty($conditions) ) {
 $conditions = (strpos($conditions, ' OR ') !== false) ? explode(' OR ', $conditions) : array($conditions) ;
 $or = '';
 foreach( $conditions as $key => $value ) {
 if ( strpos($value, ' AND ') !== false ) {
 $value = explode(' AND ', $value);
 $and = '';
 foreach( $value as $k => $v ) {
 $and .= $this->_validate_row_condition($row, $v);
 }
 $or .= (strpos($and, '0') !== false) ? '0' : '1' ;
 } else {
 $or .= $this->_validate_row_condition($row, $value);
 }
 }
 return (strpos($or, '1') !== false) ? true : false ;
 }
 return true;
 }
 return false;
 }
 
 
 * Validate a row against a single condition
 * @param row array with values from a row
 * @param condition specified condition that the row must match 
 * @return true of false
 */
 function _validate_row_condition ($row, $condition) {
 $operators = array(
 '=', 'equals', 'is',
 '!=', 'is not',
 '<', 'is less than',
 '>', 'is greater than',
 '<=', 'is less than or equals',
 '>=', 'is greater than or equals',
 'contains',
 'does not contain',
 );
 $operators_regex = array();
 foreach( $operators as $value ) {
 $operators_regex[] = preg_quote($value, '/');
 }
 $operators_regex = implode('|', $operators_regex);
 if ( preg_match('/^(.+) ('.$operators_regex.') (.+)$/i', trim($condition), $capture) ) {
 $field = $capture[1];
 $op = $capture[2];
 $value = $capture[3];
 if ( preg_match('/^([\'\"]{1})(.*)([\'\"]{1})$/i', $value, $capture) ) {
 if ( $capture[1] == $capture[3] ) {
 $value = $capture[2];
 $value = str_replace("\\n", "\n", $value);
 $value = str_replace("\\r", "\r", $value);
 $value = str_replace("\\t", "\t", $value);
 $value = stripslashes($value);
 }
 }
 if ( array_key_exists($field, $row) ) {
 if ( ($op == '=' || $op == 'equals' || $op == 'is') && $row[$field] == $value ) {
 return '1';
 } elseif ( ($op == '!=' || $op == 'is not') && $row[$field] != $value ) {
 return '1';
 } elseif ( ($op == '<' || $op == 'is less than' ) && $row[$field] < $value ) {
 return '1';
 } elseif ( ($op == '>' || $op == 'is greater than') && $row[$field] > $value ) {
 return '1';
 } elseif ( ($op == '<=' || $op == 'is less than or equals' ) && $row[$field] <= $value ) {
 return '1';
 } elseif ( ($op == '>=' || $op == 'is greater than or equals') && $row[$field] >= $value ) {
 return '1';
 } elseif ( $op == 'contains' && preg_match('/'.preg_quote($value, '/').'/i', $row[$field]) ) {
 return '1';
 } elseif ( $op == 'does not contain' && !preg_match('/'.preg_quote($value, '/').'/i', $row[$field]) ) {
 return '1';
 } else {
 return '0';
 }
 }
 }
 return '1';
 }
 
 
 function _validate_offset ($current_row) {
 if ( $this->sort_by === null && $this->offset !== null && $current_row < $this->offset ) return false;
 return true;
 }
 

 function _enclose_value ($value = null) {
 if ( $value !== null && $value != '' ) {
 $delimiter = preg_quote($this->delimiter, '/');
 $enclosure = preg_quote($this->enclosure, '/');
 if ( preg_match("/".$delimiter."|".$enclosure."|\n|\r/i", $value) || ($value{0} == ' ' || substr($value, -1) == ' ') ) {
 $value = str_replace($this->enclosure, $this->enclosure.$this->enclosure, $value);
 $value = $this->enclosure.$value.$this->enclosure;
 }
 }
 return $value;
 }
 

 function _check_data ($file = null) {
 if ( empty($this->file_data) ) {
 if ( $file === null ) $file = $this->file;
 return $this->load_data($file);
 }
 return true;
 }
 
 
 function _check_count ($char, $array, $depth, $preferred) {
 if ( $depth == count($array) ) {
 $first = null;
 $equal = null;
 $almost = false;
 foreach( $array as $key => $value ) {
 if ( $first == null ) {
 $first = $value;
 } elseif ( $value == $first && $equal !== false) {
 $equal = true;
 } elseif ( $value == $first+1 && $equal !== false ) {
 $equal = true;
 $almost = true;
 } else {
 $equal = false;
 }
 }
 if ( $equal ) {
 $match = ( $almost ) ? 2 : 1 ;
 $pref = strpos($preferred, $char);
 $pref = ( $pref !== false ) ? str_pad($pref, 3, '0', STR_PAD_LEFT) : '999' ;
 return $pref.$match.'.'.(99999 - str_pad($first, 5, '0', STR_PAD_LEFT));
 } else return false;
 }
 }
 
 
 function _rfile ($file = null) {
 if ( is_readable($file) ) {
 if ( !($fh = fopen($file, 'r')) ) return false;
 $data = fread($fh, filesize($file));
 fclose($fh);
 return $data;
 }
 return false;
 }


 function _wfile ($file, $string = '', $mode = 'wb', $lock = 2) {
 if ( $fp = fopen($file, $mode) ) {
 flock($fp, $lock);
 $re = fwrite($fp, $string);
 $re2 = fclose($fp);
 if ( $re != false && $re2 != false ) return true;
 }
 return false;
 }
 
}
?>

2) Create a Page Name csv_upload.php

include("parseCSV.php");
if($_FILES['customer_details']['name']!='') {
	
	
	$csv_report	= $_FILES['customer_details']['name'];
	$delimiter = ',';
	
		if (preg_match('/\\.(csv)$/i', $csv_report) != 1)

		{

			header("Location: csv_upload.php?err=Please upload a CSV-report with the extension.csv");
			exit;

		}
		else { 
			$csv = new parseCSV();
			$csv->delimiter = $delimiter;
			$separator = $csv->delimiter;
			$csv->parse($_FILES['customer_details']['tmp_name']);
			
			if (!isset($separator) || $separator == "")

				{

				echo "error";
				exit();

				}
			
			
			foreach ($csv->data as $key => $row) {
				print_r($row);
                                // Here You can change accordingly
                               /*
                                $event_name=$row['event_name'];
				
				$name=$row['name'];

				$email=$row['email'];

				$mobile=$row['mobile'];
				$location=$row['location'];
				$birthday=$row['birthday'];
			
				$anniversary=$row['anniversary'];
				$customer_id = '';
				$event_id = '';
			        mysql_query("INSERT INTO customers(name,email,mobile,location,birthday,anniversary) VALUES ('".$name."', '".$email."','".$mobile."','".$location."','".$birthday."','".$anniversary."') ");
					$customer_id = mysql_insert_id();
                             */
				
				
			
			}
				
			
			
		}
	
}

<form enctype="multipart/form-data" method="post"><input name="customer_details" type="file" />
<input name="import_csv" type="submit" value="Upload Customer CSV File" /></form>

3) Open csv_upload.php and upload your files

Category: mysql

how to insert Hindi language in MySQL,now ???? is coming in DB

How to insert Hindi in MySQL database using PHP, now am getting question mark

Select all data from MySQL where varchar date greater than today ?

How to Import CSV Into MySQL Database Using PHP