Exporting MODX to XLS

You can use the xls2modx command-line utility to export MODX pages to an XLS file.

The process requires 2 steps: first a YML file is generated using the map:export command. This file defines how the MODX fields and Template Variables (TVs) will map to XLS columns. Once you have a valid mapping defined in a map file, you can reference the YML file in the export command. Follow along for how to do this.

1. Generate a Map File

The first step generates a map file (a YML file) which you will use to control how (or if) MODX field names will be mapped to XLS columns:

php xls2modx map:export /path/to/export.yml

Specify where the yml file should be written. The contents will resemble the following:

modx2xls:
    id: id
    type: type
    contentType: contentType
    pagetitle: pagetitle
    longtitle: longtitle
    description: description
    # ... etc ...
    tv1: tv1
    tv2: tv2
    # ... etc ...
Hardcoded-Values:

Comments are included in the file. The basic syntax is MODX-Field-Name: XLS-Column-Name.

WARNING! You must indent properly for your YML file for it to be properly parsed.

Technically, a map file is optional, but in most cases, you will want it to control the structure of the XLS generated.

---

2. Customize your Mapping

This step is optional. If you want to dictate the columns in the XLS file generated, edit the map file.

How do I...

Skip Columns?

If you don't want a particular field exported to your XLS file, delete that row from the map file or comment it out using a "#" at the beginning of the line. E.g.

modx2xls:
#    pagetitle: pagetitle

Rename Columns?

If you want to rename any field so your spreadsheet uses a different label for each column, adjust the label to the right of the colon, e.g.

modx2xls:
    pagetitle: Page Title

Write one value to Two or more columns?

If your desired XLS format requires that the same MODX field value be written to two (or more) columns, then you can use square-brackets to define an array of column names, e.g.

modx2xls:
    pagetitle: [post_title,post_excerpt]

Hardcode values?

If you want every row of your XLS spreadsheet to contain a hard-coded static value, you can define these in the "Hardcoded-Values" section so that XLS-Column-Name: Hard-coded-value, e.g.

Hardcoded-Values:
    exportedby: zorro

---

3. Export Content

Once you have a map file, you can export your MODX content by referencing it in the export command. The map file is technically optional, so you can omit it to generate an XLS file in the default format.

php xls2modx export my_modx.xls /path/to/export.yml

Progress will be echoed back to the console during the export process: you will see the title of each page as it is exported, e.g.

Exporting MODX data using mappings contained in /path/to/export.yml
Beginning export of 21 pages @ 2015-04-13 23:0531
1. Home (1)
... etc ...
21. Child Page (22)
Export complete @ 2015-04-13 23:05:36
Peak memory usage: 18.5 MB

WARNING: the export process may take several minutes to complete.

How do I...

Skip Pages?

You can pass a JSON where condition to the export command to filter pages, e.g.

php export --where='{"author":"3"}' my_modx.xls

The format of the JSON must evaluate to a hash that is a valid condition for xPDO's getCollection method.

Use the help function to see other options.