PHP implementation CSV file import and export _php tips

In the project development, many times to import the external CSV file into the database or export the data to a CSV file, then how to achieve it? This article will use PHP and combined with MySQL to achieve the CSV Format data import and export functions.
We first prepare the MySQL datasheet, assuming that the project has a record of student information on the table student, and have id,name,sex,age record the student's name, gender, age and other information.

CREATE TABLE ' student ' ( 
 ' id ' int (one) not null auto_increment, 
 ' name ' varchar ' is 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.

<form id= "AddForm" action= "Do.php?action=import" method= "post" enctype= "Multipart/form-data" > 
 <p> Please select the CSV file to import: <br/><input type= "file" name= "file" > <input type= "Submit" class= "BTN" 
 value= "import csv "> 
 <input type=" button "class=" BTN "value=" Export csv "onclick=" window.location.href= "do.php? 
 Action=export ' "></p> 
</form> 

Select a good local CSV file, click Import, submit to Do.php?action=import processing, and click the Export button to request address Do.php?action=export data export processing.

1. Import CSV
Do.php needs to process the import and export process separately according to the parameters of Get, PHP structure as follows:

Include_once ("connect.php"); Connection database 
$action = $_get[' action ']; 
if ($action = = = ' Import ')//import CSV 
{ 
 ///import processing 
}elseif ($action = = ' export ')//export CSV 
{ 
 //export Processing 
} 

Import CSV processing process: Verify CSV file legality (omitted in this article)-> opens the read in and resolves the fields in the CSV file-> loops get each field value-> the batch add to the datasheet-> done.

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 ' does not have any data! '; 
  Exit; 
 } 
 for ($i = 1; $i < $len _result $i + +)//loop gets each field value 
 { 
  $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 "); BULK INSERT Datasheet 
 if ($query) 
 { 
  echo ' import successful! '; 
 } else{ 
  echo ' Import failed! '; 
 } 

Note that PHP's fgetcsv function makes it easy to work with CSV, which allows you to read a line from the file pointer and parse the CSV field. The following function parses the CSV file field and returns it 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; 
} 

In addition, when importing into a database, we use bulk inserts instead of inserts, so when building SQL statements, we need to do a little processing, see code.

2. Export CSV
We know that a CSV file is a plain text file made up of comma-delimited characters, and you can open it in Excel with the same effect as the XLS table.
Export CSV processing flow: Read Student information table-> Circular record build comma-delimited field information-> set header information-> export file (download) to local

... 
} ElseIf ($action = = ' exports ')//export CSV 
{ 
 $result = mysql_query ("SELECT * from student ORDER by ID ASC"); 
 $str = "name, sex, 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 the 
 $filename = Date (' Ymd ') with a comma of quotations. CSV '; Set filename 
 export_csv ($filename, $STR);//Export 
} 

To export the data locally, you need to modify the header information 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; 

Note that the import and export process, because we are using a unified UTF-8 code, encountered in Chinese characters must remember to turn code, otherwise it may appear in the case of garbled characters.

The above is the entire content of this article, I hope this article describes the PHP program to help you.