Detailed explanation of PHP import and export CSV files, _PHP tutorial

Detailed explanation of importing and exporting CSV files with PHP,

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.

Copy code The code is as follows:

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.

Copy code The code is as follows:

Please select the CSV file to import:
class="btn" value="Import CSV">

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:

Copy code The code is as follows:

include_once ("connect.php"); //Connect to the database
$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.

Copy code The code is as follows:

if ($action == 'import') { //Import CSV
$filename = $_FILES['file']['tmp_name'];
If(emptyempty ($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 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.

Copy code

The code is as follows: 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

Copy code

The code is as follows:

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

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

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

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.

Okay, that’s it for this article. I will also have articles later on how to import and export excel using PHP combined with mysql, as well as import and export xml, so stay tuned.

http://www.bkjia.com/PHPjc/904933.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/904933.htmlTechArticleDetailed explanation of PHP import and export of CSV files. We first prepare a mysql data table. Assume that there is a table recording student information in the project. student, and has id, name, sex, and age to record the student's name and gender respectively...