Importing XLS into MODX

You can use the xls2modx to import page content from an XLS file into a MODX Revo install.

The process requires 2 steps: first a YML map file is generated using the map:import command. This file defines how the XLS columns should map to MODX fields and Template Variables (TVs). Once you have a valid mapping defined in a map file, you can reference this in the import 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) your XLS columns will be to MODX field:

php xls2modx map:import /path/to/content.xls /path/to/import.yml

Specify where the yml file should be written. The contents will depend on the format of your particular XLS file, but they will resemble something like the following:

xls2modx:
    Some Column: ''
    Other Column: ''
    X: ''
    Y: ''
    
# ... etc ...

#Sample TVs:
#    - my_tv
#    - other_tv

# Configuration Settings:
Config:
    identifier: pagetitle  # columns(s) with unique values used to check if a row has already been imported.
    update: true # if true, matching rows in the XLS will be updated in MODX on successive imports.

# Hard-code values for any column here, e.g. if you want all imported records
# to be children of the same parent or use the same template.
# You can hard-code valid TVs too.
# Blank values will take on the default values.
Hardcoded-Values:
    type: ''
    contentType: ''
    pagetitle: ''
    longtitle: ''    
    # ... etc ...

Comments are included in the file.

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

Technically, you do not need a map file if your XLS file happens to already use column headers that match MODX field names exactly.

---

2. Customize your Mapping

Usually, you will need to adjust how your XLS file is imported. You control this by defining a MODX field name for any column that you want imported.

Define any TVs that should be added to your MODX site in the "TVs" section. These will be defined as Text TVs.

There are a couple subtle controls defined in this file that affect how your file is imported. These are defined inside the "Config" area.

• Config -> identifier defines which field should be checked to avoid duplicates. (default: pagetitle)
• Config -> update defines the behavior that should be executed if a matching page is found. If you are running the import command repeatedly, leaving this as "true" will ensure that MODX gets the latest version of the content. If false, no further action is taken if a matching page is found. (default: true).

How do I...

Skip Columns?

If you don't want to import a particular column into MODX, delete that row from the map file or comment it out using a "#" at the beginning of the line. E.g.

xls2modx:
#    Cruft: ''

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.

xls2modx:
    pagetitle: Page Title

Write one value to Two or more columns?

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

xls2modx:
    post_excerpt: [introtext,description]

Hardcode values?

If you want every page created as a result from this import to have a fixed value, then you can specify a hard-coded value in the "Hardcoded-Values" section of your map file, e.g.

Hardcoded-Values:
    parent: 12

This is most useful if you want to import all content into a single folder or attribute it to a specific author or assign it to a specific template.

---

3. Import Content

Once you have a map file, you can import your XLS file contents into MODX via the import command. If no map file is specified, your XLS file must use exact MODX column names as headings.

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

WARNING: the import process may take several minutes to complete. OnSave events will not be fired.

How do I...

Skip Pages?

The best way to skip the import of certain pages is to remove those rows from your XLS file.