PHP Import/export CSV file

PHP Import/export CSV file

1. Introduction

Project development, many times to import the external CSV file into the database or export the data as a CSV file, then how to implement it? This article will use native PHP to realize the import and export of CSV format data. and solve the related garbled problem.

Convert XLS to CSV text format, and then use PHP to parse this file, and PHP parsing text is no different.
Advantages: Cross-platform, high efficiency, can read and write.
Cons: You can only use CSV files directly, if you often accept. xls binaries, you need to convert them manually, not automatically. A file has only one sheet.

2. Introduction of related functions

2.1, Fgetcsv

Reference: http://www.w3school.com.cn/php/func_filesystem_fgetcsv.asp

2.2, Fputcsv

Reference: http://www.w3school.com.cn/php/func_filesystem_fputcsv.asp

2.3, fopen

Reference: http://www.w3school.com.cn/php/func_filesystem_fputcsv.asp

2.4, Iconv

Reference: http://php.net/manual/fr/function.iconv.php

This time will roughly use the above 4 functions, to achieve the import and export of CSV file

3. How to export CSV file

Note: The default export is UTF8 encoding, need to transcode strings, Excel Open file default is GBK encoding, so UTF8 export in txt file Open is normal, open in Excel is garbled

3.1. Test UTF8 Export

The exported data format

$head=Array(Id,Name,Age,' Birth date ');$data=Array(Array(' 001 ',' Zs ',10, ' 1991-1-1 ' ), array ( span class= "str" > ' 002 ' , ' John Doe ' ,10 , ' 1991-1-1 ' ), array ( span class= "str" > ' 003 ' , ' Harry ' ,10 , ' 1991-1-1 ' ),  $obj ->putcsv (, $data , $head                

How to export:

Class Excel{/** * [putcsv description] * @param string $csvFileName [description] File name * @param array $dataArr [description] arrays, each set of data is the Use, Split String * @param string $haderText [description] Title (default first line) * @param integer $line [description] starts from the first few lines * @param integer $offset [description] Total Write a few lines * @return [type] [description] */Public functionPutcsv($csvFileName,$dataArr,$haderText= ‘‘,$line= 0,$offset= 0){$handle=fopen($csvFileName,"W");Write mode OpenIf(!$handle){Return ' File open failed ';}Determine if header header is definedIf(!Empty($haderText)){$re= Fputcsv ( $handle ,< Span class= "PLN" > $haderText ); //the function returns the length of the write String. If an error occurs, False is returned: }foreach  ( $DATAARR as $ Key => $value )  {< Span class= "PLN" > $re = Fputcsv ( $handle Span class= "pun" >, $value  The function returns the length of the write String. If an error occurs, False is returned: }}}  /span>                

Operation Result:

Locate the generated CSV file and open it with Notepad first; 

Coding and content are normal, the only problem is that there is no automatic line-wrapping;

When you use Excel to open 

Because Excel is GBK encoded by default, it is garbled and can be encoded in ANSI format using Notepad

3.2, in advance to the Chinese transcoding export

<?PhpClass Excel{/** * [putcsv description] * @param string $csvFileName [description] File name * @param array $dataArr [description] arrays, each set of data is the String @param string $haderText [description] Title * @return [Type] [description] */Public functionPutcsv($csvFileName,$dataArr,$haderText= ‘‘){$handle=fopen($csvFileName,"W");Write mode OpenIf(!$handle){Return ' File open failed ';}Determine if header header is definedIf(!Empty($haderText)){Foreach($haderText as $key=$value) {$haderText[$key] =Iconv("Utf-8","Gbk//ignore",$value);Processing of Chinese encoding}$re=Fputcsv($handle,$haderText);The function returns the length of the write String. If an error occurs, False is returned:}Foreach($DATAARR as $key=$value) {Foreach($value as $k=$v  { $value [] = Iconv (  "utf-8" , "Gbk//ignore"  $v ); //processing Chinese encoding } $re =< Span class= "PLN" > Fputcsv ( $handle , $value //the function returns the length of the write String. If an error occurs, False is returned: }}}  /span>                 

Export results:

This processing is normal in both txt text and Excel. does not appear garbled

4. Import CSV file

Import CSV file also need to transcode Chinese

/** * [getcsv description] Export CSV file * @param string $csvFileName [description] File name * @param integer $line [descr Iption] Reads a few lines, reads all by default * @param integer $offset [description] reads from the first line, read by default * @return [type] [descript ION] */Public functionGetcsv($csvFileName,$line= 0,$offset= 0){$handle=fopen($csvFileName,' R ');Open the file, and if open fails, this function returns FALSE.If(!$handle){Return ' File open failed ';}Fgetcsv () returns FALSE when an error occurs, including when the file ends.$i= 0;Used to record the number of while loops, convenient and $line, $offset comparison$arr=Array();Storage array of resultsWhile($data=Fgetcsv($handle)){Less than the offset is not read, but the $i still needs to be self-increasingIf($i<$offset&&$offset){$i++;Continue;}Exit if the number of rows is greater than readIf($i>$line&&$line){Break;}$i++;Foreach( $data as $key => $value )  { $content = Iconv ( "GBK" , "Utf-8//ignore" ,< Span class= "PLN" > $value ); //conversion code  $arr [] = $content ; As to how to deal with this result, it depends on the actual situation }} return $arr ;               /span>                

CSV import data is a grid read, so in the process of relatively troublesome, need to know in advance a row has a few columns, because it is a test, so there is no special processing of the results.

5. Summary

Note In the import and export process, encountered Chinese characters must remember to transcode, otherwise there may be garbled in the case.

Import and export of CSV files is easy to operate relative to XLS files.
Original address: http://www.cnblogs.com/ImCehnyx/p/7198139.html

PHP Import/export CSV file