Detailed PHP Import export CSV file _php instance

We first prepare the MySQL data table, assuming that the project has a record of student information table student, and there are id,name,sex,age to record the student's name, gender, age and other information.

Copy the Code code as follows:
CREATE TABLE ' Student ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (not NULL),
' Sex ' varchar (+) not NULL,
' Age ' smallint (3) is 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 the Code code as follows:

After selecting a good local CSV file, click Import, submit to Do.php?action=import 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 process separately based on the parameters of Get, PHP structure is as follows:

Copy the Code code as follows:
Include_once ("connect.php"); Connecting to a database
$action = $_get[' action '];
if ($action = = ' import ')//import CSV
{
Import processing
}elseif ($action = = ' export ')//export CSV
{
Export Processing
}

Import CSV process: Verify CSV file legitimacy (this article ignores), open read-in and parse fields in CSV file, loop get each field value, and then bulk add to datasheet.

The

copy Code 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 ' doesn't have any data! ';
Exit;
}
for ($i = 1; $i < $len _result; $i + +)//loop get 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 ("I Nsert into student (name,sex,age) values $data _values "); Bulk INSERT data Table
if ($query)
{
echo ' Import succeeded! ';
}else{
Echo ' Import failed! ';
}
}

Note that PHP's own 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.

Copy the Code code 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 a database, we are using bulk inserts instead of inserts, so when you build the SQL statement, you need to handle it a bit, see code.

2. Export CSV

We know that a CSV file is a plain text file made up of comma separators, which you can open with Excel, with the same effect as the XLS table.
Export CSV process: Read the Student information form, looping record building comma-delimited field information, setting header information, export file (download) to local

Copy the Code code 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 a comma of quotations
}
$filename = Date (' Ymd '). CSV '; Set file name
Export_csv ($filename, $STR); Export
}

To export the data locally, the header information needs to be modified, with the following code:

Copy the Code code 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 the import and export process, because we are using a unified UTF-8 encoding, encountered in Chinese characters must remember transcoding, otherwise there may be garbled in the situation.
Well, this article explains this, I will also an article introduce PHP in conjunction with MySQL import and export Excel, as well as the import and export of XML, please pay attention.

