CSV导入MySQL

广告位

CSV importer是一个PHP文件,它可以帮助您使用CSV所有数据文件导入MySQL数据库。源文件csv…

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 yiibai.com

贺, 贺朝

关于作者: 贺朝

为您推荐