Import CSV data to MySQL Using PHP
Just like my previous post on Export MySQL data to CSV file Using PHP, it is vice versa process i.e. to Import CSV data to MySQL Using PHP
So here is simple CSV to MySQL import class written in PHP.
First create "csvimport" class and define required variables class csvimport { var $separator = ','; var $field_names = false; var $dbhost = ''; // mysql database server var $dbuser = ''; // database username var $dbpass = ''; // database password var $dbname = ''; // database name var $data = ''; var $error = ''; var $result = false; }
Next add constructor function in class with two parameters database table name and CSV filename and in constructor function write MySQL database connection. Define database information in above variable list.
function __construct($tablename,$filename) { $Conn = mysql_connect($this->dbhost, $this->dbuser, $this->dbpass) or $this->error_msg("Error: Invalid MySQL Server Information"); if (!$Conn) $this->error_msg("Error: Invalid MySQL Server Information"); $DB_select = mysql_select_db($this->dbname, $Conn); if (!$DB_select) $this->error_msg("Error: Invalid MySQL Database"); if ($this->temp_path == '') $this->temp_path = sys_get_temp_dir(); $this->filename = $filename; $this->sql = $sql; $this->import(); }
Here are some required functions/methods which are used while importing CSV data in MySQL. Add this functions to above class.
//sql execution function which will return resource id function execute($sql) { if ($sql!="") { $result = mysql_query($sql) or $this->error_msg("Error: Check MySQL Query($sql)"); if ($result) return $result; else return false; } } //function to check number of record of resource id function count_check($result) { if ($result) { if (mysql_num_rows($result) > 0) return true; else return false; } } //function to fetch mysql data from resource id in associative array function recordset($result) { if ($result) { while($row = mysql_fetch_assoc($result)) $data[] = $row; } return $data; } //function to display error message function error_msg($msg) { if ($msg != '') die($msg); else return true; }
And finally the import function to get CSV content and insert it into mysql table
function import() { if (file_exists($this->filename)) { $this->data = file_get_contents($this->filename); if ($this->data != '') { $lines = explode("\n", $this->data); if (is_array($lines) && count($lines) > 0) { $insert_string = ''; $data_array = array(); if ($this->field_names == true) $start_ind = 0; else $start_ind = 1; for($i=$start_ind;$i<count($lines);$i++) { if ($lines[$i] != '') { $data_array = explode($this->separator, $lines[$i]); if (is_array($data_array) && count($data_array) > 0) { $t_str = ''; foreach($data_array as $data_row) { $t_str .= "'".$data_row."',"; } } $insert_string .= '('.trim($t_str,",").'),'; } } $insert_string = trim($insert_string,","); if ($insert_string != '') { $insert_query = "INSERT INTO ".$this->tablename." VALUES $insert_string"; $ins = $this->execute($insert_query); if ($ins) $this->error_msg("Data Inserted Successfully..."); else $this->error_msg("Problem While Inserting Data..."); } } } else { $this->error_msg("Error: No Data In CSV file"); } } else { $this->error_msg("Error: Cannot open file(".$this->filename.")"); } }
As we have created csvimport class to import CSV data in MySQL table, now below is the code by which we read csv file and insert data in MySQL table.
$filename = 'test.csv'; // CSV file to be imported to MySQL $tablename = ""; // MySQL table name in which data to be inserted new csvimport($tablename,$filename);