PHP to import and export CSV files to MySQL database method

The following is just a simple example

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.

The code is as follows	Copy Code
CREATE TABLE ' Student ' ( ' id ' int (one) not NULL auto_increment, ' Name ' varchar not NULL, ' Sex ' varchar 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.

code is as follows

copy code

<form  id= "AddForm"  action= "Do.php?action=import"  method= "post"  enctype= "Multipart/form-data" >&NBSP     <p> Please select the CSV file to import: <br/><input type= "file"  name= File "> <input type=" submit "      class=" btn " value=" Import csv " >&NBSP     <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"); Connecting to a 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 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.

The code is as follows

Copy Code

if ($action = = ' Import ') {//import CSV $filename = $_files[' file ' [' Tmp_name ']; if (empty ($filename)) { Echo ' Please select the CSV file you want to import! '; Exit } $handle = fopen ($filename, ' R '); $result = Input_csv ($handle); Parsing CSV $len _result = count ($result); if ($len _result==0) { Echo ' doesn't have any data! '; Exit } for ($i = 1; $i < $len _result $i + +) {//loop to get each field value $name = Iconv (' gb2312 ', ' utf-8 ', $result [$i][0]); Chinese transfer Code $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.

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];          &NBSP;}&NBSP          $n ++;       &NBSP     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

The code is as follows

Copy Code

... } elseif ($action = = ' export ') {//export CSV $result = mysql_query ("SELECT * FROM student-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 transfer Code $sex = Iconv (' utf-8 ', ' gb2312 ', $row [' sex ']); $str. = $name. ",". $sex. "," $row [' age ']. " n "; Separate with quotation commas } $filename = Date (' Ymd '). CSV '; Set file name Export_csv ($filename, $STR); Export }

To export the data locally, you need to modify the header information as follows:

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

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.

PHP downloads Import and export CSV files: Http://file.111cn.net/download/2013/05/15/importCSV.rar