[Technical Advancement] How to import and export CSV files with PHP

黄舟
Release: 2023-03-05 14:00:02
Original
1615 people have browsed it

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?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=&#39;do.php? 
    action=export&#39;"></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.

Import CSV

do.php needs to process the import and export processes separately according to the parameters obtained. The PHP structure is as follows:

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

Import CSV processing process: verify csv File legality (ignored in this article) ->Open to read and parse the fields in the csv file

->Loop to obtain the values ​​of each field->Add to the data table in batches->Complete.

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

Export CSV

We know that csv file is a plain text file composed of comma delimiters. You can open it with excel, and the effect is the same as xls table.

Export CSV processing flow: Read the student information table->loop records to construct comma-separated field information->set header information->Export the file (download) to local.

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

To export the data to local, 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(&#39;Cache-Control:must-revalidate,post-check=0,pre-check=0&#39;); 
    header(&#39;Expires:0&#39;); 
    header(&#39;Pragma:public&#39;); 
    echo $data; 
}
Copy after login

Pay attention to the import and export process, because we use unified UTF-8 encoding , be sure to remember to transcode when encountering Chinese characters, otherwise the Chinese characters may be garbled.

The above is [Technical Advancement] How to import and export CSV files in PHP. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!


Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template