Pliki csv często występują jako efekt eksportu danych, np. z programów do fakturowania. Przetwarzanie tych danych jest możliwe w PHP.
<?php $row = 1; if (($handle = fopen("test.csv", "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $num = count($data); echo "<p> $num fields in line $row: <br /></p>n"; $row++; for ($c=0; $c < $num; $c++) { echo $data[$c] . "<br />n"; } } fclose($handle); } ?>
Kod ten wczyta zawartość pliku test.csv i kolejne linie wyrzuci na ekranie.
Inny przykład:
<?php $File = 'test.csv'; $arrResult = array(); $handle = fopen($File, "r"); if(empty($handle) === false) { while(($data = fgetcsv($handle, 1000, ",")) !== FALSE){ $arrResult[] = $data; } fclose($handle); } print_r($arrResult); ?>
Wrzucenie pliku csv do tablicy pozwoli również w nowej wersji PHP:
$csv = array_map('str_getcsv', file('test.csv'));
Linijkę tego kodu można rozwinąć do postaci:
<?php $csv = array_map('str_getcsv', file($file)); array_walk($csv, function(&$a) use ($csv) { $a = array_combine($csv[0], $a); }); array_shift($csv); // remove column header ?>
Otrzymamy tablicę:
[2] => Array ( [nazwa] => PHU Elite [ulica] => Konstytucji [miasto] => Świnoujście )
Kolejna wariacja wczytywania danych z pliku csv:
$row = 0; $headers = []; $filepath = "test.csv"; if (($handle = fopen($filepath, "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { if (++$row == 1) { $headers = array_flip($data); // Get the column names from the header. continue; } else { $col1 = $data[$headers['Col1Name']]; // Read row by the column name. $col2 = $data[$headers['Col2Name']]; print "Row $row: $col1, $col2n"; } } fclose($handle); }
Oczywiście, można skorzystać z gotowych rozwiązań, np. stąd: https://github.com/luchaninov/csv-file-loader
Wtedy możemy plik csv przetwarzać dzięki:
$loader = new CsvFileLoader('/path/to/your_data.csv'); foreach ($loader->getItems() as $item) { var_dump($item); // do something here }
co dla pliku csv zawierającego np.:
id, name, surname
1, Jack, Black
2, John, Doe
da w efekcie tablicę:
['id' => '1', 'name' => 'Jack', 'surname' => 'Black'] ['id' => '2', 'name' => 'John', 'surname' => 'Doe']
Gdy w pliku csv w pierwszej linijce pliku nie mamy kluczy nagłówków tabeli, możemy je ustawić:
- własne klucze - setHeaders(['key1', 'key2', ...]);
- stosując klucze numeryczne [0, 1, 2, ...] - setHeaders(false);
Parsowanie z pliku:
<?php $Data = str_getcsv($CsvString, "n"); //parse the rows foreach($Data as &$Row) $Row = str_getcsv($Row, ";"); //parse the items in rows ?>
Gdy w pliku csv mamy linię z kluczami tabeli:
<?php $array = array_map('str_getcsv', file('test.csv')); $header = array_shift($array); array_walk($array, '_combine_array', $header); function _combine_array(&$row, $key, $header) { $row = array_combine($header, $row); } ?>
Gdy w pliku csv mamy kodowanie znaków w UTF-8:
<?php // returns a two-dimensional array or rows and fields function parse_csv ($csv_string, $delimiter = ",", $skip_empty_lines = true, $trim_fields = true) { $enc = preg_replace('/(?<!")""/', '!!Q!!', $csv_string); $enc = preg_replace_callback( '/"(.*?)"/s', function ($field) { return urlencode(utf8_encode($field[1])); }, $enc ); $lines = preg_split($skip_empty_lines ? ($trim_fields ? '/( *R)+/s' : '/R+/s') : '/R/s', $enc); return array_map( function ($line) use ($delimiter, $trim_fields) { $fields = $trim_fields ? array_map('trim', explode($delimiter, $line)) : explode($delimiter, $line); return array_map( function ($field) { return str_replace('!!Q!!', '"', utf8_decode(urldecode($field))); }, $fields ); }, $lines ); } ?>
Inna wersja:
<?php // returns the same two-dimensional array as above, but with a one-liner code function parse_csv ($csv_string, $delimiter = ",", $skip_empty_lines = true, $trim_fields = true) { return array_map( function ($line) use ($delimiter, $trim_fields) { return array_map( function ($field) { return str_replace('!!Q!!', '"', utf8_decode(urldecode($field))); }, $trim_fields ? array_map('trim', explode($delimiter, $line)) : explode($delimiter, $line) ); }, preg_split( $skip_empty_lines ? ($trim_fields ? '/( *R)+/s' : '/R+/s') : '/R/s', preg_replace_callback( '/"(.*?)"/s', function ($field) { return urlencode(utf8_encode($field[1])); }, $enc = preg_replace('/(?<!")""/', '!!Q!!', $csv_string) ) ) ); } ?>
Gdy pojawić się mogą problemy ze znakami BOM w utf-8:
<?php $bom = pack('CCC', 0xEF, 0xBB, 0xBF); if (strncmp($yourString, $bom, 3) === 0) { $body = substr($yourString, 3); } ?>
lub:
$bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ); //define bom $f = file_get_contents('a.csv'); //open the CSV file #$csv = str_getcsv($f); //it will have bom $csv = str_getcsv(str_replace($bom,'',$f)); //replace the bom var_dump($csv); //dump
Inna funkcja, która przekonwertuje plik csv do tablicy asocjacyjnej:
<?php /** * @link http://gist.github.com/385876 */ function csv_to_array($filename='', $delimiter=',') { if(!file_exists($filename) || !is_readable($filename)) return FALSE; $header = NULL; $data = array(); if (($handle = fopen($filename, 'r')) !== FALSE) { while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) { if(!$header) $header = $row; else $data[] = array_combine($header, $row); } fclose($handle); } return $data; } ?>
Gdy problemem stają się znaki cudzysłowów w pliku:
//parse a CSV file into a two-dimensional array //this seems as simple as splitting a string by lines and commas, but this only works if tricks are performed //to ensure that you do NOT split on lines and commas that are inside of double quotes. function parse_csv($str) { //match all the non-quoted text and one series of quoted text (or the end of the string) //each group of matches will be parsed with the callback, with $matches[1] containing all the non-quoted text, //and $matches[3] containing everything inside the quotes $str = preg_replace_callback('/([^"]*)("((""|[^"])*)"|$)/s', 'parse_csv_quotes', $str); //remove the very last newline to prevent a 0-field array for the last line $str = preg_replace('/n$/', '', $str); //split on LF and parse each line with a callback return array_map('parse_csv_line', explode("n", $str)); }
//replace all the csv-special characters inside double quotes with markers using an escape sequence function parse_csv_quotes($matches) { //anything inside the quotes that might be used to split the string into lines and fields later, //needs to be quoted. The only character we can guarantee as safe to use, because it will never appear in the unquoted text, is a CR //So we're going to use CR as a marker to make escape sequences for CR, LF, Quotes, and Commas. $str = str_replace("r", "rR", $matches[3]); $str = str_replace("n", "rN", $str); $str = str_replace('""', "rQ", $str); $str = str_replace(',', "rC", $str); //The unquoted text is where commas and newlines are allowed, and where the splits will happen //We're going to remove all CRs from the unquoted text, by normalizing all line endings to just LF //This ensures us that the only place CR is used, is as the escape sequences for quoted text return preg_replace('/rn?/', "n", $matches[1]) . $str; }
//split on comma and parse each field with a callback function parse_csv_line($line) { return array_map('parse_csv_field', explode(',', $line)); } //restore any csv-special characters that are part of the data function parse_csv_field($field) { $field = str_replace("rC", ',', $field); $field = str_replace("rQ", '"', $field); $field = str_replace("rN", "n", $field); $field = str_replace("rR", "r", $field); return $field; }
Kolejne rozwiązanie do konwersji plików csv do tablicy:
function csv_to_array($string='', $row_delimiter=PHP_EOL, $delimiter = "," , $enclosure = '"' , $escape = "" ) { $rows = array_filter(explode($row_delimiter, $string)); $header = NULL; $data = array(); foreach($rows as $row) { $row = str_getcsv ($row, $delimiter, $enclosure , $escape); if(!$header) $header = $row; else $data[] = array_combine($header, $row); } return $data; }
Dla starszych wersji PHP, które nie posiadają kilku nowych łatwych funkcji:
<?php if (!function_exists('str_getcsv')) { function str_getcsv($input, $delimiter = ',', $enclosure = '"', $escape = '', $eol = 'n') { if (is_string($input) && !empty($input)) { $output = array(); $tmp = preg_split("/".$eol."/",$input); if (is_array($tmp) && !empty($tmp)) { while (list($line_num, $line) = each($tmp)) { if (preg_match("/".$escape.$enclosure."/",$line)) { while ($strlen = strlen($line)) { $pos_delimiter = strpos($line,$delimiter); $pos_enclosure_start = strpos($line,$enclosure); if ( is_int($pos_delimiter) && is_int($pos_enclosure_start) && ($pos_enclosure_start < $pos_delimiter) ) { $enclosed_str = substr($line,1); $pos_enclosure_end = strpos($enclosed_str,$enclosure); $enclosed_str = substr($enclosed_str,0,$pos_enclosure_end); $output[$line_num][] = $enclosed_str; $offset = $pos_enclosure_end+3; } else { if (empty($pos_delimiter) && empty($pos_enclosure_start)) { $output[$line_num][] = substr($line,0); $offset = strlen($line); } else { $output[$line_num][] = substr($line,0,$pos_delimiter); $offset = ( !empty($pos_enclosure_start) && ($pos_enclosure_start < $pos_delimiter) ) ?$pos_enclosure_start :$pos_delimiter+1; } } $line = substr($line,$offset); } } else { $line = preg_split("/".$delimiter."/",$line); /* * Validating against pesky extra line breaks creating false rows. */ if (is_array($line) && !empty($line[0])) { $output[$line_num] = $line; } } } return $output; } else { return false; } } else { return false; } } } ?>
Kolejna funkcja, wrzucająca plik csv do tablicy:
<?php function csv_to_array($csv, $delimiter = ',', $enclosure = '"', $escape = '', $terminator = "n") { $r = array(); $rows = explode($terminator,trim($csv)); $names = array_shift($rows); $names = str_getcsv($names,$delimiter,$enclosure,$escape); $nc = count($names); foreach ($rows as $row) { if (trim($row)) { $values = str_getcsv($row,$delimiter,$enclosure,$escape); if (!$values) $values = array_fill(0,$nc,null); $r[] = array_combine($names,$values); } } return $r; } ?>
<?php function csv_to_array($filename, $delimiter=',', $enclosure='"', $escape = '') { if(!file_exists($filename) || !is_readable($filename)) return false; $header = null; $data = array(); $lines = file($filename); foreach($lines as $line) { $values = str_getcsv($line, $delimiter, $enclosure, $escape); if(!$header) $header = $values; else $data[] = array_combine($header, $values); } return $data; } ?>
Odczyt z pliku - kolejna wersja:
$data=array();
while (!feof($fp))
{
$data[] = fgetcsv($fp, 0, $delimiter, $enclosure); // $escape only got added in 5.3.0
}
Eksport do plików csv może być realizowane tak:
<?php if(!function_exists('str_putcsv')) { function str_putcsv($input, $delimiter = ',', $enclosure = '"') { // Open a memory "file" for read/write... $fp = fopen('php://temp', 'r+'); // ... write the $input array to the "file" using fputcsv()... fputcsv($fp, $input, $delimiter, $enclosure); // ... rewind the "file" so we can read what we just wrote... rewind($fp); // ... read the entire line into a variable... $data = fgets($fp); // ... close the "file"... fclose($fp); // ... and return the $data to the caller, with the trailing newline from fgets() removed. return rtrim( $data, "n" ); } } ?>
Inna wersja zamiany tablicy na plik csv:
<?php function str_putcsv($array, $delimiter = ',', $enclosure = '"', $terminator = "n") { # First convert associative array to numeric indexed array foreach ($array as $key => $value) $workArray[] = $value; $returnString = ''; # Initialize return string $arraySize = count($workArray); # Get size of array for ($i=0; $i<$arraySize; $i++) { # Nested array, process nest item if (is_array($workArray[$i])) { $returnString .= str_putcsv($workArray[$i], $delimiter, $enclosure, $terminator); } else { switch (gettype($workArray[$i])) { # Manually set some strings case "NULL": $_spFormat = ''; break; case "boolean": $_spFormat = ($workArray[$i] == true) ? 'true': 'false'; break; # Make sure sprintf has a good datatype to work with case "integer": $_spFormat = '%i'; break; case "double": $_spFormat = '%0.2f'; break; case "string": $_spFormat = '%s'; break; # Unknown or invalid items for a csv - note: the datatype of array is already handled above, assuming the data is nested case "object": case "resource": default: $_spFormat = ''; break; } $returnString .= sprintf('%2$s'.$_spFormat.'%2$s', $workArray[$i], $enclosure); $returnString .= ($i < ($arraySize-1)) ? $delimiter : $terminator; } } # Done the workload, return the output information return $returnString; } ?>
Ciekawą funkcją zapisu do plików csv można znaleźć w artykule:
http://webtricksandtreats.com/export-to-csv-php/
function convert_to_csv($input_array, $output_file_name, $delimiter) { /** open raw memory as file, no need for temp files */ $temp_memory = fopen('php://memory', 'w'); /** loop through array */ foreach ($input_array as $line) { /** default php csv handler **/ fputcsv($temp_memory, $line, $delimiter); } /** rewrind the "file" with the csv lines **/ fseek($temp_memory, 0); /** modify header to be downloadable csv file **/ header('Content-Type: application/csv'); header('Content-Disposition: attachement; filename="' . $output_file_name . '";'); /** Send file to browser for download */ fpassthru($temp_memory); } /** Array to convert to csv */ $array_to_csv = Array(Array(12566, 'Enmanuel', 'Corvo'), Array(56544, 'John', 'Doe'), Array(78550, 'Mark', 'Smith')); convert_to_csv($array_to_csv, 'report.csv', ',');
Zapis i odczyt do pliku csv:
<?php if( !function_exists("parse_csv") ){ function parse_csv($string){ /* Author : Alexander Peev, posted at PHP.NET */ if( !function_exists("parse_csv_aux") ){ function parse_csv_aux( $string ){ $product = ""; $in_quote = FALSE; $skipped_quote = FALSE; for( $i = 0 ; $i < strlen($string) ; $i++ ){ if( $string{$i} == """ ){ if($in_quote){ if($skipped_quote){ $product .= """; $skipped_quote = FALSE; } else if( !$skipped_quote ){ $skipped_quote = TRUE; } } else{ if($skipped_quote) $skipped_quote = FALSE; $in_quote = TRUE; } } else if( $string{$i} == ";" ){ if($in_quote){ $product .= ";"; } else{ $product .= " ; "; } } else{ if($in_quote){ $in_quote = FALSE; $product .= $string{$i}; } else{ $product .= $string{$i}; } } } return $product; } } $data = array(); if( is_string($string) && ( stripos($string, "n") !== FALSE ) ){ $data = explode("n", parse_csv_aux($string) ); foreach($data as $key => $row){ $columns = array(); //$row = strtr( $row, array( "";"" => "";"", ";" => " ; " ) ); if( stripos($row, " ; ") !== FALSE ){ $columns = explode( " ; ", $row ); if( !is_array($columns) )$columns = array( strval($columns) ); $data[$key] = $columns; } } return $data; } else if( is_string($string) && ( stripos( ($string = parse_csv_aux($string)), " ; ") !== FALSE ) ){ $columns = explode( " ; ", $string ); if( !is_array($columns) )$columns = array( strval($columns) ); return array($columns); } else return strval($string); } /* end function parse_csv */ } /* end not function exists parse_csv */ if( !function_exists("store_csv") ){ function store_csv($data){ /* Author : Alexander Peev, posted at PHP.NET */ if( !function_exists("store_csv_aux") ){ function store_csv_aux( $string ){ $string = strtr( $string, array( "n" => "" ) ); $product = ""; $in_quote = FALSE; for( $i = 0 ; $i < strlen($string) ; $i++ ){ if( $string{$i} == """ ){ if($in_quote){ $product .= """"; } else{ $product .= """""; $in_quote = TRUE; } } else if( $string{$i} == ";" ){ if($in_quote){ $product .= ";"; } else{ $product .= "";"; $in_quote = TRUE; } } else{ if($in_quote){ $product .= """; $in_quote = FALSE; $product .= $string{$i}; } else{ $product .= $string{$i}; } } } if($in_quote)$product .= """; return $product; } } if(!is_array($data))return strval($data); $passed_rows = FALSE; $product = ""; foreach($data as $row){ if( $passed_rows )$product .= "n"; if( is_array($row) ){ $columns = ""; $passed_cols = FALSE; foreach($row as $column){ if( $passed_cols )$columns .= ";"; $columns .= store_csv_aux( $column ); $passed_cols =TRUE; } $product .= strval($columns); } else{ $product .= strtr( strval($row), array("n" => "") ); } $passed_rows = TRUE; } return $product; } /* end function store_csv */ } /* end not function exists store_csv */ ?>