Exporting concrete5 pages and custom attributes as a CSV file

Not long ago I discussed in a video how to set up custom page types with the composer in concrete5 and how to output custom page attributes on pages and block templates. The main reason for this is to make the entry of structured data easy and consistant, but what if you then want to export the data?

Luckily with a bit of coding it's pretty easy to create a CSV export of pages and their attributes in concrete5, we can even apply permissions to the export 'page' to restrict which users can trigger an export.

Why a single page and not a script in concrete's tools directory? The answer is permissions - creating a tools script makes it harder to lock this export feature down. We might want to use advanced permissions to limit this data export to one particular user, creating a single page gives us these permission options whereas a tools script won't. Another reason is that making this script a single page means we can have a link to it display on some navigation if we like.

So just say we already have a site set up with a custom page type and custom pages attributes to store data - in my example I'm going to use the concept of 'builders', each of which have contact details like an address, phone numbers, emails, etc.

The first thing we need to do is create a single page for the export, to do this lets create a controller file in the top level controllers folder, called builders_export.php:

In this file we set up a controller class and a view function:

<?php defined('C5_EXECUTE') or die("Access Denied.");
	class BuildersExportController extends Controller {
		public function view() {
			 // fetch records here
		}
	}
?>

This is where the logic of our data export will go. Note that the name of the class is the name of the file, without underscores and CamelCased.

We also need a corresponding single page template file, which needs to go into the top level single_pages directory. In the single_pages folder, create a folder that matches the name of our single page controller and in that a view.php to match the name of our function. We're going to simply create a blank file here - we not actually going to use this file, it's just that concrete5 is expecting it there when we install the single page.

One we have both these files in place, we can install our new single page via the Dashboard. Find and visit the 'Single Pages' section of the dashboard and add the name of your single page, in this case I've called it builders_export.

Once installed, you'll see your new single page in the sitemap, and you can move it to a location you like as it's treated like any other page.

Visiting your new page you'll see an empty page, but styled as per your current theme (it will use your theme's view.php as a wrapper).

As we are wanting to just output a CSV, we want this page to just output the data, no theme, headers, scripts, etc.

So what we are going to do is call the exit() function at the end of the view function - this will prevent concrete5 from continuing on and building the page output. We'll then need to output our headers and data before this.

Some of your reading this might be thinking: 'hey, that's bad practice to output from a controller, you're supposed to use views for that!'. Well yes, but in this case we need to bypass concrete's view layer which will automatically wrap the page with the theme. We could build an extra page type that is effectively empty, but as we are effectively streaming data to a downloaded file, we're arguably not talking about a presentation layer here, just data. That's my excuse anyway!  (If anyone knows a good way to still use a single page view file whilst turning off the theme wrapping it, please let me know in the comments)

So our builders_export.php controller file now looks like this:

<?php defined('C5_EXECUTE') or die("Access Denied.");
	class BuildersExportController extends Controller {
		public function view() {
			echo 'output here';
			
			exit();	 
		}
	}
?>

The output from this page should now just be plain text, without any template around it.

Now we have this set up, we can expand this to go and fetch the data we are after using a page list object:

<?php defined('C5_EXECUTE') or die("Access Denied.");
	class BuildersExportController extends Controller {
		public function view() {
			Loader::model('page_list');
			$pl = new PageList();
			
			// all my builders are under this part of the sitemap
			$pl->filterByPath('/builders');
			
			// The page type we want has the handle 'listing', so let's filter by that too
			$pl->filterByCollectionTypeHandle('listing');
			
			$pl->sortByName();
			
			// fetch the pages, with a large number here as we want them all
			$pages = $pl->get(5000);
			
			exit();	 
		}
	}
?>

At this point we have all the pages we want to fetch data for and export in the $pages object. We can then loop through each, calling page functions to fetch the specific pieces of data. We'll output a header row for the CSV as well. I've used PHP's fputcsv function to ensure CSV formatting:

<?php defined('C5_EXECUTE') or die("Access Denied.");
 	class BuildersExportController extends Controller {
		public function view() {
			Loader::model('page_list');
			$pl = new PageList();
			
			// all my builders are under this part of the sitemap
			$pl->filterByPath('/builders');
			
			// The page type we want has the handle 'listing', so let's filter by that too
			$pl->filterByCollectionTypeHandle('listing');
			
			$pl->sortByName();
			
			// fetch the pages, with a large number here as we want them all
			$pages = $pl->get(5000);
			
			// output csv header
			echo 'name, phone, email'. "\n";
			
			// create a write-only output buffer 
			$out = fopen('php://output', 'w');
			
			foreach($pages as $page) {
				$data = array();
				
				// fetch name and attributes of page, store in array
				$data['name'] = $page->getCollectionName();
				$data['phone']  = $page->getAttribute('phone');
				$data['email']  = $page->getAttribute('email');
				//  .. fetch other attributes as required
				
				// use PHP's fputcsv function to ensure CSV formatting
				fputcsv($out, $data);
			}
			
			fclose($out);
			exit();	 
		}
	}
?>

This results in the page outputting the CSV data as below. 

The final problem though is that this is being sent directly to the browser. We want to force this content to be downloaded as a file. We can add a few header lines to the very top of the view function, making the complete controller script:

<?php defined('C5_EXECUTE') or die("Access Denied.");
	class BuildersExportController extends Controller {
		public function view() {
			header("Content-type: text/csv");  
			header("Cache-Control: no-store, no-cache");  
			header('Content-Disposition: attachment; filename="builders_export_'. date('d-m-Y') .'.csv"'); 
			
			Loader::model('page_list');
			$pl = new PageList();
			
			// all my builders are under this part of the sitemap
			$pl->filterByPath('/builders');
			
			// The page type we want has the handle 'listing', so let's filter by that too
			$pl->filterByCollectionTypeHandle('listing');
			
			$pl->sortByName();
			
			// fetch the pages, with a large number here as we want them all
			$pages = $pl->get(5000);
			
			// output csv header
			echo 'name, phone, email'. "\n";
			
			// create a write-only output buffer 
			$out = fopen('php://output', 'w');
			
			foreach($pages as $page) {
				$data = array();
				
				// fetch name and attributes of page, store in array
				$data['name'] = $page->getCollectionName();
				$data['phone']  = $page->getAttribute('phone');
				$data['email']  = $page->getAttribute('email');
				//  .. fetch other attributes as required
				
				// use PHP's fputcsv function to ensure CSV formatting
				fputcsv($out, $data);
			}
			
			fclose($out);
			exit();	 
		}
	}
?>

Viewing the page now forces a download of this file, with a nice timestamp on it.

From here, you can now in the sitemap click on this single page, select Set Permissions and configure it so only Administrators can view/access it (or more advanced permissions if that's what you are after). 

With these permissions the page won't show up on the navigation if you aren't logged in, but if you do directly visit the page (i.e., you've sent the link in an email to someone), you'll be presented with a login screen. Logging in here will directly download the CSV file.

In the example above, I've output a CSV file, but the same logic applies if you are wanting to output something else like JSON or XML - the data retrieval is the same, you would just output the headers and data differently.

-Ryan