CSV導入MySQL

CSV importer是一個PHP文件，它可以幫助您使用CSV所有數據文件導入MySQL數據庫。源文件csv_importer.php也可作為示例教學。

打開Dreamweaver或記事本編輯器，並創建新的PHP頁麵，並將它命名import.php 現在，在您的網頁csv_importer.php文件中包含.

include ( "csv_importer.php" );

用於連接MySQL數據庫，請寫下麵這些代碼

$conn = @mysql_connect("localhost","root","password");

現在，選擇數據庫中包含

@mysql_select_db("yourdbname",$conn);

現在，下麵編寫代碼。隻需要提供要導入數據的表名。保留所有其它代碼，如下：

//create new importer object for importing data
$c = new CSV_Importer;
//display log errors at end           
$c->log_errors = true;
//skip the very first row in CSV file         
$c->skip_top   = true;
//Type of Server (default MYSQL), you can also use this  MSSQL and PGSQL          
$c->server     = MYSQL; 
//Database Table where File will be imported         
$c->table      = "yourtablename";

現在必須根據CSV文件的模板來設置表的列：

$c->SetColumnSequence("Field1,Field2,Field3,Field4,Field5");

在這裡，csv文件引用從將導入到表導入到數據庫

$result = $c->import("Your_CSV_Name.csv",$conn);

現在，在網頁csv_importer.php文件如下。

if($result === FALSE){
    //there was some error importing data
    $c->DumpErrors();
}else
{
    //Your data imported successfully, it will print number of rows inserted.
    print "Total records inserted are $result in table $c->table";
}	

關閉MySQL連接

@mysql_close();

以下是完整代碼：

class CSV_Importer {
	var $fields=false;
	var $table=false;
	var $skip_top=false;
	var $log_errors=true;
	var $errors=Array();
	var $server=0;
	var $line_size = 0;
	
	function SetColumnSequence() {
		$argc = func_num_args(); 
		$arg1 = func_get_arg(0);
		if(!is_string($arg1) )
			die("warning - Argument to CSV_Importer::SetColumnSequence must be a string
");
		
		$this->fields = explode(",",trim(str_Replace(" ","",$arg1)));
	}
	
	function getCSVArray($csv,$skip_first=false) {
		$ret = Array();
		$f = fopen($csv,"r");
		if($skip_first)
			$first_row = fgetcsv($f,$this->line_size);
		
		while($csv_row = fgetcsv($f,$this->line_size)) {
			if(count($csv_row)==1 and trim($csv_row[0]) == "") #empty row
				continue;
			
			foreach($csv_row as $ke => $va) {
				$va = str_replace('"',"\"",trim($va));
				//$va = str_replace("
"," ",$va);
				$csv_row[$ke] = $va;//str_replace("
"," ",$va);
			}
			$ret []= $csv_row;
			
		}
		fclose($f);
		return $ret;
	}		
	
	function import($filename,$conn=false) {
		if(!is_array($this->fields))
			die("warning - Set fields first using CSV_Importer::SetColumnSequence
");
	
		if(!is_string($this->table))
			die("warning - Set table first, CSV_Importer->table ="tablename";
");
			
			
		$rows = $this->getCSVArray($filename,$this->skip_top);
		
		$fields = Array();
		$skips = Array();
		$x=0;
		foreach($this->fields as $field) {
			if(trim($field)=="")
				$skips []= $x;	
			else
				$fields []= "`$field`";
			$x++;
		}
		
		$this->errors = Array();
		$x=1;
		foreach($rows as $row) {
			if(count($row)==0) continue;
			
			if(!empty($skips)) {
				foreach($skips as $index)
					unset($row[$index]);
			}
			if(empty($row)) continue;
			
			if(count($row) < count($fields))	$fields = array_slice($fields,0,count($row));
			if(count($fields) < count($row))	$row = array_slice($row,0,count($fields));
			
			$nfields = implode(",",$fields);
			
			$row = """.implode("","",$row).""";
			$query = "INSERT INTO `$this->table` ($nfields) values($row)";
			switch($this->server) {
				case 0: $qFunc = "mysql_query"; break;
				case 1: $qFunc = "mssql_query"; break;
				case 2: $qFunc = "pg_query"; break;
			}
			
			$r = @$qFunc($query,$conn);
			if(!$r and $this->log_errors) 
				$this->errors []= "Row#$x: ($query)". mysql_error();
			
			$x++;
		}
		if(!empty($this->errors))
			return FALSE;
		
		return $x;
	}
	
	function DumpErrors() { echo implode("
",$this->errors); }
};

define('MYSQL',	0);		#MySQL Server
define('MSSQL',	1);		#Microsoft SQL Server
define('PGSQL',	2);		#Postgre SQL Server 	


?>

上麵的教學腳本@2014 by gitbook.net