note 88616 modified in function.fgetcsv by danbrown
| From: | danbrown@php.net | Date: | Wed, 18 Mar 2009 21:59:41 +0000 |
| Subject: | note 88616 modified in function.fgetcsv by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-152112@lists.php.net to get a copy of this message | ||
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
define('ON_COLLISION_OVERWRITE', 1);
define('ON_COLLISION_SKIP' , 2);
define('ON_COLLISION_ABORT' , 3);
/**
* 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 string $sep_input the character that is used in the CSV file as field separator.
* @param string $sep_index the character that separates the fields used to construct the
hash table's index.
* @param array $index_by the array containing the columns to index the lookup table by,
and the function to pre-process those columns with.
* @param integer $on_collision a constant that determines what to do when an index is
already in use.
* @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
, $separator_input = ';'
, $separator_index = '|'
, $index_by = array(0 => '')
, $on_collision = ON_COLLISION_ABORT
, $rec_len = 1024
)
{
$handle = fopen($csv_file, 'r');
if($handle == null || ($data = fgetcsv($handle, $rec_len, $separator_input)) === false)
{
// Couldn't open/read from CSV file.
return -1;
}
$names = array();
foreach($data as $field)
{
$names[] = trim($field);
}
$indexes = array();
foreach($index_by as $index_in => $function)
{
if(is_int($index_in))
{
if($index_in < 0 || $index_in > count($data))
{
// Index out of bounds.
fclose($handle);
return -2;
}
$index_out = $index_in;
}
else
{
// If a column that is used as part of the key to the hash table is supplied
// as a name rather than as 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_in);
$index_out = $get_index[0];
if(is_null($index_out))
{
// 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;
}
}
$indexes[$index_out] = $function;
}
if(count($indexes) == 0)
{
// No columns were supplied to index by.
fclose($handle);
return -4;
}
$retval = array();
while(($data = fgetcsv($handle, $rec_len, $separator_input)) !== false)
{
$index_by = '';
foreach($indexes as $index => $function)
{
$index_by .= ($function ? $function($data[$index]) : $data[$index]) . $separator_index;
}
$index_by = substr($index_by, 0, -1);
if(isset($retval[$index_by]))
{
switch($on_collision)
{
case ON_COLLISION_OVERWRITE : $retval[$index_by] = array_combine($names, $data);
case ON_COLLISION_SKIP : break;
case ON_COLLISION_ABORT : return -5;
}
}
else
{
$retval[$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,
<?php
$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.
<?php
echo 'The name of day #3 is ', $days[3]['DayName'];
?>
Alternatively, you can use the function like this
<?php
$days = read_lookup_table_from_csv('DaysOfWeek.csv', 'DayName');
echo 'The abbreviation of Friday is ', $days['Friday']['DayAbbr'];
?>
or like this
<?php
$days = read_lookup_table_from_csv('DaysOfWeek.csv', 'DayAbbr');
echo 'The full name of Fri is ', $days['Fri']['DayName'];
?>
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.
[EDIT BY danbrown AT php DOT net: Updated by the original poster on 2009-03-06 to fix two typos.]
--was--
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,
<?php
$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.
<?php
echo 'The name of day #3 is ', $days[3]['DayName'];
?>
Alternatively, you can use the function like this
<?php
$days = read_lookup_table_from_csv('DaysOfWeek.csv', 'DayName');
echo 'The abbreviation of Friday is ', $days['Friday']['DayAbbr'];
?>
or like this
<?php
$days = read_lookup_table_from_csv('DaysOfWeek.csv', 'DayAbbr');
echo 'The full name of Fri is ', $days['Fri']['DayName'];
?>
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.
[EDIT BY danbrown AT php DOT net: Updated by the original poster on 2009-03-06 to fix two typos.]
http://php.net/manual/en/function.fgetcsv.php