PHP implements CSV file import and export, _PHP tutorial-PHP Tutorial-php.cn

PHP implements CSV file import and export, _PHP tutorial

WBOY

Release： 2016-07-12 09:06:52

Original

1763 people have browsed it

PHP implements CSV file import and export.

During project development, it is often necessary to import external CSV files into the database or export data as CSV files. So what should be done specifically? What about implementation? This article will use PHP combined with mysql to implement the import and export functions of CSV format data.
We first prepare the mysql data table. Assume that there is a table student in the project that records student information, and has id, name, sex, age to record the student's name, gender, age and other information respectively.

CREATE TABLE `student` ( 
 `id` int(11) NOT NULL auto_increment, 
 `name` varchar(50) NOT NULL, 
 `sex` varchar(10) NOT NULL, 
 `age` smallint(3) NOT NULL default '0', 
 PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Copy after login

We also need an html interactive page to place the import form and export button.

<form id="addform" action="do.php&#63;action=import" method="post" enctype="multipart/form-data"> 
 <p>请选择要导入的CSV文件：<br/><input type="file" name="file"> <input type="submit" 
 class="btn" value="导入CSV"> 
 <input type="button" class="btn" value="导出CSV" onclick="window.location.href='do.php&#63; 
 action=export'"></p> 
</form>

Copy after login

After selecting the local csv file, click Import and submit it to do.php?action=import for processing, and click the Export button to request the address do.php?action=export for data export processing.

1. Import CSV
do.php needs to handle the import and export processes respectively according to the parameters obtained. The PHP structure is as follows:

include_once ("connect.php"); //连接数据库 
$action = $_GET['action']; 
if ($action == 'import') //导入CSV 
{ 
 //导入处理 
}elseif($action=='export') //导出CSV 
{ 
 //导出处理 
}

Copy after login

Import CSV processing process: Verify the legality of the csv file (ignored in this article) -> Open the fields in the csv file that are read and parsed -> Loop to obtain the values of each field -> Add to the data table in batches -> ;Finish.

if ($action == 'import') { //导入CSV 
 $filename = $_FILES['file']['tmp_name']; 
 if(emptyempty ($filename)) 
 { 
  echo '请选择要导入的CSV文件！'; 
  exit; 
 } 
 $handle = fopen($filename, 'r'); 
 $result = input_csv($handle); //解析csv 
 $len_result = count($result); 
 if($len_result==0) 
 { 
  echo '没有任何数据！'; 
  exit; 
 } 
 for($i = 1; $i < $len_result; $i++) //循环获取各字段值 
 { 
  $name = iconv('gb2312', 'utf-8', $result[$i][0]); //中文转码 
  $sex = iconv('gb2312', 'utf-8', $result[$i][1]); 
  $age = $result[$i][2]; 
  $data_values .= "('$name','$sex','$age'),"; 
 } 
 $data_values = substr($data_values,0,-1); //去掉最后一个逗号 
 fclose($handle); //关闭指针 
 $query = mysql_query("insert into student (name,sex,age) values $data_values"); //批量插入数据表中 
 if($query) 
 { 
  echo '导入成功！'; 
 }else{ 
  echo '导入失败！'; 
 } 
}

Copy after login

Note that PHP’s own fgetcsv function can easily handle CSV. Use this function to read a line from the file pointer and parse the CSV field. The function below parses the csv file fields and returns them as an array.

function input_csv($handle) 
{ 
 $out = array (); 
 $n = 0; 
 while ($data = fgetcsv($handle, 10000)) 
 { 
  $num = count($data); 
  for ($i = 0; $i < $num; $i++) 
  { 
   $out[$n][$i] = $data[$i]; 
  } 
  $n++; 
 } 
 return $out; 
}

Copy after login

In addition, when importing into the database, we use batch insertion instead of inserting one by one, so when constructing the SQL statement, we need to do a little processing, see the code.

2. Export CSV
We know that a csv file is a plain text file composed of comma delimiters. You can open it with excel and the effect is the same as an xls table.
Export CSV processing flow: read student information table -> loop records to construct comma-separated field information -> set header information -> export file (download) to local

... 
}elseif ($action=='export') //导出CSV 
{ 
 $result = mysql_query("select * from student order by id asc"); 
 $str = "姓名,性别,年龄\n"; 
 $str = iconv('utf-8','gb2312',$str); 
 while($row=mysql_fetch_array($result)) 
 { 
  $name = iconv('utf-8','gb2312',$row['name']); //中文转码 
  $sex = iconv('utf-8','gb2312',$row['sex']); 
  $str .= $name.",".$sex.",".$row['age']."\n"; //用引文逗号分开 
 } 
 $filename = date('Ymd').'.csv'; //设置文件名 
 export_csv($filename,$str); //导出 
}

Copy after login

To export data to the local computer, you need to modify the header information. The code is as follows:

function export_csv($filename,$data) 
{ 
 header("Content-type:text/csv"); 
 header("Content-Disposition:attachment;filename=".$filename); 
 header('Cache-Control:must-revalidate,post-check=0,pre-check=0'); 
 header('Expires:0'); 
 header('Pragma:public'); 
 echo $data; 
}

Copy after login

Please note that during the import and export process, because we use unified UTF-8 encoding, you must remember to transcode when encountering Chinese characters, otherwise garbled Chinese characters may occur.

The above is the entire content of this article. I hope this article will be helpful to everyone’s PHP programming design.