Exporting Data to a Downloadable CSV File with CakePHP

24/02/16: If you’re looking for a CakePHP 3 solution check out my updated article Exporting Data to a Downloadable CSV File with CakePHP 3 and CsvView.

22/04/14: As pointed out by Mark in his comment generating CSVs in CakePHP can be easily done using the csvView plugin. However, the method described below is still useful to know as it can be extended for other types of file downloads.


Generating a downloadable CSV file in CakePHP seems to be a bit of a stumbling block for many relatively new to using Cake. It doesn’t need to be challenging. There’s just a few basic steps you need to put in place.

As an example we’ll consider a Subscriber model that contains contact details of people who have subscribed to our app. We want to create a CSV export of all the subscribers data.

(The following has been tested against CakePHP v2.4.6.)

The Controller

Let’s start with the controller. We need to create an action in the Subscriber’s Controller for generating the CSV export:-

<?php
// app/Controllers/SubscribersController.php

class SubscribersController extends AppController {

	public function export() {

		$this->response->download("export.csv");

		$data = $this->Subscriber->find('all');
		$this->set(compact('data'));

		$this->layout = 'ajax';

		return;

	}

}

We’re calling $this->response->download("export.csv") to set the relevant headers. When you trigger the controller’s action from the browser it will start to download a file named ‘export.csv’ rather than attempt to display the view in the browser.

We then grab the data from the database and set it ready for the View.

Finally we set the View layout to use the AJAX layout, $this->layout = 'ajax'. This might seem quirky, but it will ensure the View isn’t wrapped in any HTML markup. We just want to output the content of the View we are shortly about to put together.

The Route

As it currently stands the URL for our CSV export will be ‘subscribers/export’; it would be nice to append that with the CSV extension. To do that we’re going to tell Cake to correctly parse CSV file requests. In our routes.php file (in app/Config/) add the following:-

Router::parseExtensions('csv');

This will now allow us to access our CSV export from the URL ‘subscribers/export.csv’.

The View

Now we need to put together our View, making sure we correctly escape values being output:-

<?php

// app/Views/Subscribers/export.ctp

foreach ($data as $row):
	foreach ($row['Subscriber'] as &$cell):
		// Escape double quotation marks
		$cell = '"' . preg_replace('/"/','""',$cell) . '"';
	endforeach;
	echo implode(',', $row['Subscriber']) . "\n";
endforeach;

This should be pretty self explanatory. We’re looping over all the rows in our $data array; escaping any double quotation marks in the values and then wrapping the value in quotation marks; and finally outputting the row delimiting values with commas and adding a new line break at the end.

Finally

Everything should now be setup, you’ll just want to be able to link to your CSV export from another View. If you’re parsing the file extension then you need to include this in the URL; so using the HtmlHelper we need to include the ‘ext’ parameter for the file extension:-

echo $this->Html->link('export', array(
	'controller' => 'subscribers', 
	'action' => 'export',
	'ext' => 'csv'
));

That should be it! Clicking on the ‘export’ link should start the browser downloading our export.csv file.

Related Content

Published on


Comments

  1. mark |

    Whats wrong with https://github.com/josegonzalez/cakephp-csvview/ ?
    IMO that is the real answer on how to create csv files. Especially since a View class should be responsible for the presentation of the data – and layout/template files are not needed here then either.

  2. Andy |

    @mark there’s nothing wrong with using the plugin you’ve mentioned. I wrote this piece in response to someone who was attempting to achieve CSVs in Cake by manually setting the headers in the view.

    I’m of the view that if there’s a stable plugin to achieve something it’s worth using rather than re-inventing the wheel, but it is still good to understand the workings as they can come in handy.

    I’ve added a note about the plugin to the article.

  3. surjit |

    this tutorial is help full me..Thanx

  4. Nandini |

    Thank you so much. It helps me.

  5. Lalit |

    Nice tutorial for csv export .it helps me a lot.

  6. Mark B |

    Hi Andy Carter in Sheffield (UK). This is Mark B, also in Sheffield (UK) :)

    Just trying out your export CSV code and all works well except it’s outputting as an HTML file with the name ‘export.csv.html’.

    What could I be doing wrong?

    Thanks!

  7. Andy |

    Hi Mark B in Sheffield, not sure why you would be getting ‘.html’ at the end. Have you setup the routes correctly with parseExtensions? I’d also check you haven’t got anything setup on the server to append ‘.html’ to paths.

  8. Chris |

    Hi Andy,

    I am also having the same issue as Mark B – when I download the file it downloads a file called ‘export.csv.html’.

    Mark B did you get this problem solved?

    Any ideas?

    Thanks :-)

  9. Chris |

    Yeah, the routes are set correctly and nothing configured on the server.

    After having a look around it seems to be an issue with Safari – https://jamfnation.jamfsoftware.com/discussion.html?id=6773

    It works fine in Chrome.

    Not sure if anything can be added in the CakePHP view to make Safari recognise that it’s a CSV?

  10. Andy |

    Just did a quick Google and it does seem like it’s an issue with Safari. Someone’s asked about a similar issue on Stack Overflow.

  11. Ashwani |

    what if i have to just save the file to another folder on the server

  12. Andy |

    Hi Ashwani,

    If all you need to do is save some data as a CSV file on the server then you don’t really need most of what is described above. CakePHP has a really helpful File utility that will help you save some data to a file.

    Basically, you will want to reuse some of the code described in the View section above to output comma separated values with the File utility. Obviously not within a View though.

  13. Ramkumar Gubendhiran |

    Hai,This is useful for me.Then i have one clarification.How can i add a new row at the top the csv ?

  14. Andy |

    Hi Ramkumar,

    You should be able to add a new line at the start by just echoing “\n” at the top of the View template.

  15. Orande |

    This was very useful, i was able to get file cvs to download quite easy, i am however wonder how could i include the column names to the cvs file.

  16. Andy |

    Hi Orande, glad you found this useful. It’s really simple to include column names in the CSV file. You just need to output a new comma delimited line with the column names before the foreach in the View.

    I sometimes do this using an associative array defined in the Controller that I pass as a View variable; the array keys can then be used to define which fields you want returning in your ‘find’ query.

  17. migul |

    thak you

  18. srujan |

    Hi Iam I used “\n” for new line to separate the data in a row but “\n” not working

    My code is
    for loop..code here
    echo @$year.” “. @$award[‘Recipient’][$i][‘name’].” \n “;
    end loop;

  19. rupesh |

    Nice article sir.
    Thanks a lot.

  20. Jigar Dhaduk |

    Hi Andy sir,
    It helps me.
    Thanks a lot :)

  21. Woon |

    hi, I am using cakephp 3, I have followed all the steps and just changing the
    $this->layout = ‘ajax’; to $this->viewBuilder()->layout = ‘ajax’;,
    but the content got exported was including all html elements, &#039;, and also thsoe sql query.
    Hope you can help me out on this, thank you.

  22. Amit Ghosh |

    How to get multiple table columns from specific ( user + student ) Table and only get student_id columns values

  23. Anupal Sharma |

    Hi, i have working everything, But problem with me is when there is very huge amount of data like 10,000 above records then it goes out of memory. I did set max_execution_time to maximum but still its not able to handle.
    How i would do that ?
    I seen other application they do this process in backend and send email when its done. How can i do that?

  24. Andy |

    Hi Anupal, you probably want to take a look at using a queue system like the Queue plugin.

  25. Ndumiso |

    Hello there,thanks so much for such easy to follow tutorial.Mine is working just fine.The problem I have encountered is that ,the export doesn’t export table headers to excel.

  26. FAIZAN SHAH |

    this work for me, but i have a question
    what if my table contains foreign key and i want to access values that belongs to that foreign key ?

Leave a Comment
  • You will need to preview your comment before you can submit it.