Explanation of PHP Import and Export CSV File

First, we prepare the mysql data table, assuming that there is a table student recording students' information in the project, and id, name, sex and age recording students' names, genders, ages 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;  

We also need an html interactive page with import forms and export buttons.

<form id="addform" action="do.php?action=import" method="post" enctype="multipart/form-data">  

    <p> Please select the CSV Documents: <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>  

After selecting the local csv file, click Import and submit to do. php? action=import processing, and clicking the export button requests the address do. php? action=export for data export processing.

1. Import CSV
do. php needs to process the import and export processes according to the parameters from get. The structure of php is as follows:

include_once ("connect.php"); // Connect to a database    

$action = $_GET['action'];   

if ($action == 'import') // Import CSV   

{   

    // Import processing    

}elseif($action=='export') // Export CSV   

{   

    // Export processing     

}  

Import CSV processing flow: verify the validity of csv file (ignored in this article)- > Open read-in and parse fields in an csv file- > Loop to get the values of each field- > Bulk Add to Datasheet- > Done.

if ($action == 'import') { // Import CSV   

    $filename = $_FILES['file']['tmp_name'];   

    if(emptyempty ($filename))   

    {   

        echo ' Please select the CSV Files! ';   

        exit;   

    }   

    $handle = fopen($filename, 'r');   

    $result = input_csv($handle); // Analyse csv   

    $len_result = count($result);   

    if($len_result==0)   

    {   

        echo ' There is no data! ';   

        exit;   

    }   

    for($i = 1; $i < $len_result; $i++) // Loop to get 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 1 Comma    

    fclose($handle); // Close the pointer    

    $query = mysql_query("insert into student (name,sex,age) values $data_values"); // Batch insert into data table    

    if($query)   

    {   

        echo ' Import successful! ';   

    }else{   

        echo ' Import failed! ';   

    }   

}  

Note that csv is easily handled by the fgetcsv function that comes with php, which reads 1 line from the file pointer and parses the CSV field. The following function 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;   

}  

In addition, when importing into the database, we use batch insertion instead of 1-bar insertion, so we need a little processing when building SQL statement, as shown in the code.

2. Export CSV

We know that the csv file is a plain text file composed of comma separators. You can open it with excel, and the effect is the same as that of xls table.
Export CSV processing flow: read student information table- > Cyclic record builds comma-separated field information- > Set header information- > Export file (download) to local

...   

}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"; // Separated by citation commas    

    }   

    $filename = date('Ymd').'.csv'; // Set the file name    

    export_csv($filename,$str); // Export    

}  

To export the data locally, that is, below, 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('Cache-Control:must-revalidate,post-check=0,pre-check=0');   

    header('Expires:0');   

    header('Pragma:public');   

    echo $data;   

} 

Pay attention to the process of import and export, because we use the system 1UTF-8 coding, we must remember to transcode when encountering Chinese character 1, otherwise there may be Chinese garbled codes.
Ok, this article explains this. Later, I will introduce PHP combined with mysql to import and export excel, and xml to import and export. Please pay attention.