If it helps, I wrote a component for handing CSV files and put it on
the Bakery, but it hasn't been published. The text is below:
CSV (comma-separated values) is a simple format for storing data. As
such, adding support for it in CakePHP is simple as well. Here we go...
[h4]Exporting CSV[/h4]
Exporting data is as simple as querying the model and adding a
delimeter character. For the interest of generalizing the solution, I
made the delimiter default to a comma but allow for anything. Also, I'm
using this mostly in situations where I don't want to see every value,
so I allowed for the possibility of narrowing the results with the
'where' clause.
[code]
function exportCSV($where = NULL, $delimeter = ',') {
$csv = '';
$this->recursive = -1;
$rows = $this->findAll($where);
foreach($rows as $row) {
$row[$this->name] = str_replace('\\', '\\\\',
$row[$this->name]);
$row[$this->name] = str_replace($delimeter, '\\' . $delimeter,
$row[$this->name]);
$csv .= implode($delimeter, $row[$this->name]) . chr(13);
}
$csv = trim($csv);
return $csv;
}
[/code]
As you can see, we are escaping the delimiter character. Also, it is
important to escape the escape character (in this case a backslash).
[h4]Importing CSV[/h4]
Importing CSV is a little trickier than exporting it because we want to
make sure we get a nicely formatted multi-dimensional array that
CakePHP's save function can chew on. Again, we will ask for the
delimiter of choice but default to a comma. When importing the CSV that
we previously exported, we want to do things in roughly the reverse
order. First, we are going to split the string at the line breaks into
rows. Second, remove escaped characters and replace a non-separating
comma (or other delimiting character) with a placeholder. Third, split
the rows where the delimiting character occurs. Fourth, replace the
placeholder with the character we took out. (I'll explain that in a
second.) Then finally, put humpty-dumpty back together again in the
expected structure with the correct keys.
[code]
function importCSV($csv, $delimeter = ',') {
$keys = $this->getFieldNames();
$rows = array();
$csv = trim($csv);
$csv_rows = explode(chr(13), $csv);
foreach($csv_rows as $csv_row) {
$csv_row = str_replace('\\\\', '\\', $csv_row);
$csv_row = str_replace('\\' . $delimeter, chr(26), $csv_row);
$row = explode($delimeter, $csv_row);
$row = str_replace(chr(26), $delimeter, $row);
$row = array_combine($keys, $row);
$rows = array_merge_recursive($rows, array(array($this->name =>
$row)));
}
return $rows;
}
[/code]
[h4]What's with the placeholder?[/h4]
Imagine we had a record, like the following:
[code]
[id] => 1
[product] => Black\Blue Shirt
[comment] => Nice, but wrinkles easy.
[/code]
Assuming that we use a comma as a delimiter when we exported to CSV we
would get the following line:
[code]
1,Black\\Blue Shirt,Nice\, but wrinkles easy.
[/code]
Now we want to import this. First, strip the escaped escape character
to get this:
[code]
1,Black\Blue Shirt,Nice\, but wrinkles easy.
[/code]
This looks great, but we have a problem. If we stripped the escape
character before the comma in the third value, we would end up with a
delimiting character that would result in four fields. Furthermore, if
we tried to explode the string by the delimiting character
[b]before[/b] stripping it we would still have four fields.
I solved this problem by turning those characters into a placeholder
character, then splitting the string by the delimiter, and adding the
original character back in. (I'm using a carat '^' in place of the
invisible character that I actually use.)
[code]
1,Black\Blue Shirt,Nice^ but wrinkles easy.
[/code]
After exploding we get:
[code]
[id] => 1
[product] => Black\Blue Shirt
[comment] => Nice^ but wrinkles easy.
[/code]
Then, stick the original character back:
[code]
[id] => 1
[product] => Black\Blue Shirt
[comment] => Nice, but wrinkles easy.
[/code]
[h4]Finding Field Names[/h4]
In order to make our structured array that's compatible with CakePHP's
save function, we need to know the field names of our model table.
Although I'm sure Cake does this at some point in the code, I felt like
having my own simplified function for our purposes. I'm basically using
MySQL's DESCRIBE function to return the field names and then I'm
returning them in a form of an array (perfect for use with combining
with the above functions).
[code]
function getFieldNames() {
$names = array();
$table_name = $this->tablePrefix . $this->table;
$descriptions = $this->query('desc ' . $table_name);
foreach($descriptions as $description) {
array_push($names, $description[0]['Field']);
}
return $names;
}
[/code]
[h4]In Use[/h4]
Now that all of our models have the ability to import and export CSV,
putting it to use is simple. We could do something like the following
to do an export:
[code]
$csv = $this->Product->exportCSV();
[/code]
To import our CSV, we could do something like this:
[code]
$rows = $this->Product->importCSV($csv);
foreach($rows as $row)
$this->Product->save($row);
[/code]
[h4]Wrapping it Up[/h4]
The code in its entirety is as follows. Place it in your
'app_model.php' file in the 'app' directory.
[code]
<?
//////////////////////////////////////////////////////////////////////////////////////////
// Exports CSV
function exportCSV($where = NULL, $delimeter = ',') {
$csv = '';
$this->recursive = -1;
$rows = $this->findAll($where);
foreach($rows as $row) {
$row[$this->name] = str_replace('\\', '\\\\',
$row[$this->name]);
$row[$this->name] = str_replace($delimeter, '\\' .
$delimeter, $row[$this->name]);
$csv .= implode($delimeter, $row[$this->name]) . chr(13);
}
$csv = trim($csv);
return $csv;
}
//////////////////////////////////////////////////////////////////////////////////////////
// Imports CSV
function importCSV($csv, $delimeter = ',') {
$keys = $this->getFieldNames();
$rows = array();
$csv = trim($csv);
$csv_rows = explode(chr(13), $csv);
foreach($csv_rows as $csv_row) {
$csv_row = str_replace('\\\\', '\\', $csv_row);
$csv_row = str_replace('\\' . $delimeter, chr(26),
$csv_row);
$row = explode($delimeter, $csv_row);
$row = str_replace(chr(26), $delimeter, $row);
$row = array_combine($keys, $row);
$rows = array_merge_recursive($rows,
array(array($this->name => $row)));
}
return $rows;
}
//////////////////////////////////////////////////////////////////////////////////////////
// Returns model fieldnames
function getFieldNames() {
$names = array();
$table_name = $this->tablePrefix . $this->table;
$descriptions = $this->query('desc ' . $table_name);
foreach($descriptions as $description) {
array_push($names, $description[0]['Field']);
}
return $names;
}
}
?>
[/code]
I'm sure there is a more clever way of doing this and I'd love to hear
it. Please give me some feedback on this as I would love to improve it
for my own application. Take care!
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
