fgetc
Currently, I'm working on a script that takes data exported from one database and converts it so that it can be imported in another. I have been given a bunch of CSV files containing data to make the translation possible --actually, the values are semi-colon separated, not comma-separated.
The following script reads a CSV file with field names on the first line (for an example, see below the script), and turns it into a hash table where the key is one of the fields.
<?php
/**
* Reads a CSV file and stores it as a lookup table, implemented as a PHP hash table.
*
* @param string $csv_file the CSV file to read.
* @param mixed $index_by the column name (string) ort number (integer) to index the lookup table by.
* @param string $separator the character that is used in the CSV file as field separator.
* @param integer $rec_len the maximum length of a record in the input file.
* @return mixed an error number or the resulting hash table.
*/
function read_lookup_table_from_csv($csv_file, $index_by = 0, $separator = ';', $rec_len = 1024)
{
$handle = fopen($csv_file, 'r');
if($handle == null || ($data = fgetcsv($handle, $rec_len, $separator)) === false)
{
// Couldn't open/read from CSV file.
return -1;
}
$names = array();
foreach($data as $field)
{
$names[] = trim($field);
}
if(is_int($index_by))
{
if($index_by < 0 || $index_by > count($data))
{
// Index out of bounds.
fclose($handle);
return -2;
}
}
else
{
// If the column to index by is given as a name rather than an integer, then
// determine that named column's integer index in the $names array, because
// the integer index is used, below.
$get_index = array_keys($names, $index_by);
$index_by = $get_index[0];
if(is_null($index_by))
{
// A column name was given (as opposed to an integer index), but the
// name was not found in the first row that was read from the CSV file.
fclose($handle);
return -3;
}
}
$retval = array();
while(($data = fgetcsv($handle, $rec_len, $separator)) !== false)
{
$retval[trim($data[$index_by])] = array_combine($names, $data);
}
fclose($handle);
return $retval;
}
?>
Assume the CSV file DaysOfWeek.csv contains this:
DayNo;DayName;DayAbbr
0;Sunday;Sun
1;Monday;Mon
2;Tuesday;Tue
3;Wednesday;Wed
4;Thursday;Thu
5;Friday;Fri
6;Saturday;Sat
(Reproduced in full as a service to you, dear reader, so you can more easily test the script if you wish. :-)
Now,
$days = read_lookup_table_from_csv('DaysOfWeek.csv');
$days = read_lookup_table_from_csv('DaysOfWeek.csv', 0);
$days = read_lookup_table_from_csv('DaysOfWeek.csv', 'DayNo');
will all read the file, and index it using the contents of the zero'th column as an index.
echo 'The name of day #3 is ', $days[3]['Name'];
Alternatively, you can use the function like this
$days = read_lookup_table_from_csv('DaysOfWeek.csv', 'DayName');
echo 'The abbreviation of Friday is ', $days['Friday']['DayAbbr'];
or like this
$days = read_lookup_table_from_csv('DaysOfWeek.csv', 'DayAbbr');
echo 'The full name of Fri is ', $days['Fri']['Name'];
Typically, of course, the first index to $days will be a variable rather than a literal/constant, allowing for easy translation of one value to another.
fgetcsv
(PHP 4, PHP 5)
fgetcsv — ファイルポインタから行を取得し、CSVフィールドを処理する
説明
array fgetcsv
( resource $handle
[, int $length
[, string $delimiter
[, string $enclosure
[, string $escape
]]]] )
fgets() に動作は似ていますが、 fgetcsv() は行を CSV フォーマットのフィールドとして読込み処理を行い、 読み込んだフィールドを含む配列を返すという違いがあります。
パラメータ
- handle
-
ファイルポインタは有効なものでなければならず、また fopen(), popen(), もしくは fsockopen() で正常にオープンされたファイルを指している必要があります。
- length
-
(行末文字を考慮して) CSV ファイルにある最も長い行よりも大きい必要があります。 PHP 5 でオプションになりました。このパラメータを省略 (もしくは PHP 5.0.4 かそれ以降で 0 を設定) すると、 最大行長は制限されません。この場合、若干動作が遅くなります。
- delimiter
-
フィールドのデリミタ (1 文字のみ) を設定します。 デフォルトはカンマです。
- enclosure
-
フィールド囲いこみ文字 (1 文字のみ) を設定します。 デフォルトはダブルクォーテーションマークです。
- escape
-
エスケープ文字 (1 文字のみ) を設定します。 デフォルトはバックスラッシュ (\) です。
返り値
読み込んだフィールドの内容を含む数値添字配列を返します。
注意: CSV ファイルの空行は null フィールドを一つだけ含む配列として返され、 エラーにはなりません。
注意: マッキントッシュコンピュータ上で作成されたファイルを読み込む際に、 PHP が行末を認識できないという問題が発生した場合、 実行時の設定オプションauto_detect_line_endings を有効にする必要が生じるかもしれません。
fgetcsv() は、ファイルの終端に達した場合を含めてエラー時に FALSE を返します。
変更履歴
| バージョン | 説明 |
|---|---|
| 5.3.0 | escape パラメータが追加されました。 |
| 4.3.5 | fgetcsv() はバイナリセーフとなりました。 |
| 4.3.0 | enclosure パラメータが追加されました。 |
例
例1 CSV ファイルの全てのコンテンツを読み込み、表示する
<?php
$row = 1;
$handle = fopen("test.csv", "r");
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);
?>
注意
注意: この関数はロケール設定を考慮します。もし LANG が例えば en_US.UTF-8 の場合、 ファイル中の 1 バイトエンコーディングは間違って読み込まれます。
参考
- str_getcsv() - CSV 文字列をパースして配列に格納する
- explode() - 文字列を文字列により分割する
- file() - ファイル全体を読み込んで配列に格納する
- pack() - データをバイナリ文字列にパックする
- fputcsv() - 行を CSV 形式にフォーマットし、ファイルポインタに書き込む
add a note
User Contributed Notesfgetcsv
jaimthorn at yahoo dot com
01-Feb-2009 12:13
01-Feb-2009 12:13
Zyphos
18-Dec-2008 04:32
18-Dec-2008 04:32
Here is a script that convert a CSV to an double-array ([row][col]), none of the ones below works in my case. (Multi-line, quoted or not, ...) It use coma as field separator, and double-quote.
<?php
$fileContent = file_get_contents("my_file.csv");
//Convert Windows CR to Linux
$fileContent = str_replace("\r\n","\n",$fileContent);
//Split file by finding right CR.
$expr="/\n(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
$rows=preg_split($expr,trim($fileContent));
unset($fileContent); //Free up some memory
$data = array();
foreach($rows as $row){
//Find right coma
$expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
$results = preg_split($expr,trim($row));
//Remove quote
$data[] = preg_replace("/\"(.*)\"/s","$1",$results);
}
unset($rows); //Free up some memory
//Show the result
echo "<pre>";
print_r($data);
echo "</pre>";
?>
james dot ellis at gmail dot com
24-Nov-2008 08:19
24-Nov-2008 08:19
If you need to set auto_detect_line_endings to deal with Mac line endings, it may seem obvious but remember it should be set before fopen, not after:
This will work:
<?php
ini_set('auto_detect_line_endings',TRUE);
$handle = fopen('/path/to/file','r');
while ( ($data = fgetcsv($handle) ) !== FALSE ) {
//process
}
ini_set('auto_detect_line_endings',FALSE);
?>
This won't, you will still get concatenated fields at the new line position:
<?php
$handle = fopen('/path/to/file','r');
ini_set('auto_detect_line_endings',TRUE);
while ( ($data = fgetcsv($handle) ) !== FALSE ) {
//process
}
ini_set('auto_detect_line_endings',FALSE);
?>
cizar at nixar dot org
27-Oct-2008 03:51
27-Oct-2008 03:51
In my opinion, the two examples that use the SPL Iterator Interface don't work as they should. If you call several times to the method current() then it eventually will reach the eof...
<?php
public function current() {
$this->currentElement = fgetcsv($this->filePointer, self::ROW_SIZE, $this->delimiter);
$this->rowCounter++;
return $this->currentElement;
}
?>
If you want to go next, you need to call the method next(). So here is my solution...
<?php
/**
* @author cizar@nixar.org
*/
class CsvIterator implements Iterator
{
const DEFAULT_DELIMITER = ';';
const DEFAULT_LENGTH = 4096;
/**
* The CSV file handler.
*
* @var resource
* @access private
*/
private $_handler = null;
/**
* The delimiter of the CSV file.
*
* @var string
* @access private
*/
private $_delimiter = null;
/**
* The dafs
*
* @var integer
* @access private
*/
private $_length = null;
/**
* The row counter.
*
* @var integer
* @access private
*/
private $_key = null;
/**
* The element that will be returned on each iteration.
*
* @var mixed
* @access private
*/
private $_current = null;
/**
* This is the constructor. It try to open the CSV file.
*
* @access public
* @param string $filename The fullpath of the CSV file.
* @param string $delimiter The delimiter.
* @param integer $length The amount of bytes to be read on each iteration.
*
* @throws Exception
*/
public function __construct ($filename, $delimiter = self::DEFAULT_DELIMITER, $length = self::DEFAULT_LENGTH)
{
if (($this->_handler = fopen($filename, 'r')) === false) {
throw new Exception("The file '$filename' cannot be opened");
}
$this->_delimiter = $delimiter;
$this->_length = $length;
}
/**
* This is the destructor. It close the CSV file.
*
* @access public
*/
public function __destruct ()
{
fclose($this->_handler);
}
/**
* This method move the file pointer to the next row.
*
* @access public
*/
public function next ()
{
$this->_read();
$this->_key += 1;
}
/**
* This method reset the file handler.
*
* @access public
*/
public function rewind ()
{
rewind($this->_handler);
$this->_read();
$this->_key = 0;
}
/**
* This method returns the current row number.
*
* @access public
*/
public function key ()
{
return $this->_key;
}
/**
* This methods return the current CSV row data.
*
* @access public
* @return array The row as an one-dimensional array
*/
public function current ()
{
return $this->_current;
}
/**
* This method checks if the current row is readable.
*
* @access public
* @return boolean If the current row is readable.
*/
public function valid ()
{
return $this->_current !== FALSE;
}
/**
* This method read the next row of the CSV file.
*
* @access private
*/
private function _read ()
{
$this->_current = fgetcsv($this->_handler, $this->_length, $this->_delimiter);
}
}
michael dot martinek at gmail dot com
09-Oct-2008 07:12
09-Oct-2008 07:12
Here's something I put together this morning. It allows you to read rows from your CSV and get values based on the name of the column. This works great when your header columns are not always in the same order; like when you're processing many feeds from different customers. Also makes for cleaner, easier to manage code.
So if your feed looks like this:
product_id,category_name,price,brand_name, sku_isbn_upc,image_url,landing_url,title,description
123,Test Category,12.50,No Brand,0,http://www.paperpear.com, http://www.paperpear.com/landing.php, Some Title,Some Description
You can do:
<?php
while ($o->getNext())
{
$dPrice = $o->getPrice();
$nProductID = $o->getProductID();
$sBrandName = $o->getBrandName();
}
?>
If you have any questions or comments regarding this class, they can be directed to michael.martinek@gmail.com as I probably won't be checking back here.
<?php
define('C_PPCSV_HEADER_RAW', 0);
define('C_PPCSV_HEADER_NICE', 1);
class PaperPear_CSVParser
{
private $m_saHeader = array();
private $m_sFileName = '';
private $m_fp = false;
private $m_naHeaderMap = array();
private $m_saValues = array();
function __construct($sFileName)
{
//quick and dirty opening and processing.. you may wish to clean this up
if ($this->m_fp = fopen($sFileName, 'r'))
{
$this->processHeader();
}
}
function __call($sMethodName, $saArgs)
{
//check to see if this is a set() or get() request, and extract the name
if (preg_match("/[sg]et(.*)/", $sMethodName, $saFound))
{
//convert the name portion of the [gs]et to uppercase for header checking
$sName = strtoupper($saFound[1]);
//see if the entry exists in our named header-> index mapping
if (array_key_exists($sName, $this->m_naHeaderMap))
{
//it does.. so consult the header map for which index this header controls
$nIndex = $this->m_naHeaderMap[$sName];
if ($sMethodName{0} == 'g')
{
//return the value stored in the index associated with this name
return $this->m_saValues[$nIndex];
}
else
{
//set the valuw
$this->m_saValues[$nIndex] = $saArgs[0];
return true;
}
}
}
//nothing we control so bail out with a false
return false;
}
//get a nicely formatted header name. This will take product_id and make
//it PRODUCTID in the header map. So now you won't need to worry about whether you need
//to do a getProductID, or getproductid, or getProductId.. all will work.
public static function GetNiceHeaderName($sName)
{
return strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $sName));
}
//process the header entry so we can map our named header fields to a numerical index, which
//we'll use when we use fgetcsv().
private function processHeader()
{
$sLine = fgets($this->m_fp);
//you'll want to make this configurable
$saFields = split(",", $sLine);
$nIndex = 0;
foreach ($saFields as $sField)
{
//get the nice name to use for "get" and "set".
$sField = trim($sField);
$sNiceName = PaperPear_CSVParser::GetNiceHeaderName($sField);
//track correlation of raw -> nice name so we don't have to do on-the-fly nice name checks
$this->m_saHeader[$nIndex] = array(C_PPCSV_HEADER_RAW => $sField, C_PPCSV_HEADER_NICE => $sNiceName);
$this->m_naHeaderMap[$sNiceName] = $nIndex;
$nIndex++;
}
}
//read the next CSV entry
public function getNext()
{
//this is a basic read, you will likely want to change this to accomodate what
//you are using for CSV parameters (tabs, encapsulation, etc).
if (($saValues = fgetcsv($this->m_fp)) !== false)
{
$this->m_saValues = $saValues;
return true;
}
return false;
}
}
//quick example of usage
$o = new PaperPear_CSVParser('F:\foo.csv');
while ($o->getNext())
{
echo "Price=" . $o->getPrice() . "\r\n";
}
?>
marcos at yuniti com
09-Sep-2008 02:35
09-Sep-2008 02:35
Smarter/better fgetcsv functions, acceptying multiple-character delim, enclosure, and escape.
<?php
define('BUFFER_READ_LEN', 4096);
function fgetcsv_ex($file_handle, $delim = ',', $enclosure = '"', $escape = '"') {
$fields = null;
$fldCount = 0;
$inQuotes = false;
$complete = false;
$search_chars_list = array('\r\n', '\n', '\r');
if ( $delim && ($delim != '') )
$search_chars_list[] = $delim;
if ( $enclosure && ($enclosure != '') )
{
$search_chars_list[] = $enclosure;
$enclosure_len = strlen($enclosure);
}
else
$enclosure_len = 0;
if ( $escape && ($escape != '') )
{
$search_chars_list[] = $escape;
$escape_len = strlen($escape);
}
else
$escape_len = 0;
$search_regex = '/' . implode('|', $search_chars_list) . '/';
$cur_pos = 0;
$line = '';
$cur_value = '';
$in_value = false;
$last_value = 0;
while ( ! $complete )
{
$read_result = fread($file_handle, BUFFER_READ_LEN);
if ( $read_result )
$line .= $read_result;
else if ( strlen($line) == 0 )
return null;
else
$line .= "\n";
$line_len = strlen($line);
while ( true )
{
if (! preg_match($search_regex, $line, $matches, PREG_OFFSET_CAPTURE, $cur_pos))
{
if ($read_result) //need more chars
break;
else
return null; //Incomplete file
}
else
{
$non_escape = false;
$cur_char = $matches[0][0];
$cur_len = strlen($cur_char);
$new_pos = $matches[0][1];
if (($enclosure == $escape) && $in_value && ($cur_char == $escape)) //Escape char = enclosure char special handling
{
if (($new_pos + $cur_len + $enclosure_len) >= $line_len) //We need the next char
break;
$next_char = substr($line, $new_pos + $cur_len, $enclosure_len);
if ( (! $enclosure) || ($next_char != $enclosure) )
$non_escape = true;
}
$cur_pos = $new_pos;
if ( $in_value && (! $non_escape) )
{
$cur_value .= mb_substr($line, $last_value, $cur_pos - $last_value);
if ($cur_char == $escape) //Skip escape char
$cur_pos += $escape_len;
$last_value = $cur_pos;
}
else if ( ( $cur_char == "\n" ) || ( $cur_char == "\r" ) || ( $cur_char == "\r\n" ) )
{
$blank_start_lines = ( $cur_pos == 0 );
++$cur_pos;
$cur_pos = $cur_pos + strspn($line, "\n\r", $cur_pos);
if ( ! $blank_start_lines )
{
$complete = true;
break;
}
else
{
$last_value = $cur_pos;
continue;
}
}
else if ( $cur_char == $delim )
{
if ( is_null($fields) )
$fields = array();
$fields[] = $cur_value . trim(mb_substr($line, $last_value, $cur_pos - $last_value));
$last_value = $cur_pos + $cur_len;
$cur_value = '';
}
else if ( $cur_char == $enclosure )
{
if ( $in_value )
$cur_value .= mb_substr($line, $last_value, $cur_pos - $last_value);
$last_value = $cur_pos + $cur_len;
$in_value = ! $in_value;
}
$cur_pos += $cur_len;
}
}
}
fseek($file_handle, $cur_pos - strlen($line), SEEK_CUR);
return $fields;
}
?>
hunzinker at gmail dot com
28-Aug-2008 01:45
28-Aug-2008 01:45
Here is another function that splits the rows of a CSV file and uses a comma as its delimiter. The function also ignores commas inside of double quotations and removes any double quotes and ultimately prints out the data into a table. If you need to keep the double quotes you'll need to edit the code.
The function takes an array as an argument. The array I pass into the function contains the display name and CSV file name.
Enjoy!
<?php
/**
* Function to print out data from an uploaded CSV file into a table.
*
* @param array $data
* @return
*/
function printCSV ($data) {
if (is_array($data)) {
// open the csv file and read it
$dataFile = 'documents/courses/' . $data['course_file']; // change per setup - $data['course_file'] = CSV file name
$open = fopen($dataFile, 'r');
$csv = fread($open, filesize($dataFile));
print "<h2>" . $data['name'] . "</h2>"; // optional
// split up the rows
$csv = preg_split('/[\r\n]/', $csv);
$csvData = array();
$i = 0;
foreach ($csv as $line) {
// splits lines with a comma but ignores a comma wrapped with double quotes
$csvData[$i] = preg_split('/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/', $line);
$i++;
}
print "<table width='60%' cellpadding='0' cellspacing='0' class='course-table'>";
for ($j = 0; $j < count($csvData); $j++) {
// print the first line with <th> tags
if ($csvData && $j == 0) {
print "<tr>";
for ($k = 0; $k < count($csvData[$j]); $k++) {
// remove the double quotes
if (preg_match('/\"/', $csvData[$j][$k])) {
print "<th>" . trim($csvData[$j][$k], '"') . "</th>"; // edit if you wish to keep the double quotes
} else {
print "<th>" . $csvData[$j][$k] . "</th>";
}
}
print "</tr>";
// print the rest with <td> tags
} else {
print "<tr>";
for ($k = 0; $k < count($csvData[$j]); $k++) {
// remove the double quotes
if (preg_match('/\"/', $csvData[$j][$k])) {
print "<td>" . trim($csvData[$j][$k], '"') . "</td>"; // edit if you wish to keep the double quotes
} else {
print "<td>" . $csvData[$j][$k] . "</td>";
}
}
print "</tr>";
}
}
print "</table>";
} else {
return false;
}
}
?>
Dave
01-Aug-2008 03:03
01-Aug-2008 03:03
If you want to get your CSV rows (in this case a file posted from a form) into one big array, you can use this code. You may have to play around with the ord() and explode separators depending on your line endings. You can change to the commented out explode to use " delimiter.
<?php
if( $_FILES['file']['tmp_name'] )
{
$csv_rows=Array();
$csv="";
$handle = fopen($_FILES['file']['tmp_name'], "r");
//load char by char, to replace line endings
while($data = fgetc($handle))
{
if(ord($data)==13)
{
$csv.="\r\n";
}
else
{
$csv.=$data;
}
}
fclose($handle);
$csv_lines=explode("\r\n", $csv);
foreach($csv_lines as $line)
{
$csv_rows[]=explode(",", $line);
//or ltrim(rtrim(explode('","', $line),'"'),'"') for delimited fields
}
}
?>
Vijayaraj Nagarajan
11-Jul-2008 06:40
11-Jul-2008 06:40
I used this simple php code for reading a csv file with two columns (without column heads) and generating an associative array out of it...
<?php
#reads the file data.csv
$data=file("data.csv", FILE_IGNORE_NEW_LINES);
#explodes each line contents and assigns the values to an array
foreach ($data as $line)
{
$mparts=explode(',',$line);
#pushes the array values in to an associative array
$dataarray[$mparts[0]]=$mparts[1];
}
#prints the values of the new associative array
print_r($dataarray);
?>
tanthalas at magickfox dot org
30-May-2008 05:33
30-May-2008 05:33
Hey everybody...
I know that a lot of people have written code to parse CSVs etc... I have written one which I believe is a lot smaller and faster than those listed here...
<?php
/*
class.parser.php
Dynamically parses a CSV file and returns the values as array for processing.
*/
class parser {
function doParse($csvFile,$sep) {
$csvFile = file($csvFile);
foreach ($csvFile as $key=>$value) {
$v = explode($sep,$value);
foreach ($v as $kk=>$lineItem) {
$csv[$key][$kk] = trim(trim($lineItem),"\"");
}
}
return $csv; // an associative array of the csv.
}
function dumpCSV($data,$sep,$heading = "") {
/*
build the "csv" from a 2 dimensional array
$result = $parser->dumpCSV($data,",",$heading);
where $data is an array formed similar to doParse (see above), and $heading
is the heading line for the CSV (titles etc).... Something like
$heading = "\"Booking Number\",\"Booking Date\"\n";
if you do not want/need a headling line, do not include $heading
*/
unset($message);
$message[] = $heading;
$x = 0;
foreach ($data as $key=>$v) {
unset($tempmsg);
foreach ($v as $item) {
$tempmsg .= "\"".$item."\"".$sep;
}
$message[$x] = trim($tempmsg,","); // gets rid of excess , @ the end of each line.
$x++;
}
//print_r($message);
// calculate accurate file size for the "downloaded file"
foreach ($message as $line) {
$bytes .= strlen($line);
}
return $message;
}
function downloadCSV($data,$filename) {
header ("Content-Type: application/vnd.ms-excel");
header ("Content-disposition: attachment; filename=\"".$filename.".csv\"");
header ("Content-length: $bytes");
foreach ($data as $line) {
echo $line."\n";
}
}
function searchCSVKey($data,$searchkey) {
foreach ($data as $key=>$v) {
foreach ($v as $item) {
if ($item == $searchkey) {
$returnvalue = $data[$key];
$returnvalue['line'] = $key;
break 2;
}
}
}
if ($returnvalue == "") {
$returnvalue['0'] = "NULL";
$returnvalue['line'] = 0;
}
return $returnvalue;
}
function getPartialCSVAlpha($data,$start) {
// gets from $start to the end of the CSV. useful for searching
for ($x = $start + 1; $x <= count($data); $x++) {
$d[] = $data[$x];
}
return $d;
}
function getPartialCSVOmega($data,$finish) {
// 1 to the $finish of the CSV and return
for ($x = 0; $x < $finish; $x++) {
$d[] = $data[$x];
}
return $d;
}
function getSection($data,$startSearch,$finishSearch) {
$r1 = $this->searchCSVKey($data,$startSearch);
$data = $this->getPartialCSVAlpha($data,$r1['line']); // gets rid of the first section not needed.
$r2 = $this->searchCSVKey($data,$finishSearch);
$data = $this->getPartialCSVOmega($data,$r2['line']); // sections down the CSV.
return $data;
}
function getSectionByLine($data,$start,$finish) {
for ($x = $start; $x <= $finish; $x++) {
$output[] = $data[$x];
}
return $output;
}
function addValues3($array1,$array2) {
foreach ($array1 as $key1=>$val1) {
foreach ($val1 as $i=>$v) {
if ((is_numeric($array1[$key1][$i]) == TRUE) && ($i > 1)) {
$output[$key1][$i] = floatval($array2[$key1][$i]) + floatval($array1[$key1][$i]);
} else {
$output[$key1][$i] = $array1[$key1][$i];
}
}
}
return $output;
}
}
// </eof> //
?>
Hope it helps all :)
Verlustmeldung at gmx dot de
24-May-2008 10:59
24-May-2008 10:59
i wrote a decent litte class which is able to import excel 2003 .csv files kickass fast and easy. havent tested it with other excel versions, but they should work as well.
<?php
class read_csv {
function read_csv () {
// nothing
}
function read_csv_run($f="") {
if ( $f AND is_file($f) ) {
// set excel type delimiter, etc
$delimiter = ';';
$enclosure = '"';
// read file & parse
$input = file($f);
$csv = array();
foreach ( $input as $key => $value ) {
// rtrim crap at the end of the string
$tmp = explode($delimiter,rtrim($value));
// parse
$in_quote = false;
$arr = array();
foreach ( $tmp as $key => $value ) {
if ( $in_quote ) {
if ( $this->read_csv_has_quote($value,$enclosure) ) {
$in_quote = false;
$value = substr_replace($value,'',-1,1);
}
$key = (count($arr)-1);
$arr[$key] .= $delimiter.$value; // continue last array element
} else {
if ( $this->read_csv_has_quote($value,$enclosure) ) {
$in_quote = true;
$value = substr_replace($value,'',0,1);
} else if ( substr($value,0,1) == $enclosure AND substr($value,-1,1) == $enclosure ) {
// string is quoted, remove quotes
$value = substr_replace($value,'',0,1); // start
$value = substr_replace($value,'',-1,1); // end
}
$arr[] = $value; // append to array
}
}
foreach ( $arr as $key => $value ) {
$arr[$key] = str_replace($enclosure.$enclosure,$enclosure,$value);
}
// append to array
$csv[] = $arr;
} // end foreach
echo nl2br(print_r($csv,1));
} // end if
} // end func
function read_csv_has_quote ($str="",$enc="") {
$c = substr_count($str,$enc);
if ( stristr(($c/2),".") ) {
return true;
}
}
} // end class
$csv =& new read_csv();
$csv->read_csv_run("katalog_excel_D.csv");
?>
mfg RR
skirkendall at NOSPAM dot dsl-only dot net
01-May-2008 04:55
01-May-2008 04:55
The array_flip() function is handy for converting column names to column numbers. Assuming the first row contains column names, you can simply read it via fgetcsv(); this will give you a number-indexed array of column names. Applying array_flip() converts that into a name-indexed array of column numbers.
The following example does this, and assumes that two of the columns are named "animal" and "sound" but does not make any assumption about where those columns are.
$fp = fopen($url, "r");
$names = array_flip(fgetcsv($fp, 1000));
while (($values = fgetcsv($fp, 1000)) !== FALSE) {
print "The ".$values[$names["animal"]]." says ".$values[$names["sound"]].".\n";
}
fclose($fp);
Philipp
19-Apr-2008 09:31
19-Apr-2008 09:31
With this modification the last item will be added to the array: "a","b","c" is transformed to array("a","b","c") - old function returned array("a","b")
<?php
/*
Modified function from user comment by Marcos Boyington / 06-Mar-2008 03:08
This is a pretty useful update/modification to the fgetcsv function, which allows for:
* Multiple-character/multibyte delim/enclosure/escape
* Multibyte values
* Escape character specification in < PHP5
* Escape character = delim character
* Direct reading from files without bloating memory too much
*/
define('BUFFER_READ_LEN', 4096);
function fgetcsv_ex($file_handle, $delim = ',', $enclosure = '"', $escape = '"') {
$fields = null;
$fldCount = 0;
$inQuotes = false;
$complete = false;
$search_chars_list = array('\r\n', '\n', '\r');
if ($delim && ($delim != ''))
$search_chars_list[] = $delim;
if ($enclosure && ($enclosure != '')) {
$search_chars_list[] = $enclosure;
$enclosure_len = strlen($enclosure);
} else
$enclosure_len = 0;
if ($escape && ($escape != '')) {
$search_chars_list[] = $escape;
$escape_len = strlen($escape);
} else
$escape_len = 0;
$search_regex = '/' . implode('|', $search_chars_list) . '/';
$cur_pos = 0;
$line = '';
$cur_value = '';
$in_value = false;
$last_value = 0;
while (! $complete) {
$read_result = fread($file_handle, BUFFER_READ_LEN);
if ($read_result) {
$line .= $read_result;
} else if (strlen($line) == 0) {
return null;
} else {
$line .= "\n";
}
$line_len = strlen($line);
while (true) {
if (! preg_match($search_regex, $line, $matches, PREG_OFFSET_CAPTURE, $cur_pos)) {
if ($read_result) {
// need more chars
break;
} else {
// Incomplete file
return null;
}
} else {
$non_escape = false;
$cur_char = $matches[0][0];
$cur_len = strlen($cur_char);
$new_pos = $matches[0][1];
if (($enclosure == $escape) && $in_value && ($cur_char == $escape)) {
// Escape char = enclosure char special handling
if (($new_pos + $cur_len + $enclosure_len) >= $line_len) {
// We need the next char
break;
}
$next_char = substr($line, $new_pos + $cur_len, $enclosure_len);
if ((! $enclosure) || ($next_char != $enclosure)) {
$non_escape = true;
}
}
$cur_pos = $new_pos;
if ($in_value && (! $non_escape)) {
$cur_value .= mb_substr($line, $last_value, $cur_pos - $last_value);
if ($cur_char == $escape) {
// Skip escape char
$cur_pos += $escape_len;
}
$last_value = $cur_pos;
} else if (($cur_char == "\n") || ($cur_char == "\r") || ($cur_char == "\r\n")) {
$blank_start_lines = ($cur_pos == 0);
++$cur_pos;
$cur_pos = $cur_pos + strspn($line, "\n\r", $cur_pos);
if (! $blank_start_lines) {
$complete = true;
} else {
$last_value = $cur_pos;
continue;
}
}
if ($cur_char == $delim || $complete) {
if (is_null($fields)) {
$fields = array();
}
$fields[] = $cur_value . trim(mb_substr($line, $last_value, $cur_pos - $last_value));
$last_value = $cur_pos + $cur_len;
$cur_value = '';
} else if ($cur_char == $enclosure) {
if ($in_value) {
$cur_value .= mb_substr($line, $last_value, $cur_pos - $last_value);
}
$last_value = $cur_pos + $cur_len;
$in_value = ! $in_value;
}
if ($complete) {
break;
}
$cur_pos += $cur_len;
}
}
}
fseek($file_handle, $cur_pos - strlen($line), SEEK_CUR);
return $fields;
}
?>
ohira atto web dotto de
08-Nov-2007 11:59
08-Nov-2007 11:59
Yet another tool to parse CSV data into a associated 2d array. However, when within quotes, newline characters are treated as data instead of syntax.
<?php
define('LF', "\n");
// Parse a CSV data to a associated 2D array
function csvToArray($data)
{
// output
$csv = array();
$line = array();
$fieldnames = array();
$got_fieldnames = false;
$escaped = false; // Flag: escape char
$quoted = false; // Flag: quoted string
$buffer = ''; // Buffer (quoted values)
$junk = ''; // Junk buffer (unquoted values)
$fieldname_index = 0;
for($i = 0; $i < strlen($data); $i++)
{
$char = $data[$i];
if($quoted)
{
if(($char == '\\') && ($escaped === false))
{
// Set flags
$escaped = true;
}
elseif(($char == '"') && ($escaped === false))
{
// Set flags
$quoted = false;
$escaped = false;
}
else
{
// Add char to buffer
$buffer .= $char;
// Set flags
$escaped = false;
}
}
else
{
if($char == LF) // Start a new line
{
if(strlen($buffer) > 0)
{
// Add buffer to line
if($got_fieldnames)
{
$line[$fieldnames[$fieldname_index]] = $buffer;
$fieldname_index++;
}
else
{
$fieldnames[] = $buffer;
}
// Clear buffer
$buffer = '';
}
else
{
$junk = trim($junk);
// Add junk to line (possible unquoted values?)
if($got_fieldnames)
{
$line[$fieldnames[$fieldname_index]] = $junk;
$fieldname_index++;
}
else
{
$fieldnames[] = $junk;
}
}
// Clear junk
$junk = '';
// Add line to CSV
if($got_fieldnames)
{
$csv[] = $line;
}
$got_fieldnames = true;
// Clear line
$line = array();
$fieldname_index = 0;
}
elseif($char == '"') // Start new value
{
// Set flags
$quoted = true;
}
elseif($char == ';')
{
if(strlen($buffer) > 0)
{
// Add buffer to line
if($got_fieldnames)
{
$line[$fieldnames[$fieldname_index]] = $buffer;
$fieldname_index++;
}
else
{
$fieldnames[] = $buffer;
}
// Clear buffer
$buffer = '';
}
else
{
$junk = trim($junk);
// Add junk to line (possible unquoted values?)
if($got_fieldnames)
{
$line[$fieldnames[$fieldname_index]] = $junk;
$fieldname_index++;
}
else
{
$fieldnames[] = $junk;
}
}
// Clear junk
$junk = '';
}
else // Add to junk char
{
$junk .= $char;
}
}
}
return $csv;
}
?>
Tim Henderson
04-Oct-2007 04:40
04-Oct-2007 04:40
Only problem with fgetcsv(), at least in PHP 4.x -- any stray slash in the data that happens to come before a double-quote delimiter will break it -- ie, cause the field delimiter to be escaped. I can't find a direct way to deal with it, since fgetcsv() doesn't give you a chance to manipulate the line before it reads it and parses it...I've had to change all occurrences of '\"' to '" in the file first before feeding ot to fgetcsv(). Otherwise this is perfect for that Microsoft-CSV formula, deals gracefully with all the issues.
marcus at synchromedia dot co dot uk
03-Oct-2007 07:44
03-Oct-2007 07:44
This is a minor fix to mortanon@gmail.com's CSVIterator. The original version would die if the last line of a file did not end in a line break and you called valid() inside the iterator loop because the file would have already been closed and thus feof() would have an invalid file pointer param.
<?php
/**
* @author mortanon@gmail.com
* @link http://uk.php.net/manual/en/function.fgetcsv.php
*/
class CsvIterator implements Iterator {
const ROW_SIZE = 4096;
/**
* The pointer to the cvs file.
* @var resource
* @access private
*/
private $filePointer = NULL;
/**
* The current element, which will
* be returned on each iteration.
* @var array
* @access private
*/
private $currentElement = NULL;
/**
* The row counter.
* @var int
* @access private
*/
private $rowCounter = NULL;
/**
* The delimiter for the csv file.
* @var str
* @access private
*/
private $delimiter = NULL;
/**
* This is the constructor.It try to open the csv file.The method throws an exception
* on failure.
*
* @access public
* @param str $file The csv file.
* @param str $delimiter The delimiter.
*
* @throws Exception
*/
public function __construct($file, $delimiter=',') {
try {
$this->filePointer = fopen($file, 'r');
$this->delimiter = $delimiter;
}
catch (Exception $e) {
throw new Exception('The file "'.$file.'" cannot be read.');
}
}
/**
* This method resets the file pointer.
*
* @access public
*/
public function rewind() {
$this->rowCounter = 0;
rewind($this->filePointer);
}
/**
* This method returns the current csv row as a 2 dimensional array
*
* @access public
* @return array The current csv row as a 2 dimensional array
*/
public function current() {
$this->currentElement = fgetcsv($this->filePointer, self::ROW_SIZE, $this->delimiter);
$this->rowCounter++;
return $this->currentElement;
}
/**
* This method returns the current row number.
*
* @access public
* @return int The current row number
*/
public function key() {
return $this->rowCounter;
}
/**
* This method checks if the end of file is reached.
*
* @access public
* @return boolean Returns true on EOF reached, false otherwise.
*/
public function next() {
if (is_resource($this->filePointer)) {
return !feof($this->filePointer);
}
return false;
}
/**
* This method checks if the next row is a valid row.
*
* @access public
* @return boolean If the next row is a valid row.
*/
public function valid() {
if (!$this->next()) {
if (is_resource($this->filePointer)) {
fclose($this->filePointer);
}
return false;
}
return true;
}
}
?>
daevid at daevid dot com
26-Sep-2007 10:39
26-Sep-2007 10:39
A much simpler way to map the heading/column names to the elements on each line. It also doesn't fill up one big array which could cause you to run out of memory on large datasets. This loads one at a time so you can process/insert to db/etc...
$handle = fopen('somefile.csv', 'r');
if ($handle)
{
set_time_limit(0);
//the top line is the field names
$fields = fgetcsv($handle, 4096, ',');
//loop through one row at a time
while (($data = fgetcsv($handle, 4096, ',')) !== FALSE)
{
$data = array_combine($fields, $data);
}
fclose($handle);
}
jszatmary at hotmail dot com
21-Aug-2007 05:06
21-Aug-2007 05:06
This function appears to assume that \" is an escaped quote - similar to "" - which may lead to incorrect results while reading some files. Found while running under PHP 5.1.6.
myrddin at myrddin dot myrddin
21-Jun-2007 11:16
21-Jun-2007 11:16
RE post by:- stinkyj at gmail dot com
02-Aug-2006 10:15
the enclosure param defaulting to " and giving a warning if it's an empty string makes this function nearly worthless. csv files do not always have the fields enclosed, and in those cases it doesn't work.
---------
I had the same problem with this as well, enclosure really should be possible to be made null.
However, perhaps a solution to the problem is to use "\n" as the enclosure character in fgetcsv. As far as I tested it seems to work out just fine. I was thinking of using "\0" but that may cause problems with some data files. If anyone knows of any issues that might crop up when using "\n" as enclosure, please post away. Thanks.
e at osterman dot com
13-Jun-2007 12:39
13-Jun-2007 12:39
A 5.2 way to lazily parse a single CSV line
function parseCSV($str, $delimiter = ',', $enclosure = '"', $len = 4096)
{
$fh = fopen('php://memory', 'rw');
fwrite($fh, $str);
rewind($fh);
$result = fgetcsv( $fh, $len, $delimiter, $enclosure );
fclose($fh);
return $result;
}
D Steer
11-Jun-2007 05:32
11-Jun-2007 05:32
Here is a simple to include the field names in the array. Altough this is very simple, it does the job fantastically
<?php
print_r(buildStock('stock.csv'));
function buildStock($File) {
$handle = fopen($File, "r");
$fields = fgetcsv($handle, 1000, ",");
while($data = fgetcsv($handle, 1000, ",")) {
$detail[] = $data;
}
$x = 0;
$y = 0;
foreach($detail as $i) {
foreach($fields as $z) {
$stock[$x][$z] = $i[$y];
$y++;
}
$y = 0;
$x++;
}
return $stock;
}
?>
anykey
24-May-2007 11:40
24-May-2007 11:40
final version...
<?php
private function parseCsvLine($str) {
$delimier = ';';
$qualifier = '"';
$qualifierEscape = '\\';
$fields = array();
while (strlen($str) > 0) {
if ($str{0} == $delimier)
$str = substr($str, 1);
if ($str{0} == $qualifier) {
$value = '';
for ($i = 1; $i < strlen($str); $i++) {
if (($str{$i} == $qualifier) && ($str{$i-1} != $qualifierEscape)) {
$str = substr($str, (strlen($value) + 2));
$value = str_replace(($qualifierEscape.$qualifier), $qualifier, $value);
break;
}
$value .= $str{$i};
}
} else {
$end = strpos($str, $delimier);
$value = ($end !== false) ? substr($str, 0, $end) : $str;
$str = substr($str, strlen($value));
}
$fields[] = $value;
}
return $fields;
}
?>
02-May-2007 12:07
a flexible parser that can be used for csv or tsv (or any delimited flatfile data source).
<?php
/* assumes a single line of input; automatically determines the number of fields */
function parse_line($input_text, $delimiter = ',', $text_qualifier = '"') {
$text = trim($input_text);
if(is_string($delimiter) && is_string($text_qualifier)) {
$re_d = '\x' . dechex(ord($delimiter)); //format for regexp
$re_tq = '\x' . dechex(ord($text_qualifier)); //format for regexp
$fields = array();
$field_num = 0;
while(strlen($text) > 0) {
if($text{0} == $text_qualifier) {
preg_match('/^' . $re_tq . '((?:[^' . $re_tq . ']|(?<=\x5c)' . $re_tq . ')*)' . $re_tq . $re_d . '?(.*)$/', $text, $matches);
$value = str_replace('\\' . $text_qualifier, $text_qualifier, $matches[1]);
$text = trim($matches[2]);
$fields[$field_num++] = $value;
} else {
preg_match('/^([^' . $re_d . ']*)' . $re_d . '?(.*)$/', $text, $matches);
$value = $matches[1];
$text = trim($matches[2]);
$fields[$field_num++] = $value;
}
}
return $fields;
} else {
return false;
}
}
?>
Bob
29-Apr-2007 02:19
29-Apr-2007 02:19
Thank you to the mystery contributor of csv_string_to_array function:
http://uk3.php.net/manual/en/function.fgetcsv.php#62524
This works great when your CSV data has literal commas inside enclosures that you want to preserve, fgetcsv fails at this & interprets comma as end of item even without ending enclosure.
r at smagoo dot ch
11-Apr-2007 05:00
11-Apr-2007 05:00
If you had a problem with fgetcsv and multibyte characters, you have to set the correct local setting:
<?php
setlocale(LC_ALL, 'en_US.UTF-8');
?>
Change it to your local settings and/or charset.
pinkgothic at gmail dot com
03-Apr-2007 04:47
03-Apr-2007 04:47
I find the documentation mildly misleading:
fgetcsv() does not - as this documentation seemingly claims in the descriptive line - get a line out of the file (via the file pointer) and then parses this for CSV fields, but instead retrieves a CSV row out of the file, which it then splits into an array.
The difference may seem trivial, but reading the description of this function I feared it might not support linebreaks in individual CSV values. Testing, however, revealed that fgetcsv() [fortunately!] works as one would expect from a CSV parser, and my fears were without cause.
In fact, fgetcsv() is remarkably hard to break. It's not confused by the value """,""" for example (three quotation marks followed by a comma followed by three quotation marks - which represents the value "quotation mark, comma, quotation mark" in case it's not immediately obvious).
I hope this extra documentation is helpful for someone.
eoj at seznam dot cz
13-Mar-2007 10:09
13-Mar-2007 10:09
I had a problem with fgetcsv and multibyte characters so i used one of functions below (16-Nov-2002 04:01 to be specific) and modified it to be (hopefully) multibyte safe.
<?php
/**
* @param the csv line to be split
* @param the delimiter to split by (default ';' )
* @param if this is false, the quotation marks won't be removed from the fields (default true)
*/
function mb_csv_split($line, $delim = ';', $removeQuotes = true) {
$fields = array();
$fldCount = 0;
$inQuotes = false;
for ($i = 0; $i < mb_strlen($line); $i++) {
if (!isset($fields[$fldCount])) $fields[$fldCount] = "";
$tmp = mb_substr($line, $i, mb_strlen($delim));
if ($tmp === $delim && !$inQuotes) {
$fldCount++;
$i+= mb_strlen($delim) - 1;
}
else if ($fields[$fldCount] == "" && mb_substr($line, $i, 1) == '"' && !$inQuotes) {
if (!$removeQuotes) $fields[$fldCount] .= mb_substr($line, $i, 1);
$inQuotes = true;
}
else if (mb_substr($line, $i, 1) == '"') {
if (mb_substr($line, $i+1, 1) == '"') {
$i++;
$fields[$fldCount] .= mb_substr($line, $i, 1);
} else {
if (!$removeQuotes) $fields[$fldCount] .= mb_substr($line, $i, 1);
$inQuotes = false;
}
}
else {
$fields[$fldCount] .= mb_substr($line, $i, 1);
}
}
return $fields;
}
?>
31-Jan-2007 06:23
just another csv file parse function
<?php
define('CSV_BOTH', 1);
define('CSV_ASSOC', 2);
define('CSV_NUM', 3);
function parse_csv($filename, $result_type = CSV_BOTH) {
if(!file_exists($filename)) {
die("file (" . $filename . ") does not exist\n");
}
$lines = file($filename);
$title_line = trim(array_shift($lines));
$titles = split(",", $title_line);
$records = array();
foreach($lines as $line_num => $line) {
$subject = trim($line);
$fields = array();
for($field_num = 0; $field_num < count($titles); $field_num++) {
if($subject{0} == '"') {
preg_match('/^"(([^"]|\\")*)",?(.*)$/', $subject, $matches);
$value = $matches[1];
$subject = $matches[3];
if($result_type == CSV_BOTH || $result_type == CSV_ASSOC) {
$fields[$titles[$field_num]] = $value;
}
if($result_type == CSV_BOTH || $result_type == CSV_NUM) {
$fields[$field_num] = $value;
}
} else {
preg_match('/^([^,]*),?(.*)$/', $subject, $matches);
$value = $matches[1];
$subject = $matches[2];
if($result_type == CSV_BOTH || $result_type == CSV_ASSOC) {
$fields[$titles[$field_num]] = $value;
}
if($result_type == CSV_BOTH || $result_type == CSV_NUM) {
$fields[$field_num] = $value;
}
}
}
$records[] = $fields;
}
return $records;
}
?>
Mr N.
05-Jan-2007 11:23
05-Jan-2007 11:23
There is still a bug with column headings ( "false" != false )
<?php
function parse_csv_file($file, $columnheadings = false, $delimiter = ',', $enclosure = "\"") {
$row = 1;
$rows = array();
$handle = fopen($file, 'r');
while (($data = fgetcsv($handle, 1000, $delimiter, $enclosure )) !== FALSE) {
if (!($columnheadings == false) && ($row == 1)) {
$headingTexts = $data;
} elseif (!($columnheadings == false)) {
foreach ($data as $key => $value) {
unset($data[$key]);
$data[$headingTexts[$key]] = $value;
}
$rows[] = $data;
} else {
$rows[] = $data;
}
$row++;
}
fclose($handle);
return $rows;
}
?>
lj at matter dot dk
06-Nov-2006 04:15
06-Nov-2006 04:15
There was a bug with the column headings
function parse_csv_file($file, $columnheadings = false, $delimiter = ',', $enclosure = "\"") {
$row = 1;
$rows = array();
$handle = fopen($file, 'r');
while (($data = fgetcsv($handle, 1000, $delimiter, $enclosure )) !== FALSE) {
if (!($columnheadings == "false") && ($row == 1)) {
$headingTexts = $data;
} elseif (!($columnheadings == "false")) {
foreach ($data as $key => $value) {
unset($data[$key]);
$data[$headingTexts[$key]] = $value;
}
$rows[] = $data;
} else {
$rows[] = $data;
}
$row++;
}
fclose($handle);
return $rows;
}
aruggirello, at, tiscali, d0t it
10-Oct-2006 07:20
10-Oct-2006 07:20
Whenever you have to parse CSV data that isn't stored in a file, or should you need to encode your data as CSV, you may use my set of functions, featuring:
- bi-directional conversion, meaning you may read as well as write CSV data, to or from your arrays.
- Support for customizable field separator, newline character (also auto-detects and converts Win/Mac/*nix newlines).
- separators, newlines (auto-converted), properly handled within fields enclosed in quotation marks. Last line may or may not be terminated by a newline.
- checks for possibly corrupt data (whenever last field in last row starts, but is not terminated, with a quotation mark).
- Top speed guaranteed by use of strpos(), substr() etc.; room yet for some optimization (especially with associative arrays).
- Clean (no data stored or retrieved from outside function scope, except for CSVstruct array).
Requires PHP 4.3 or newer.
Enjoy!
Andrea Ruggirello
souce available at:
http://web.tiscali.it/caelisoft/provacsv.txt
spam at cyber-space dot nl
07-Sep-2006 04:23
07-Sep-2006 04:23
I needed a fast/robust csv parser in PHP that could handle unix-, windows- and mac-style linebreaks.
i takes a csv-string as input and outputs a multidimensional array with lines and fields.
<?php
function parse_csv_php(&$data,$delim=',',$enclosure='"')
{
$enclosed=false;
$fldcount=0;
$linecount=0;
$fldval='';
for($i=0;$i<strlen($data);$i++)
{
$chr=$data{$i};
switch($chr)
{
case $enclosure:
if($enclosed&&$data{$i+1}==$enclosure)
{
$fldval.=$chr;
++$i; //skip next char
}
else
$enclosed=!$enclosed;
break;
case $delim:
if(!$enclosed)
{
$ret_array[$linecount][$fldcount++]=$fldval;
$fldval='';
}
else
$fldval.=$chr;
break;
case "\\r":
if(!$enclosed&&$data{$i+1}=="\\n")
continue;
case "\\n":
if(!$enclosed)
{
$ret_array[$linecount++][$fldcount]=$fldval;
$fldcount=0;
$fldval='';
}
else
$fldval.=$chr;
break;
default:
$fldval.=$chr;
}
}
if($fldval)
$ret_array[$linecount][$fldcount]=$fldval;
return $ret_array;
}
?>
Skakunov Alexander <i1t2b3 at gmail dot com>
24-Aug-2006 01:42
24-Aug-2006 01:42
If you need to import a huge CSV file into a database, use the bulk insert technique instead of many line-by-line inserts:
- MSSQL: bcp tool and "BULK INSERT" SQL
- MySQL: mysqlimport tool and "LOAD DATA INFILE" SQL
As for MySQL, you can use the ready "Quick CSV import" class at http://a4.users.phpclasses.org/browse/package/2917.html
stinkyj at gmail dot com
02-Aug-2006 05:15
02-Aug-2006 05:15
the enclosure param defaulting to " and giving a warning if it's an empty string makes this function nearly worthless. csv files do not always have the fields enclosed, and in those cases it doesn't work.
myrddin at myrddin dot myrddin
18-Jul-2006 07:14
18-Jul-2006 07:14
Here is a OOP based importer similar to the one posted earlier. However, this is slightly more flexible in that you can import huge files without running out of memory, you just have to use a limit on the get() method
Sample usage for small files:-
-------------------------------------
$importer = new CsvImporter("small.txt",true);
$data = $importer->get();
print_r($data);
Sample usage for large files:-
-------------------------------------
$importer = new CsvImporter("large.txt",true);
while($data = $importer->get(2000))
{
print_r($data);
}
And heres the class:-
-------------------------------------
class CsvImporter
{
private $fp;
private $parse_header;
private $header;
private $delimiter;
private $length;
//--------------------------------------------------------------------
function __construct($file_name, $parse_header=false, $delimiter="\t", $length=8000)
{
$this->fp = fopen($file_name, "r");
$this->parse_header = $parse_header;
$this->delimiter = $delimiter;
$this->length = $length;
$this->lines = $lines;
if ($this->parse_header)
{
$this->header = fgetcsv($this->fp, $this->length, $this->delimiter);
}
}
//--------------------------------------------------------------------
function __destruct()
{
if ($this->fp)
{
fclose($this->fp);
}
}
//--------------------------------------------------------------------
function get($max_lines=0)
{
//if $max_lines is set to 0, then get all the data
$data = array();
if ($max_lines > 0)
$line_count = 0;
else
$line_count = -1; // so loop limit is ignored
while ($line_count < $max_lines && ($row = fgetcsv($this->fp, $this->length, $this->delimiter)) !== FALSE)
{
if ($this->parse_header)
{
foreach ($this->header as $i => $heading_i)
{
$row_new[$heading_i] = $row[$i];
}
$data[] = $row_new;
}
else
{
$data[] = $row;
}
if ($max_lines > 0)
$line_count++;
}
return $data;
}
//--------------------------------------------------------------------
}
Ariel asphp at dsgml dot com
01-Jun-2006 01:52
01-Jun-2006 01:52
Should you need it, here is a nice and simple function for escaping csv fields properly.
This version is conditional - it only adds quotes if needed:
<?
function csv_escape($str) {
$str = str_replace(array('"', ',', "\n", "\r"), array('""', ',', "\n", "\r"), $str, &$count);
if($count) {
return '"' . $str . '"';
} else {
return $str;
}
}
?>
This version is even simpler, but adds quotes even if not needed.
<?
function csv_escape($str) {
return = '"' . str_replace('"','""', $str) . '"';
}
?>
jon at jonhassall dot com
25-May-2006 07:54
25-May-2006 07:54
I modified the code for my own purposes, to return an array with named keys for each field. I tried various alternatives, and this seems to work well with exported Excel data.
<pre><?php
//Move through a CSV file, and output an associative array for each line
ini_set("auto_detect_line_endings", 1);
$current_row = 1;
$handle = fopen("testdatasource.csv", "r");
while ( ($data = fgetcsv($handle, 10000, ",") ) !== FALSE )
{
$number_of_fields = count($data);
if ($current_row == 1)
{
//Header line
for ($c=0; $c < $number_of_fields; $c++)
{
$header_array[$c] = $data[$c];
}
}
else
{
//Data line
for ($c=0; $c < $number_of_fields; $c++)
{
$data_array[$header_array[$c]] = $data[$c];
}
print_r($data_array);
}
$current_row++;
}
fclose($handle);
//Look at my photos www.jonhassall.com !
?></pre>
Jon Hassall
br0derbund at hotmail
19-May-2006 06:32
19-May-2006 06:32
Thank you donnoman and ramdac, that's exactly the kind of function I needed!
For my use, assigning metadata to images, I actually want the first dimension array key to be a string pulled from the CSV; the unique filename, instead of a number.
Easy, just insert the second dim's key...
Change: $return[]=$row;
To: $return[$data[0]]=$row;
(You may indeed wish to confirm unique values first!)
Also, if you find the fields getting out of sync, increase the $len byte count for the largest cell of data.
simone.sanfratello at tiscali dot it
17-Mar-2006 04:30
17-Mar-2006 04:30
to get an array with data from the MS Excel csv format (separated by ; and with string wich contains ; or " optionally delimited by " )
function getcsvxls($buffer)
{
$buffer = str_replace('""', '"', $buffer);
$n = strlen($buffer);
$i = $line = 0;
$del = false;
while($i < $n)
{
$part = substr($buffer, $i);
if(
(substr($part, 0, 1) == ';' && !$del) ||
(substr($part, 0, 2) == '";' && $del)
)
{
$i ++;
if($del)
{
$str = substr($str, 1, strlen($str) - 1);
$i ++;
}
$data[$line][] = $str;
$del = false;
$str = '';
} else if(substr($part, 0, 2) == "\r\n")
{
$data[$line][] = $str;
$str = '';
$del = false;
$line ++;
$i += 2;
} else
{
if($part[0] == '"')
$del = true;
$str .= $part[0];
$i ++;
}
}
return $data;
}
daniel at softel dot jp
09-Mar-2006 05:03
09-Mar-2006 05:03
Note that fgetcsv() uses the system locale setting to make assumptions about character encoding.
So if you are trying to process a UTF-8 CSV file on an EUC-JP server (for example),
you will need to do something like this before you call fgetcsv():
setlocale(LC_ALL, 'ja_JP.UTF8');
[Also not that setlocale() doesn't *permanently* affect the system locale setting]
02-Mar-2006 04:16
For those of you who need to get CSV data from a single line of quoted CSV (comma delimited) values (that may have commas in the data), use this:
<?
function csv_string_to_array($str){
$expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
$results=preg_split($expr,trim($str));
return preg_replace("/^\"(.*)\"$/","$1",$results);
}
$str=<<<EOF
"testing, stuff, here","is testing ok",200,456
EOF;
print_r(csv_string_to_array($str));
?>
abu1980 at yahoo dot com
28-Feb-2006 07:18
28-Feb-2006 07:18
I was using the function to import a csv file that had some values with "\" included this confused the import function to ignore the End of line and hence an incorrect number of rows is returned to me.
Suggest you try to replace that \ and { brackets as well since they have the same function of an escape character before u import the function
thanks
28-Feb-2006 01:05
beware of characters of binary value == 0, as they seem to make fgetcsv ignore the remaining part of a line where they appear.
Maybe this is normal under some convention I don't know, but a file exported from Excel had those as values for some cells *sometimes*, thus fgetcsv return variable cell counts for different lines.
i'm using php 4.3
donnoman at donovanbray dot com
23-Nov-2005 05:48
23-Nov-2005 05:48
/**
* Based on an example by ramdac at ramdac dot org
* Returns a multi-dimensional array from a CSV file optionally using the
* first row as a header to create the underlying data as associative arrays.
* @param string $file Filepath including filename
* @param bool $head Use first row as header.
* @param string $delim Specify a delimiter other than a comma.
* @param int $len Line length to be passed to fgetcsv
* @return array or false on failure to retrieve any rows.
*/
function importcsv($file,$head=false,$delim=",",$len=1000) {
$return = false;
$handle = fopen($file, "r");
if ($head) {
$header = fgetcsv($handle, $len, $delim);
}
while (($data = fgetcsv($handle, $len, $delim)) !== FALSE) {
if ($head AND isset($header)) {
foreach ($header as $key=>$heading) {
$row[$heading]=(isset($data[$key])) ? $data[$key] : '';
}
$return[]=$row;
} else {
$return[]=$data;
}
}
fclose($handle);
return $return;
}
junk at vhd dot com dot au
14-Nov-2005 02:46
14-Nov-2005 02:46
Based on my observations a few comments below I have written a class that incorporates all these features. Here is the class description:
----------
This class will parse a csv file in either standard or MS Excel format.
Two methods are provided to either process a line at a time or return the whole csv file as an array.
It can deal with:
- Line breaks within quoted fields
- Character seperator (usually a comma or semicolon) in quoted fields
- Can leave or remove leading and trailing spaces or tabs
- Can leave or skip empty rows.
- Windows and Unix line breaks dealt with automatically. (Care must be taken with Macintosh format.)
Also, the escape character is automatically removed.
-----------
So basically it should "just work". If it doesn't please send me an email ;-)
You can download it from: http://www.phpclasses.org/browse/package/2672.html
An example on how to use the class and a test.csv file are also provided.
tokai at binaryriot dot com
08-Nov-2005 12:18
08-Nov-2005 12:18
Newer PHP versions handle cvs files slightly different than older versions.
"Max Mustermann"|"Muster Road 34b"|"Berlin" |"Germany"
"Sophie Master" |"Riverstreet" |"Washington"|"USA"
The extra spaces behind a few fields in the example (which are useful, when you manually manage a small csv database to align the columns) were ignored by fgetcsv from PHP 4.3. With the new 4.4.1 release they get appended to the string, so you end up with "Riverstreet " instead the expected "Riverstreet".
Easy workaround is to just trim all fields after reading them in.
while ( $data = fgetcsv($database, 32768, "|") )
{
$i = 0;
while(isset($data[$i]))
{
$data[$i] = rtrim($data[$i]);
$i++;
}
....
}
junk at vhd dot com dot au
25-Oct-2005 07:52
25-Oct-2005 07:52
The fgetcsv function seems to follow the MS excel conventions, which means:
- The quoting character is escaped by itself and not the back slash.
(i.e.Let's use the double quote (") as the quoting character:
Two double quotes "" will give a single " once parsed, if they are inside a quoted field (otherwise neither of them will be removed).
\" will give \" whether it is in a quoted field or not (same for \\) , and
if a single double quote is inside a quoted field it will be removed. If it is not inside a quoted field it will stay).
- leading and trailing spaces (\s or \t) are never removed, regardless of whether they are in quoted fields or not.
- Line breaks within fields are dealt with correctly if they are in quoted fields. (So previous comments stating the opposite are wrong, unless they are using a different PHP version.... I am using 4.4.0.)
So fgetcsv if actually very complete and can deal with every possible situation. (It does need help for macintosh line breaks though, as mentioned in the help files.)
I wish I knew all this from the start. From my own benchmarks fgetcsv strikes a very good compromise between memory consumption and speed.
-------------------------
Note: If back slashes are used to escape quotes they can easily be removed afterwards. Same for leading and trailing spaces.
mortanon at gmail dot com
14-Oct-2005 06:05
14-Oct-2005 06:05
Hier is an example for a CSV Iterator.
<?php
class CsvIterator implements Iterator
{
const ROW_SIZE = 4096;
/**
* The pointer to the cvs file.
* @var resource
* @access private
*/
private $filePointer = null;
/**
* The current element, which will
* be returned on each iteration.
* @var array
* @access private
*/
private $currentElement = null;
/**
* The row counter.
* @var int
* @access private
*/
private $rowCounter = null;
/**
* The delimiter for the csv file.
* @var str
* @access private
*/
private $delimiter = null;
/**
* This is the constructor.It try to open the csv file.The method throws an exception
* on failure.
*
* @access public
* @param str $file The csv file.
* @param str $delimiter The delimiter.
*
* @throws Exception
*/
public function __construct($file, $delimiter=',')
{
try {
$this->filePointer = fopen($file, 'r');
$this->delimiter = $delimiter;
}
catch (Exception $e) {
throw new Exception('The file "'.$file.'" cannot be read.');
}
}
/**
* This method resets the file pointer.
*
* @access public
*/
public function rewind() {
$this->rowCounter = 0;
rewind($this->filePointer);
}
/**
* This method returns the current csv row as a 2 dimensional array
*
* @access public
* @return array The current csv row as a 2 dimensional array
*/
public function current() {
$this->currentElement = fgetcsv($this->filePointer, self::ROW_SIZE, $this->delimiter);
$this->rowCounter++;
return $this->currentElement;
}
/**
* This method returns the current row number.
*
* @access public
* @return int The current row number
*/
public function key() {
return $this->rowCounter;
}
/**
* This method checks if the end of file is reached.
*
* @access public
* @return boolean Returns true on EOF reached, false otherwise.
*/
public function next() {
return !feof($this->filePointer);
}
/**
* This method checks if the next row is a valid row.
*
* @access public
* @return boolean If the next row is a valid row.
*/
public function valid() {
if (!$this->next()) {
fclose($this->filePointer);
return false;
}
return true;
}
}
?>
Usage :
<?php
$csvIterator = new CsvIterator('/path/to/csvfile.csv');
foreach ($csvIterator as $row => $data) {
// do somthing with $data
}
?>
Vladimir Gruzdev
20-Sep-2005 06:09
20-Sep-2005 06:09
"cristian DOT zuddas AT gmail DOT com" has posted the corrected function "CSV2Array", but all the same there's an error. For example, function incorrectly processes a simple csv-file from "MS Excel" (http://wsCat.ka.pp.ru/test.csv) in which fields any way contain quotes and CR/LF . I has written working function which has successfully applied to import of the greater production catalog.
<?php
// --------- Test ---------------------------------------
$str=file_get_contents('http://wsCat.ka.pp.ru/test.csv');
$rows=CSV2Array($str);
echo '<table border="1" cellSpacing="2" cellPadding="2">';
for($i=0;$i<count($rows);$i++)
{
echo '<tr>';
for ($j=0;$j<count($rows[$i]);$j++)
{
echo '<td>'.$rows[$i][$j]. '</td>';
}
echo '</tr>';
}
echo '</table>';
// -----------------------------------------------------
/**
*function CSV2Array
*Convert CSV-text to 2d-array
*(delimeter = ';', line ending = '\r\n' - only for windows platform)
* @param string $query (query to database)
* @return array
*/
function CSV2Array($content)
{
if ($content{strlen($content)-1}!="\r" && $content{strlen($content)-1}!="\n")
$content .= "\r\n";
$arr=array();
$temp=$content;
$tma=array();
while (strlen($temp)>0)
{
if ($temp{0}=='"')
{
$temp=substr($temp,1);
$str='';
while (1)
{
$matches=array();
if (!preg_match('/^(.*?)"("*?)(;|\r\n)(.*)$/is',$temp,$matches))
return $arr;
$temp=$matches[4];
if (fmod(strlen($matches[2]),2)>0)
{
$str.=$matches[1].$matches[2].'"'.$matches[3];
continue;
}
else
{
$tma[]=preg_replace('/""/','"',$str.$matches[1].$matches[2]);
if ($matches[3]!=';')
{
$arr[]=$tma;
$tma=array();
}
break;
}
}
}
else
{
$matches=array();
if (!preg_match('/^([^;\r\n]*)(;|\r\n)(.*)$/is',$temp,$matches))
return $arr;
$tma[]=$matches[1];
$temp=$matches[3];
if ($matches[2]!=';')
{
$arr[]=$tma;
$tma=array();
}
}
}
return $arr;
}
?>
at lapstore doot de the webmaster
26-Aug-2005 05:16
26-Aug-2005 05:16
Some of you might need this:
CSV-Parser optimized for -speed- which can handle linebreaks in quoted strings
This parser can not handle unquoted data containing quotes ("), i.e. apple, ban"ana, cherry
<?php
// csv.inc.php
//
// Usage:
// use argument chopnl=true to autoconvert linebreaks in strings to spaces
$csv_data = "apple; banana; \"multi-\nline\";\"string with \"\" quoted quotes\";\n";
$csv_data .= "second;line;with;trailing;colon\n";
$csv_data .= "third;line;without;trailing;colon;";
$prepared = csv_prepare($csv_data);
while ($myrow = csv_getrow(&$prepared, false)) {
echo "Row ".csv_rownumber(&$prepared).": ";
for ($i = 0;$i < $myrow["c"]; $i++) echo "[".htmlspecialchars($myrow[$i])."]";
echo "<BR>\n";
}
// ******** public functions ********
function csv_prepare($data) {
$rc = array();
$rc[0] = strtr($data,array("\r\n" => "\n"));
$rc[0] = strtr($rc[0],array("\r" => "\n"));
$rc[1] = 0;
$rc[2] = strlen($rc[0]);
$rc[3] = 0;
return $rc;
}
// data[0] = csv-data
// data[1] = startindex
// data[2] = total length csv
// data[3] = currentrow starting from 1 (only for informational purposes)
function csv_rownumber(&$data) {
return $data[3];
}
function csv_getrow(&$data, $chopnl) {
$data[3] += 1;
$rc = array();
$line = csv__getline(&$data);
$line = trim($line);
$len = strlen($line);
$start = 0;
if ($len == 0 && $data[1] >= $data[2]) {
return false;
}
$iter = 0;
while ($iter < 500) {
$item = csv__getitem(&$line, $chopnl, &$start, $len);
$rc[$iter] = $item;
$iter += 1;
if ($start >= $len) break;
}
$rc["c"] = $iter;
return $rc;
}
// ************ Internals *********
function csv__getline(&$data) {
$sep = csv__extract(&$data[0], "\n", $data[1], $data[2]);
$oi = $data[1];
$data[1] = $sep + 1;
return substr($data[0],$oi,$sep - $oi);
}
function csv__getitem(&$data, $chopnl, &$start, $len) {
$sep = csv__extract(&$data, ";",$start, $len);
$rc = trim(substr($data,$start,$sep - $start));
$start = $sep + 1;
if (substr($rc,0,1) == "\"") {
if (substr($rc,strlen($rc) - 1,1) == "\"") {
$rc = substr($rc,1,strlen($rc) - 2);
}
}
$rc = strtr($rc,array("\"\"" => "\""));
if ($chopnl) {
$rc = strtr($rc,array("\n" => " "));
}
return trim($rc);
}
function csv__extract(&$data, $sep, $startidx, $len) {
$instr = false;
for ($i=$startidx;$i < $len; $i+=1) {
$c = $data[$i];
if ($c == "\"") {
if ($instr) {
$c2 = ($i < $len - 1) ? $data[$i + 1] : "";
if ($c2 == "\"") {
$i += 1;
continue;
} else {
$instr = false;
}
} else {
$instr = true;
}
} elseif ($c == $sep) {
if ($instr) {
continue;
} else {
return $i;
}
}
}
return $len;
}
?>
andychr17 at hotmail dot com
05-Aug-2005 10:30
05-Aug-2005 10:30
The man below suggested this to avoid and endless loop from using "!== FALSE":
while (($data = fgetcsv($handle, 1000, ",")) != FALSE) {
This is redundant, if you want to do it with the != you may as well do:
while ($data = fgetcsv($handle, 1000, ",")) {
essaym.net
29-Jun-2005 10:17
29-Jun-2005 10:17
Heres a function i wrote because all the other functions on here didnt work quiet as easy as I wanted them to:
It opens the CSV file, and reads it into a 3d array. If you set $columnsOnly, you will only get the first line.
<?php
function CSV2Array($openFile, $columnsOnly = false)
{
$handle = fopen ($openFile,"r");
$rows = 0;
while (!feof($handle)) {
$columns[] = explode(",", fgets($handle, 4096));
if ($rows++ == 0 && $columnsOnly)
break;
}
fclose ($handle);
return $columns;
}
?>
cristian DOT zuddas AT gmail DOT com
17-Jun-2005 04:58
17-Jun-2005 04:58
"marc at henklein dot com" posted a cool function "CSV2Array", but there's an error. If the last char of the CSV file isn't a carriage return, the function can't take the last field of the last line in the CSV.
Fixed adding this check:
<?
if ($content[strlen($content)-1]!="\r" && $content[strlen($content)-1]!="\n")
$content .= "\r\n";
?>
The updated function:
<?
function CSV2Array($content, $delim = ';', $encl = '"', $optional = 1) {
if ($content[strlen($content)-1]!="\r" && $content[strlen($content)-1]!="\n")
$content .= "\r\n";
$reg = '/(('.$encl.')'.($optional?'?(?(2)':'(').
'[^'.$encl.']*'.$encl.'|[^'.$delim.'\r\n]*))('.$delim.
'|\r\n)/smi';
preg_match_all($reg, $content, $treffer);
$linecount = 0;
for ($i = 0; $i<=count($treffer[3]);$i++) {
$liste[$linecount][] = $treffer[1][$i];
if ($treffer[3][$i] != $delim)
$linecount++;
}
unset($linecount);
unset($i);
unset($reg);
unset($content);
unset($delim);
unset($encl);
unset($optional);
unset($treffer);
return $liste;
}
?>
Bart
17-Jun-2005 02:08
17-Jun-2005 02:08
The file function reads the file in an array with the EOL still attached, so the +1 is not necessary.
Bob Kolk
17-May-2005 07:50
17-May-2005 07:50
Dave Meiners above example is great, except that you need to need to add one to the final length to account for EOL. So it'd be:
while ($data = fgetcsv($handle, ($length+1), ","))
marc at henklein dot com
21-Feb-2005 02:57
21-Feb-2005 02:57
for excel-csv-sheets like:
test1;test2;test3;test4;"test5
test5b
test5c";test6
(optionally enclosed by " including line-breaks)
.. i wrote a little function to solve this problem.
<?php
function CSV2Array($content, $delim = ';', $encl = '"', $optional = 1)
{
$reg = '/(('.$encl.')'.($optional?'?(?(2)':'(').
'[^'.$encl.']*'.$encl.'|[^'.
$delim.'\r\n]*))('.$delim.'|\r\n)/smi';
preg_match_all($reg, $content, $treffer);
$linecount = 0;
for ($i = 0; $i<=count($treffer[3]);$i++)
{
$liste[$linecount][] = $treffer[1][$i];
if ($treffer[3][$i] != $delim)
$linecount++;
}
return $liste;
}
// usage for example:
$content = join('',file('test.csv'));
$liste = CSV2Array($content);
print_r($liste);
?>
ramdac at ramdac dot org
24-Jan-2005 07:40
24-Jan-2005 07:40
Be wary of Example #1 above.
If the file doesn't exist, the application will spit out data to STDOUT and could fill up /tmp space if you're not careful. The output might will look like this:
PHP Warning: fgetcsv(): supplied argument is not a valid stream resource in...(your filename here)
To get around this, do the following:
<?
row = 1;
if(!$handle = fopen("test.csv", "r"))
{
print 'could not open file. quitting';
die;
}
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);
?>
Daffy
22-Nov-2004 04:25
22-Nov-2004 04:25
This is a little sniplet for all users who has problems with Excel csv files. This functions does almost the same like the linux shell program dos2unix.
/* ------------------8<------------------------*/
function dos2unix ($filename) {
//open original file
$fp = fopen($filename,'r');
//open original file
$fptmp = fopen($filename.'_tmp','w');
while(!feof($fp)){
$line = chop(fgets($fp,4096));
$ret = ereg_replace(chr(13) . chr(10),"\n",$line);
$ret = ereg_replace(chr(13),"\n",$ret);
fwrite($fptmp,$ret);
}
fclose($fp);
fclose($fptmp);
//remove original file
unlink($filename);
//move converted file to old filename
copy($filename.'_tmp', $filename);
//remove temp file
unlink($filename.'_tmp');
}
/* ------------------8<------------------------*/
18-Nov-2004 08:47
example 1 above goes into an infinte loop if the file name is bad because fgetcsv return a false result not identical to false but equl to false
to fix this use != instead of !== in the test as shown below it will still work correctly for the case when the file exists.
while (($data = fgetcsv($handle, 1000, ",")) != FALSE) {
mickoz[at]parodius[dot]com
31-Oct-2004 01:54
31-Oct-2004 01:54
In "php at dogpoop dot cjb dot net" post, I would change in his function:
function csv_split($line,$delim=',',$removeQuotes=true)
this code:
if ($line[$i+1] == '"') {
to:
if ($i+1 < strlen($line) && $line[$i+1] == '"') {
The reason is that if the quote (") is at the last line of the string, then it will try to reach an undefined space in the string, therefore giving a PHP error.
Of course his function is great if your line is a true compatible csv line, but I leave that to you to judge if you can do this assumption ;-)
aidan at php dot net
01-Jul-2004 04:47
01-Jul-2004 04:47
If you're looking to parse CSV files but find fgetcvs insufficient, check out http://pear.php.net/package/File
dave146 at burtonsys dot com
18-Jun-2004 11:29
18-Jun-2004 11:29
To convert .dbf files (dBase/xBase/FoxPro/Clipper/etc.) files
to .csv, so that you can read them with fgetcsv, get dbf2csv.zip
from http://www.burtonsys.com/downloads.html
(It seems odd to me that php has fgetcsv but no splitcsv.)
-Dave
jc at goetc dot net
17-Jun-2004 06:51
17-Jun-2004 06:51
I've had alot of projects recently dealing with csv files, so I created the following class to read a csv file and return an array of arrays with the column names as keys. The only requirement is that the 1st row contain the column headings.
I only wrote it today, so I'll probably expand on it in the near future.
<?php
class CSVparse
{
var $mappings = array();
function parse_file($filename)
{
$id = fopen($filename, "r"); //open the file
$data = fgetcsv($id, filesize($filename)); /*This will get us the */
/*main column names */
if(!$this->mappings)
$this->mappings = $data;
while($data = fgetcsv($id, filesize($filename)))
{
if($data[0])
{
foreach($data as $key => $value)
$converted_data[$this->mappings[$key]] = addslashes($value);
$table[] = $converted_data; /* put each line into */
} /* its own entry in */
} /* the $table array */
fclose($id); //close file
return $table;
}
}
?>
dawa at did-it dot com
12-Apr-2004 10:30
12-Apr-2004 10:30
The following modification will hide the unnecessary delimiter in the array
that is returned when fgetcsvfromline is called.
<?php
function fgetcsvfromline ($line, $columnCount, $delimiterChar = ',',
$enclosureChar = '"') {
global $regExpSpecialChars;
$matches = array();
$delimiterChar = strtr($delimiterChar, $regExpSpecialChars);
$enclosureChar = strtr($enclosureChar, $regExpSpecialChars);
$cutpoint = strlen($delimiterChar)+1;
$regExp = "/^";
for ($i = 0; $i < $columnCount; $i++) {
$regExp .= $enclosureChar.'?(.*?)'.$enclosureChar.'?'.$delimiterChar;
}
$regExp = substr($regExp,0,-$cutpoint).'/';
if (preg_match($regExp, $line, $matches)) {
return $matches;
}
return 0;
}
?>
=== If you were getting
[0] => "Ma"rk","Bergeron","rocks","12345,"times"
[1] => "
[2] => Ma"rk
[3] => "
..etc
You will now get
[0] => "Ma"rk","Bergeron","rocks","12345,"times"
[1] => Ma"rk
...etc
bu at orbitel dot bg
18-Mar-2004 06:54
18-Mar-2004 06:54
I found a way to parse CSVs with Perl RegExps and it's a lot faster and more efficient than the conventional fgetcsv() way.
The fgetcsv() took more than 2 minutes to parse a 15,000 lined file while my function takes 6 to 7 secs on a Celeron 366 MHz prehistoric machine.
Here's a link to the source of my function.
fgetcsvfromline() source
http://bu.orbitel.bg/fgetcsvfromline.php
fgetcsvfromline() in action
http://bu.orbitel.bg/csv.php
Please let me know if you use it or if you've found a better solution.
Dave Meiners
05-Feb-2004 06:37
05-Feb-2004 06:37
using the example above with a length of 1000 will truncate any csv rows longer than 1000 bytes, the remainder of that line will be represented next time you call $data = fgetcsv(). one solution i have seen to this is to use filesize("test.csv") as the length argument, however sometimes with large csv files you may encounter errors for exceeding the memory limit. to remedy this, i have read the csv file into an array, looping through that array to find the longest line in the csv, and then using that value as my length argument, unset($array) to free up the memory. im open to better solutions.
<?php
$length = 1000;
$array = file("test.csv");
for($i=0;$i<count($array);$i++)
{
if ($length < strlen($array[$i]))
{
$length = strlen($array[$i]);
}
}
unset($array);
$handle = fopen("test.csv", "r");
while ($data = fgetcsv($handle, $length, ","))
{
// do what you want with your array here.
}
fclose($handle);
?>
guntars at datapro dot lv
27-Nov-2003 03:24
27-Nov-2003 03:24
For all those people struggling with Macintosh conversions, since PHP 4.3 there is a new runtime setting available:
auto_detect_line_endings boolean
When turned on, PHP will examine the data read by fgets() and file() to see if it is using Unix, MS-Dos or Macintosh line-ending conventions.
This enables PHP to interoperate with Macintosh systems, but defaults to Off, as there is a very small performance penalty when detecting the EOL conventions for the first line, and also because people using carriage-returns as item separators under Unix systems would experience non-backwards-compatible behaviour.
phpnet at smallfryhosting dot co dot uk
19-Sep-2003 07:42
19-Sep-2003 07:42
Another version [modified michael from mediaconcepts]
<?php
function arrayFromCSV($file, $hasFieldNames = false, $delimiter = ',', $enclosure='') {
$result = Array();
$size = filesize($file) +1;
$file = fopen($file, 'r');
#TO DO: There must be a better way of finding out the size of the longest row... until then
if ($hasFieldNames) $keys = fgetcsv($file, $size, $delimiter, $enclosure);
while ($row = fgetcsv($file, $size, $delimiter, $enclosure)) {
$n = count($row); $res=array();
for($i = 0; $i < $n; $i++) {
$idx = ($hasFieldNames) ? $keys[$i] : $i;
$res[$idx] = $row[i];
}
$result[] = $res;
}
fclose($file);
return $result;
}
?>
NOSPAM-michael at mediaconcepts dot nl
15-Sep-2003 05:45
15-Sep-2003 05:45
A little contribution to make it more easy to use this function when working with a database. I noticed this function doesn't add logical keys to the array, so I made a small function which creates a 2dimensional array with the corresponding keys added to the rows.
<?php
// this function requires that the first line of your CSV file consists of the keys corresponding to the values of the other lines
function convertCSVtoAssocMArray($file, $delimiter)
{
$result = Array();
$size = filesize($file) +1;
$file = fopen($file, 'r');
$keys = fgetcsv($file, $size, $delimiter);
while ($row = fgetcsv($file, $size, $delimiter))
{
for($i = 0; $i < count($row); $i++)
{
if(array_key_exists($i, $keys))
{
$row[$keys[$i]] = $row[$i];
}
}
$result[] = $row;
}
fclose($file);
return $result;
}
?>
ng4rrjanbiah at rediffmail dot com
08-Sep-2003 01:02
08-Sep-2003 01:02
Important note about the CSV format:
There should *not* be any space in between the fields. For example,
field1, field2, field3 [Wrong!]
field1,field2,field3 [Correct-No space between fields]
If you add space between the fields, MS Excel won't recognize the fields (especially date and multi-line text fields).
HTH,
R. Rajesh Jeba Anbiah
shaun at linuxhost dot cc
15-Aug-2003 10:03
15-Aug-2003 10:03
I've seen alot of people talking about ways to convert from mac to unix or from dos to unix.
For DOS to UNIX I use dos2unix in the "tofrodos Ver 1.4" package.
http://www.thefreecountry.com/tofrodos/index.shtml
For Mac to UNIX I have the follwing in my .bashrc
alias mac2unix="perl -pi -e 'tr/\015/\012/'"
Just run dos2unix or mac2unix <filename> and it will convert. My life has been easier ever since I started installing these utilities.
reptileX at example dot com
12-Aug-2003 10:57
12-Aug-2003 10:57
beware of using this function in two different php versions,
in php 4.3 the 4th parameter can be given altough empty
in php 4.2 you get just a warning but it is not able to read the csv file if you place an enclosure that is empty
brian at brianvoss dot com
26-Jul-2003 01:55
26-Jul-2003 01:55
If anyone else is taking on the task of converting from FileMaker Pro to MySQL, you might find this useful:
Here's a collection of simple functions that take a CSV file, re-formats the data and writes to a file with MySQL INSERTs.
ORIGINAL DATA:
"1", "foo", "2"
"3", "bar", "4"
OUTPUT
INSERT info mysql_table VALUES('', '1', 'foo', '2');
INSERT info mysql_table VALUES('', '3', 'bar', '4');
For simple data it works alright, but I could not find a way to escape the contents before assembling the INSERT statements - kept getting "bad argument for implode()"...
<?php
/*************************************
* main *
*************************************/
$file = "path/to/your/file.txt";
$tbl = "your_MySQL_table";
$CSVarray = get_csv($file);
$CSVarray = makeINSERTS($CSVarray, "$tbl");
$filetowrite = $_POST["file"]."_sql.txt";
$fp = fopen($filetowrite, "w");
while (list($key, $val) = @each($CSVarray)) {
fwrite($fp, $val);
}
fclose($fp);
chmod($filetowrite, 0777);
echo "File written Successfully";
}
/*************************************
* functions *
*************************************/
// This function courtesy of drudge@phpcoders.net
function get_csv($filename, $delim =","){
$row = 0;
$dump = array();
$f = fopen ($filename,"r");
$size = filesize($filename)+1;
while ($data = fgetcsv($f, $size, $delim)) {
$dump[$row] = $data;
//echo $data[1]."<br>";
$row++;
}
fclose ($f);
return $dump;
}
function makeINSERT($text, $table){
$insert = array();
$i = 0;
while (list($key, $val) = @each($text)){
// We'll start off the INSERT with '', for an
// auto-incremented key. Remove if not needed.
$insert[$i] = "INSERT into ".$table." VALUES('','";
$insert[$i] .= implode("','", $val);
$insert[$i] .= "');\n";
$i++;
}
return $insert;
}
?>
kurtnorgaz at web dot de
21-Jul-2003 10:00
21-Jul-2003 10:00
You should pay attention to the fact that "fgetcsv" does remove leading TAB-chars "chr(9)" while reading the file.
This means if you have a chr(9) as the first char in the file and you use fgetcsv this char is automaticaly deleted.
Example:
file content:
chr(9)first#second#third#fourth
source:
$line = fgetcsv($handle,500,"#");
The array $line looks like:
$line[0] = first
$line[1] = second
$line[2] = third
$line[3] = fourth
and not
$line[0] = chr(9)first
$line[1] = second
$line[2] = third
$line[3] = fourth
All chr(9) after another char is not deleted!
Example:
file content:
Achr(9)first#second#third#fourth
source:
$line = fgetcsv($handle,500,"#");
The array $line looks like:
$line[0] = Achr(9)first
$line[1] = second
$line[2] = third
$line[3] = fourth
php at joestump dot net
14-Jul-2003 05:58
14-Jul-2003 05:58
For those of you having problems with Excel's version of CSV file exports (which include the infamous ^M characters in *NIX editors, such as VIM) here is a simple script to remove them and replace them in the normal UNIX newline character (\n).
<?
$fp = fopen('/dev/stdin','r');
while(!feof($fp)){
$line = chop(fgets($fp,4096));
$ret = ereg_replace(chr(13) . chr(10),"\n",$line);
$ret = ereg_replace(chr(13),"\n",$ret);
echo $ret."\n";
}
fclose($fp);
?>
Usage: cat file_with_windows_newlines | php -q strip_newlines.php > outfile
(this works for other stuff too, like PHP "coders" who use Notepad and upload to a UNIX environment).
kmlinac/nospam/ at oliver dot /nospam/efos dot hr
09-May-2003 12:12
09-May-2003 12:12
This is the solution to Microsoft Excel csv file - this function add last delimiter in line.
<?php
function delimiter()
{
if (is_file("file.csv"))
{
$fp = fopen($file.csv,"r");
while (false!=($line = fgets($fp)))
{
$line = "$line;";
putf($line);
}
fclose($fp);
}
}
//puts dedlimited line in new file
function putf($line)
{
$fp = fopen($file,"a");
fputs($fp,$line);
fclose($fp);
}
/* this readsl line at the time and adds ; as delimiter on line end. Its easy and helpfull*/
delimiter();
?>
anthony dot thomas at nospam dot com
06-Dec-2002 07:29
06-Dec-2002 07:29
fgetcsv stops reading your file?
I had to write a script to validate and import a large amount of data to MySQL and it would stop running. I've noticed, after trawling the internet for a solution, quite a few people have had similar problems.
Solution? Like a dope I had forgotten to put in
set_time_limit()
within a loop. Otherwise the script would time out before it finished importing all the data.
Moral of the story? Only suppress errors after your script works with a large amount of test data!
php at dogpoop dot cjb dot net
16-Nov-2002 06:01
16-Nov-2002 06:01
This function takes a csv line and splits it into an array, much like fgetcsv does but you can use it on data that isn't coming in from a file, or you can read data from a file some other way (like if your Mac files aren't being read correctly) and use this to split it. If you have any corrections, comments (good or bad), etc. I would appreciate an email to the above address.
<?php
function csv_split($line,$delim=',',$removeQuotes=true) {
#$line: the csv line to be split
#$delim: the delimiter to split by
#$removeQuotes: if this is false, the quotation marks won't be removed from the fields
$fields = array();
$fldCount = 0;
$inQuotes = false;
for ($i = 0; $i < strlen($line); $i++) {
if (!isset($fields[$fldCount])) $fields[$fldCount] = "";
$tmp = substr($line,$i,strlen($delim));
if ($tmp === $delim && !$inQuotes) {
$fldCount++;
$i += strlen($delim)-1;
} else if ($fields[$fldCount] == "" && $line[$i] == '"' && !$inQuotes) {
if (!$removeQuotes) $fields[$fldCount] .= $line[$i];
$inQuotes = true;
} else if ($line[$i] == '"') {
if ($line[$i+1] == '"') {
$i++;
$fields[$fldCount] .= $line[$i];
} else {
if (!$removeQuotes) $fields[$fldCount] .= $line[$i];
$inQuotes = false;
}
} else {
$fields[$fldCount] .= $line[$i];
}
}
return $fields;
}
?>
martin dot goldinger at netserver dot ch
28-Oct-2002 04:12
28-Oct-2002 04:12
With MAC, use: Save As - CSV for Windows. Then fgetcsv works as expected.
justin at indent dot com dot au
08-Aug-2002 10:24
08-Aug-2002 10:24
If you're working in Excel on a Mac and are exporting a spreadsheet as a CSV, PHP won't recognise the line breaks, and interprets the file as one really long line. It borders on a bug really. For me, I just open the file in BBEdit or BBEdit Lite, convert the file from Mac to Unix, and all is well. But if you're dealing with user-contributed files, you'll need to convert the line-endings first... replacing all \r's with \n's should do it.
c dot greenNOSP at Mits dot uq dot edu dot au
24-Jun-2002 06:21
24-Jun-2002 06:21
Quoting csv is pretty simple, and there are two steps..I note in previous comments only the second step has been explained.
First fields with double quotes need to double up the quotes, ie _He said "Timmy"..._ should become _He said ""Timmy""..._
Secondly as mentioned above, fields with commas need double quotes.
Here is a simple function to achieve this, that I pass to array walk, if you want to use it somewhere else, prob get rid of reference and return the value.
function check_csv_field_ref(&$item) {
$item = str_replace('"', '""', $item);
if (strpos($item, ",") !== FALSE) {
$item = '"' . $item . '"';
}
}
joeldegan-AT-yahoo.com
12-Jun-2002 11:01
12-Jun-2002 11:01
function to parse multi arrays into csv data
array in... array of array(datasets); first dataset = field names.
usage:
$toparse[0][0] = "field1";
$toparse[0][1] = "field2";
$toparse[1][0] = "value1";
$toparse[1][1] = "123123123"; // to see
echo export_to_csv($toparse);
<?php
function export_to_csv($inarray){
while (list ($key1, $val1) = each ($inarray)) {
while (list ($key, $val) = each ($val1)) {
if (is_numeric($val)){
$sendback .= $val.",";
}else{
$sendback .= "\"". $val ."\",";
}//fi
}//wend
$sendback = substr($sendback, 0, -1); //chop last ,
$sendback .= "\n";
}//wend
return ($sendback);
}// end function
?>
send the file to the client.. pretty simple.
usage: send_file_to_client("data.csv",export_to_csv($data));
<?php
function send_file_to_client($filename, $data){
header("Content-type: application/ofx");
header("Content-Disposition: attachment; filename=$filename");
echo $data;
};
?>
blair at squiz . net
15-Aug-2001 05:19
15-Aug-2001 05:19
Here is a function that takes an array and adds a CSV line to the passed file pointer.
<?php
function fputcsv ($fp, $array, $deliminator=",") {
$line = "";
foreach($array as $val) {
# remove any windows new lines,
# as they interfere with the parsing at the other end
$val = str_replace("\r\n", "\n", $val);
# if a deliminator char, a double quote char or a newline
# are in the field, add quotes
if(ereg("[$deliminator\"\n\r]", $val)) {
$val = '"'.str_replace('"', '""', $val).'"';
}#end if
$line .= $val.$deliminator;
}#end foreach
# strip the last deliminator
$line = substr($line, 0, (strlen($deliminator) * -1));
# add the newline
$line .= "\n";
# we don't care if the file pointer is invalid,
# let fputs take care of it
return fputs($fp, $line);
}#end fputcsv()
?>