Loading data

In order to personalize the content of a template in the Designer, you need to have a Data Model and a sample of customer data. At the design stage the Designer doesn't need to have access to all data; it just needs to know which data fields exist in your data and it needs some data to be able to display a preview of the output.

To get access to a Data Model and data, you can open:

When you open a data file or a database, the Data Model will be derived from it.

After opening a data file or database, the Data Model pane at the right hand bottom shows the data fields that occur in the data.

The Value column displays data from the first record in the data file. Use the First, Previous, Next and Last buttons to browse through the records, or use the Page Up, Page Down, Home and End keys.

Note: When loading data from a CSV or Excel file, the data type of each field (String, Integer, Boolean, Currency, Float or Date) is auto-detected based on the data in the first 10 rows. With other data files, all values are initially strings.

In order to set conditions based on a data field, or to apply formatting to a value, the data type may be important. To change the type of a field, right-click it and select Set Type.

Associated data file

When you save a template, any data file that is currently open will be associated with the template by saving the path to the data file in the template file.
The next time you open the template the Designer will attempt to reopen the data file as well.

To change which data file is linked to the template, open both the template and the data file that should be linked to it; then save the template.

Adding data from a data file

Note: Note that all values are initially strings (except when loading a CSV or Microsoft Excel file).

In order to set conditions based on a data field, the data type may be important. To change the type of a field, right-click it and select Set Type.

  1. Click File, select Data and then click Open Data File.... Browse to the location of the file and select it.
    The Designer can open the following types of data files:

    • Tabular files: CSV files (.csv, .txt) and Microsoft Excel files (.xls, .xlsx)

      Note: Excel files saved in "Strict Open XML" format are not supported yet.

    • Microsoft Access Database (.mdb, .accddb)

    • JSON files (JSON)

    • XML files (.XML)

  2. Review the options presented, to ensure that the data will be interpreted correctly. The options available depend on the type of data file (see below).

  3. Click OK and verify that the data are read properly.

    With CSV and Microsoft Excel files, the first 10 rows are analyzed to detect a field's data type. If the data in a column is not consistent throughout the file, an exception may be thrown if a value cannot be interpreted, and data loss may occur if the detected data type is more general than the data type in a subsequent row. For example, if an Integer is detected in the analyzed rows, but a subsequent row contains a Float value, the value of the Float value is truncated to an Integer.
    To change the detected type of a field, right-click it and select Set Type.

Once loaded, a link to this data file is saved with the template. The next time the template is opened, the data file is loaded as well.

To remove the link to the data file: click the arrow next to the Data file button in the Data Model toolbar, and select Close data file.

Microsoft Excel (XLS/XLSX) file options

  • First row contains field names: Check this option to use the first line of the Excel file as headers. This option automatically names all extracted fields.
  • Sheet: Only one sheet can be selected as the data source.

    • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

CSV file options

  • Encoding: The Designer can not infer from a CSV file what encoding it is in. The default is right in the large majority of cases, but when it isn't, it can be very difficult to figure out the correct encoding. Ask your source what the encoding of the file is.
  • Field separator: Choose the character that separates the fields in the file.
  • Comment delimiter: If there are comment lines in the file, type the character that starts a comment line.
  • Text Delimiter: Type the character that surrounds text fields in the file. Other delimiters will not be interpreted within these text delimiters.
  • Ignore unparsable lines: When checked, any line that does not correspond to the above settings will be ignored.
  • First row contains field names: Check this option to use the first line of the CSV as headers. This option automatically names all extracted fields.

    • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

Microsoft Access MDB file options

  • File: Include the full path to the file.
  • Password: If the file isn't password protected, you can click Next without filling out this field.
  • Table name: Use the drop-down to select the appropriate table or stored query to retrieve the appropriate data set.
  • Encoding: Use the drop-down to select the encoding with which to read the data in the table.

    • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

JSON file options

After selecting a JSON file, specify if and how the JSON file must be split into multiple records.
This is done by selecting an object or array as parent element. Its direct child elements - objects and arrays, not key-value pairs - can be seen as individual source records. If the root is selected, there will be only one source record.

Whether source records are output as individual records depends on the trigger. Either:

  • Select On element to create a new record in the output for each object or array in the parent element.

  • Select On change to create a new record each time the value in a certain key-value pair changes. Only key-value pairs that exist at the root of a child element can be evaluated.

All data found in child elements of the selected parent element are extracted to fields at the root of the Data Model. If a value consists of an object or array, the entire object or array is extracted to one data field.

Field names are derived from keys, objects and arrays in the first record, but those aren't necessarily the same in a subsequent record. If following records have a different structure, for example if a record has more child elements compared to the first record, some data may not get extracted.

Tip: JSON data can also be imported directly into the Data Model; see Adding JSON sample data.

XML file options

Select what level of XML elements defines a record.

The Trigger is what triggers the creation of a new record. It can be set to:

  • On element: This defines a new record when a new element occurs on the selected XML level.
  • On change: This defines a new record when a specific field under the chosen XML level has a new value. After selecting this option, you have to select the field that triggers the creation of a new record.

Adding data from a database

  1. Click File, select Data and then click Connect with Database.... Browse to the location of the file and select it.
    The Designer can open databases from the following types of data sources:

    • MariaDB

    • MySQL

    • Oracle

    • Microsoft SQL Server

    • Microsoft Access Database (.mdb, .accddb)

    • ODBC DataSource

    • JDBC

  2. Review the options presented. The options available depend on the type of database data source; see below.

MariaDB \ MySQL

  1. Enter the appropriate information to connect to the database:

    • Server: Enter the server address for the MariaDB or MySQL database.

    • Port: Enter the port to communicate with the MariaDB or MySQL server. The default port is 330.

    • Database name: Enter the exact name of the database from where the data should be extracted.

    • User name: Enter a user name that has access to the MariaDB or MySQL server and specified database. The user only requires Read access to the database.

    • Password: Enter the password that matches the username above.

  2. Click Next and enter the information for the source table.

    • Connection string: Displays the full path to the database.

    • Table: Use the drop-down to select the appropriate table or stored query to retrieve the appropriate data set.

    • Encoding: Use the drop-down to select the encoding with which to read the data in the table.

      • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

  3. Click Finish to open the database.

Oracle

  1. Enter the appropriate information to connect to the database:
    • Server: Enter the server address for the Oracle database.
    • Port: Enter the port to communicate with the Oracle server.
    • Database name: Enter the exact name of the database from where the data should be extracted.
    • User name: Enter a username that has access to the Oracle server and specified database. The user only requires Read access to the database.
    • Password: Enter the password that matches the username above.
  2. Click Next and enter the information for the source table.
    • Connection string: Displays the full path to the database.
    • Table: Use the drop-down to select the appropriate table or stored query to retrieve the appropriate data set.
    • Encoding: Use the drop-down to select the encoding with which to read the data in the table.

      • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

  3. Click Finish to open the database.

Microsoft SQL Server

  1. Enter the appropriate information to connect to the database:

    • Server: Enter the server address for the Microsoft SQLServer database.

    • Port: Enter the port to communicate with the SQLServer. The default port is 1433.

    • Database name: Enter the exact name of the database from where the data should be extracted.

    • User name: Enter a user name that has access to the SQLServer and specified database. The user only requires Read access to the database.

    • Password: Enter the password that matches the user name above.

  2. Click Next and enter the information for the source table.

    • Connection string: Displays the full path to the database.

    • Table: Use the drop-down to select the appropriate table or stored query to retrieve the appropriate data set.

    • Encoding: Use the drop-down to select the encoding with which to read the data in the table.

      • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

  3. Click Finish to open the database.

Microsoft Access

  1. Enter the appropriate information to connect to the database:
    • File name: Browse to your Microsoft Access database file (.mdb, .accddb).
    • Password: Enter a password if one is required.
  2. Click Next and enter the information for the source table.
    • Connection string: Displays the full path to the database.
    • Table: Use the drop-down to select the appropriate table or stored query to retrieve the appropriate data set.
    • Encoding: Use the drop-down to select the encoding with which to read the data in the table.

      • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

  3. Click Finish to open the database.

ODBC DataSource

  1. Select the ODBC system data source. Note: Only 32-bit data sources are currently shown in this dialog, even if your system is 64-bits.
  2. Click Next and enter the information for the source table.
    • Connection string: Displays the full path to the database.
    • Table: Use the drop-down to select the appropriate table or stored query to retrieve the appropriate data set.
    • Encoding: Use the drop-down to select the encoding with which to read the data in the table.

      • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

  3. Click Finish to open the database

JDBC

  1. Enter the appropriate information to connect to the database:
    • JDBC Driver: Use the drop-down to select which JDBC Driver to use for the database connection.
    • JAR file path: Enter a path to the JAR file that contains the appropriate driver for the database below.
    • Server: Enter the server address for the database server.
    • Port: Enter the port to communicate with the server.
    • Database name: Enter the exact name of the database from where the data should be extracted.
    • User name: Enter a username that has access to the server and specified database. The user only requires Read access to the database.
    • Password: Enter the password that matches the username above.
    • Advanced mode: check to enable the Connection String to manually enter the database connection string.
    • Connection string: Type or copy in your connection string.
  2. Click Next and enter the information for the source table.

    • Connection string: Displays the full path to the database.

      Note: By default, the connection is attempted with encryption enabled. To instruct the driver to not use encryption, add the ";encrypt=false" parameter to the connection string.

    • Table: Use the drop-down to select the appropriate table or stored query to retrieve the appropriate data set.
    • Encoding: Use the drop-down to select the encoding with which to read the data in the table.

      • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

  3. Click Finish to open the database.

After adding data from a database, the Data Model pane at the right hand bottom shows the data fields that occur in the data.

The Value column displays data from the first record in the data file. Use the First, Previous, Next and Last buttons to browse through the records.

Adding JSON sample data

JSON data can either be added to, or replace the Data Model in a template.

To add JSON sample data to the Data Model:

  1. Select File > Data > Open JSON Sample Data, from the menu. Alternatively, click the JSON Sample Data toolbar button on the Data Model pane.

  2. Either browse to the location of the file and select it, or paste or write the JSON directly in the box below the file name.

    3. Tip: If a data field contains JSON data you could use that data as sample data: right-click the field, select Copy to copy the JSON data to the clipboard; then open the JSON Sample Data dialog and paste the data there.

  3. Review the JSON; you may edit it if you like.

  4. Select the Replace Data Model option if you want the JSON to replace the existing Data Model. Otherwise, the JSON data will be mapped to corresponding fields in the existing Data Model, and data that cannot be mapped to any field will be discarded.

  5. Click Finish.

Add a counter using the Generate Counter Wizard

Generating a counter is useful for numbered tickets or any other template requiring sequential numbers but no variable data.

The Generate Counter Wizard creates a record set with a Counter field and in that field, the current counter value for each record. The Counter starts and stops at set values and is incremented by a set value as well.

  1. To open the Generate Counter Wizard, select File > Add data > Generate counters.

  2. Adjust the settings:

    • Starting value: The starting number for the counter. Defaults to 1.

    • Increment value: The value by which to increment the counter for each record. For example, an increment value of 3 and starting value of 1 would give the counter values of 1, 4, 7, 10, [...]

    • Number of records: The total number of counter records to generate. This is not the end value but rather the total number of actual records to generate.

    • Padding character: Which character to add if the counter's value is smaller than the width.

    • Width: The number of digits the counter will have (prefix and suffix not included). If the width is larger than the current counter value, the padding character will be used on the left of the counter value, until the width is equal to the set value. For example for a counter value of "15", a width of "4" and padding character of "0", the value will become "0015".

    • Prefix: String to add before the counter, for example, adding # to get #00001. The prefix length is not counted in the width.

    • Suffix: String to add after the counter. The suffix length is not counted in the width.

  3. Click Finish to generate the Counter record set.

While the Generate Counter script is really useful for things like raffle tickets, it's unusable in combination with a data file or database, as it cannot complement that data automatically.

Tip: The Handlebars helper: counter generates sequential numbers that update for each record in the main data set.