PHP method to import and export CSV files to mysql database

Due to work needs, we need to export the data in mysql into csv files, and then use PHP to import the csv files into the mysql database using the data provided by the merchants. Now I will introduce to you how PHP imports and exports CSV files to MySQL database program code, friends who need to know can refer to it.

The following is just a simple example

We first prepare the mysql data table. Assume that the project has a table student that records student information, and has id, name, sex, age to record the student's name, gender, age and other information respectively.

The code is as follows

Copy code

代码如下	复制代码
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;

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;

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

The code is as follows

Copy code

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"); //Connect to the database

The code is as follows	Copy code
$action = $_GET['action'] ; if ($action == 'import') { //Import CSV //Import processing } elseif ($action=='export') { //Export CSV //Export processing }

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.

The code is as follows

Copy code

代码如下

复制代码

if ($action == 'import') { //导入CSV
    $filename = $_FILES['file']['tmp_name'];
    if (empty ($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 '导入失败！';
}
}

if ($action == 'import') { //Import CSV
$filename = $_FILES['file']['tmp_name'];
If (empty ($filename)) {
echo 'Please select the CSV file to import! ';
exit;
}
$handle = fopen($filename, 'r');
$result = input_csv($handle); //Parse csv
$len_result = count($result);
If($len_result==0){
echo 'No data! ';
exit;
}
for ($i = 1; $i < $len_result; $i++) { //Loop to obtain the values of each field
        $name = iconv('gb2312', 'utf-8', $result[$i][0]); //Chinese transcoding
         $sex = iconv('gb2312', 'utf-8', $result[$i][1]);
$age = $result[$i][2];
         $data_values .= "('$name','$sex','$age'),";
}
$data_values = substr($data_values,0,-1); //Remove the last comma
fclose($handle); //Close the pointer
$query = mysql_query("insert into student (name,sex,age) values $data_values");//Batch insert into the data table
If($query){
echo 'Import successful! ';
}else{
echo 'Import failed! ';
}
}

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; }

The code is as follows	Copy code
<🎜>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; } <🎜>

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 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 student information table-> loop records to construct comma-separated field information-> set header information-> export file (download) to local

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

The code is as follows

代码如下

复制代码

...
} 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 code

代码如下	复制代码
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; }

...
} elseif ($action=='export') { //Export CSV
$result = mysql_query("select * from student order by id asc");
$str = "Name, gender, age n";
$str = iconv('utf-8','gb2312',$str);
While($row=mysql_fetch_array($result)){
         $name = iconv('utf-8','gb2312',$row['name']); //Chinese transcoding
         $sex = iconv('utf-8','gb2312',$row['sex']);
            $str .= $name.",".$sex.",".$row['age']."n"; //Separate with quotation commas
}
$filename = date('Ymd').'.csv'; //Set the file name
export_csv($filename,$str); //Export
}

The code is as follows

Copy code

http://www.bkjia.com/PHPjc/630713.html

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630713.htmlTechArticleBecause of work needs, we need to export the data in mysql into a csv file, and then we need the data provided by the merchant Use php to import csv files into mysql database. Let me introduce it to you...

PHP method to import and export CSV files to mysql database_PHP tutorial