How-to library · Developer docs

How-to library

Task-shaped pages: a title that starts with a verb, the situation in a few lines, the steps, the working code in both engines, and why this way rather than another. Found from a search, done, and left.

How-to Jobs and automation

Build a nightly export as a CSV file

Published

A scheduled job that retrieves the records, renders them as properly escaped CSV from a template, saves the file with an expiry, and emails a link to it.

Context

Context. Someone needs a spreadsheet every morning: the product list for a buyer, yesterday's sign-ups for the membership team. A scheduled job can find the records, render them as CSV, save the file, and email a link to it, with no server of your own and nothing to run by hand.

Steps

  1. Create a job Schedule: nightly → export products CSV, limited to the site, asynchronous. Set its recurring schedule to 0 2 * * *, 2 am in the client's timezone.
  2. Retrieve records find-products: entity products, order {"id": "ASC"}, limit 5000.
  3. Render template render-csv, from templates/hooks/exports/products.csv.html in the repository (below).
  4. Create file save-csv: source render-csv, expires in 14 days, filename /exports/products-{date('Y-m-d')}-{bin2hex(openssl_random_pseudo_bytes(6))}.csv.
  5. Send mail email-link: to {$siteData->get('export-recipients')}, subject Product export {date('j F Y')}, body with the link below.
  6. Run the job by hand once from the admin, open the link in the email, and check the file opens in a spreadsheet with the right columns and characters.

Template code

Template code 
{* templates/hooks/exports/products.csv.html *}
{strip}
"id","code","name","price","url"
{foreach $runData->get('find-products')->records as $product}
  {$cells = [$product->getId(), $product->getCode(), $product->getName(), $product->getFinalPrice(), "https://{$site->getHostName()}{$product->getFullPath()}"]}
  {$line = []}
  {foreach $cells as $cell}
    {$cell = $cell|strval}
    {if preg_match('/^[=+\-@]/', $cell)}{$cell = "'"|cat:$cell}{/if}
    {$escaped = str_replace('"', '""', $cell)}
    {$line[] = '"'|cat:$escaped|cat:'"'}
  {/foreach}
  {"\n"}{implode(',', $line)}
{/foreach}
{/strip}
{# templates/hooks/exports/products.csv.html #}
"id","code","name","price","url"
{%- for product in runData.get('find-products').records %}
  {%- set cells = [product.getId(), product.getCode(), product.getName(), product.getFinalPrice(), 'https://' ~ site.getHostName() ~ product.getFullPath()] %}
  {%- set line = [] %}
  {%- for cell in cells %}
    {%- set cell = cell ~ '' %}
    {%- if cell matches '/^[=+\\-@]/' %}{% set cell = "'" ~ cell %}{% endif %}
    {%- set line = line|merge(['"' ~ cell|replace({'"': '""'}) ~ '"']) %}
  {%- endfor %}
  {{- "\n" ~ line|join(',') }}
{%- endfor %}
Template code 
{* the email body *}
<!DOCTYPE html><html><body style="font-family:Arial,sans-serif;font-size:14px;">
<p>Last night's product export, {$runData->get('find-products')->records|count} products:</p>
<p><a href="https://{$site->getHostName()}{$runData->get('save-csv')->file->getPath()}">Download the CSV</a> (the link works for 14 days)</p>
</body></html>
{# the email body #}
<!DOCTYPE html><html><body style="font-family:Arial,sans-serif;font-size:14px;">
<p>Last night's product export, {{ runData.get('find-products').records|length }} products:</p>
<p><a href="https://{{ site.getHostName() }}{{ runData.get('save-csv').file.getPath() }}">Download the CSV</a> (the link works for 14 days)</p>
</body></html>

Why this and not that

Every cell quoted and its quotes doubled, which is the whole of CSV escaping: a name with a comma or a quote in it stays in its column. A cell that starts with =, +, - or @ gets a leading apostrophe, because a spreadsheet runs it as a formula, and product names and form answers are typed by people you do not control.

A folder and a random part in the filename. A file saved without a folder goes in the batch's own folder and is deleted with the batch when its persistence discards the run; a file under /files/ is served to anyone with its URL, so the name must not be guessable. The expiry deletes old exports so the file manager does not fill up.

A link, not an attachment: Send mail cannot attach files. One render for a few thousand rows; beyond that, a single template becomes slow and memory-hungry. For a larger export, write the rows in parts from a loop, one file per chunk, and join them with Merge files; chunk sizes are on Scheduled work. If the export must contain only what changed since yesterday, use a watermark rather than a date filter in Retrieve records, which cannot compare dates.

Go deeper
GuideScheduler tasks and the job queue GuideRecord actions

Was this page helpful?

Your answer goes to the docs team.