Lookup in Microsoft® Excel® Documents

The Lookup in Microsoft® Excel® Documents action task is used to complement your job file's Metadata by retrieving data from a Microsoft® Excel® spreadsheet on your system. The data retrieved is based on existing data in your Metadata, and it will either be added to your Metadata or will append or replace your existing Metadata if it exists.

Fields on any level (Page, Datapage, Document, Group, Job) can be used, and the result field will be added on the same level as the lookup field.

Note: This task will automatically "loop" through the Metadata and repeat its action for each of your Metadata's data pages. This task should not be placed after a Metadata Sequencer task, otherwise it will run as many times as there are Metadata sequences, which will result in decreased performance.

Use cases

Here are some examples of how the Lookup in Microsoft® Excel® Documents task could be used in combination with PlanetPress Design documents.

Use case 1: Send personalized emails with promotional document attached

A PlanetPress Design document takes a PDF file as the input data file, and reproduces it exactly as it enters. The document also contains a custom data selection set to hold an email address. The data selection's value is given by a Metadata Field called 'Email'. The value of this Email Metadata field is a region from the sample data file representing the customer number. At production time, the Lookup in Microsoft® Excel® Documents action task will replace the value of this Metadata field with the corresponding customer email.

Use Case 2: Translate a list of line items descriptions into a given language

A PlanetPress Design document takes as input a transactional PDF file, and reproduces it exactly as it enters. Metadata fields called ItemDesc are created, one for each line item description, at the data page level. Each ItemDesc Metadata field is given the value of a line item description as found on a region of the current data page. The line item descriptions appearing on the resulting page produced by the design tool are custom data selections whose value come from the corresponding ItemDesc Metadata fields. The Lookup in Microsoft® Excel® Documents action task updates the value of all 'ItemDesc' Metadata fields with their corresponding foreign language descriptions.

Input

Any compatible data file. This task requires Metadata to be present.

Processing

The task parses each level of the Metadata and, for each field of the specified name it finds, a lookup is made. If a field of the same name appears on multiple levels, the lookup will happen for all fields, on all levels, individually.

Output

The original data file is unchanged. Metadata is updated according to the specified criteria.

Task properties

General Tab

  • Excel group
    • Excel workbook: The full path and file name of a Microsoft® Excel® workbook (.xls or .xslx file). You can use the Browse button on the right to browse to the file on your computer.
    • Excel worksheet: The name of the worksheet you want to use. Once a workbook is open, this drop-down will automatically list all the available worksheets.
    • Refresh button: If you have modified the original Microsoft® Excel® workbook to add a sheet, click this button to refresh the list of worksheets.
  • Metadata group
    • Lookup Field: The name of the Metadata field that will be used to determine which row should be returned. The Metadata field can be on any level.
    • Lookup Column: The name of the column in the Microsoft® Excel® worksheet that corresponds to the contents of the Lookup Field.
    • Action: What to do with the resulting data from the Microsoft® Excel® worksheet. This can be:
      • Add Field: Creates a new field with the data. This may cause multiple fields to be created.
      • Replace field value: Replaces any existing field with the new content. Only the last result will be displayed. If the field does not exist, it will create it.
      • Append field value: Ads the data to the existing field within the same one. No "separator" is added. If the field does not exist, it will create it.
    • Result Field: The Metadata field name in which the result should be stored. This field will appear in the same Metadata level as the Lookup Field.
    • Result Column: The name of the column where the information you want to retrieve is located. For example, this could be a client email or full name.
    • button: Ads a new lookup line. You can have as many lines as you want. The lines will be executed in order from top to bottom, so you can rely on a previous line to bring additional information.
    • button: Removes the currently selected (highlighted) line.
    • button: Moves the currently selected line up one place.
    • button: Moves the currently selected line down one place.
  • Search option group
    • Match case: Will force the lookup column names to be in exactly the same case as the Lookup column name. This means if you type in "CustomerID" in the lookup column and the actual column is named "customerid", it would not return any result.

On Error Tab

For a description of the options on the On Error tab see Using the On Error tab.

Miscellaneous Tab

The Miscellaneous tab is common to all tasks.

It contains a text area (Task comments) that lets you write comments about the task. These comments are saved when the dialog is closed with the OK button and are displayed in The Task Comments Pane.

Check the option Use as step description to display the text next to the icon of the plugin in the Process area.

The tab also provides an option to highlight the task in The Process area with the default color, set in the Preferences (see Colors), or the color selected or defined under Highlight color on this tab.
To revert the selected highlight color to the default color, open this tab, turn the Highlight option off and close the dialog with the OK button; then turn highlighting back on.
Highlighting can also be turned on and off via the task's contextual menu and with the Highlight button on the View ribbon.