Vote Charlie!

Creating a CSV export Movable Type template

Posted at age 25.
Edited .

Movable Type is an incredibly flexible publishing system that makes it trivial to create output files of any text file format. One common request is to create a Microsoft Excel compatible CSV (comma separated values) spreadsheet of various data from a content management system.

Generic CSV file

Briefly, the CSV format consists of lines of data where each line has the same number of items separated by commas. If the data has commas or line breaks, it should be surrounded by double quotes. If not, you can still use double quotes, which is convenient for writing a Movable Type or other CMS template. See Creativyst Software for more details on the CSV format.

A basic CSV file might look like this:

Name, Birth Date, Death Date
Britney Spears, 1981-12-02, 
Albert Einstein, 1879-03-14, 1955-04-18
Justin Bieber, 1994-03-01,
Betty White, 1922-01-17, 
George Washington, 1732-02-22, 1799-12-14

As mentioned above, you can always use double quotes around the values, so this is identical to the above:

"Name", "Birth Date", "Death Date"
"Britney Spears", "1981-12-02", ""
"Albert Einstein", "1879-03-14", "1955-04-18"
"Justin Bieber", "1994-03-01", ""
"Betty White", "1922-01-17", ""
"George Washington", "1732-02-22", "1799-12-14"

Movable Type template CSV file

To create a nice CSV file, we can use a few Movable Type tricks.

We need to escape double quotes that exist in our data by using an extra double quote, so we can using the replace template tag modifier to handle this. Since the target character is a double quote, we will use single quotes for the actual replace modifier’s syntax:

replace='"','""'

Within a looping function tag like Entries, Categories or Authors, we can use a Section block to apply a strip_linefeeds modifier to let us write one data item per line for easier readability in our template.

<mt:Section strip_linefeeds="1">
"<$mt:AuthorID replace='"','""'$>",
"<$mt:AuthorName replace='"','""'$>",
"<$mt:AuthorDisplayName replace='"','""'$>",
"<$mt:AuthorEmail replace='"','""'$>",
"<$mt:AuthorURL replace='"','""'$>"
</mt:Section>

If we are using the same code block in multiple places, we can use a SetVarTemplate block to avoid repetition:

<mt:SetVarTemplate name="authors_block">
TEMPLATE CODE
</mt:SetVarTemplate>

<mt:Authors include_blogs="all" status="enabled" need_entry="0">
<$mt:Var name="authors_block"$>
</mt:Authors>

<mt:Authors include_blogs="all" status="disabled" need_entry="0">
<$mt:Var name="authors_block"$>
</mt:Authors>

Finally, we can remove any blank lines by surrounding the entire template in another Section block and using a regex_replace modifier:

<mt:Section regex_replace="/\n\s*\n/mg","\n">
Data with many

blank lines


we want removed
</mt:Section>

Combining all these tricks, we can create a CSV export template like the following:

<mt:Section regex_replace="/\n\s*\n/mg","\n">

<mt:Ignore>CSV file header row of column labels</mt:Ignore>
AuthorID, AuthorName, AuthorDisplayName, AuthorEmail, AuthorURL

<mt:SetVarTemplate name="authors_block">
<mt:Section strip_linefeeds="1">
"<$mt:AuthorID replace='"','""'$>",
"<$mt:AuthorName replace='"','""'$>",
"<$mt:AuthorDisplayName replace='"','""'$>",
"<$mt:AuthorEmail replace='"','""'$>",
"<$mt:AuthorURL replace='"','""'$>"
</mt:Section>
</mt:SetVarTemplate>

<mt:Authors include_blogs="all" status="enabled" need_entry="0">
<$mt:Var name="authors_block"$>
</mt:Authors>

<mt:Authors include_blogs="all" status="disabled" need_entry="0">
<$mt:Var name="authors_block"$>
</mt:Authors>

</mt:Section>

For those new to Movable Type, creating a template in the system and publishing is a breeze.

  1. Navigate to the Blog Templates screen, usually by clicking “Design”
  2. Click “Create Index Template”
  3. Enter a title and paste the above code into the body area
  4. Under “Template Options” at the bottom, enter a file name like “csv_exports/authors.csv” next to “Output File”
  5. Under “Template Options” at the bottom, select “Manual” from the “Publishing” menu if you don’t want this republished every time an entry is saved
  6. Click “Save”
  7. Click “Save and Publish” to publish the template