Excel Import and Export

PreviousCreate a New Entity NextOCR Service

Last updated 10 months ago

Excel Import and Export

Excel Import and Export enables users to seamlessly import data from an Excel sheet. The data can then be validated, stored, and exported to a standardized Excel sheet format.

Rules and Guidelines

Multiple sheets in one workbook must have different sheet identifiers to be identified by different mappings.
Multiple sheets in one workbook with the same sheet identifier are not supported.

Viewing Validation Rules

Validation results are stored in the entity VALIDATION_ENTITY_RESULT.
One instance of a validation entity will be created per uploaded file regardless of the number of sheets in the workbook.
An attribute configured above will contain a single message, aggregating the results of the validation for all sheets.
A sheet is considered valid if you see [Validation successful] next to the sheet name. For example:
Any other message indicates that a sheet is not configured properly and will explain why, e.g.,
- [Sheet 'Asset Manager Submission' validation result: [There was found more than one Sheet Identifier for the sheet 'Asset Manager Submission': [87bee507-d284-4dcd-bde4-b04a56b0aa08, bdb559fd-7ade-416e-a288-db54873ad073]]]
- [Sheet 'Notes' validation result: [No Sheet Identifiers were found for the sheet 'Notes']]
- [Sheet 'Pooled Fund' validation result: [Field D24 has type STRING, but attribute GROSS_RETURN_1_YEAR of LGPS_DOCUMENT entity is NUMERIC], Sheet 'Segregated Mandate' validation result: [No Sheet Identifiers were found for the sheet 'Segregated Mandate'], Sheet 'Notes' validation result: [No Sheet Identifiers were found for the sheet 'Notes']]
If validation fails for a sheet, no data from that sheet will be processed, but correctly configured sheets will still produce a custom entity. The import sheet should be fixed before retrying.
In the User Interface, a green check mark (✓) is shown after successfully uploading a sheet:
Failure is shown as a red cross (×):

Store Data in a Custom Entity

To store data from the Excel import sheet, a custom entity is needed in the datastore.

Custom Entity Structure

A foreign key between this custom entity and FILE is needed.
The SHEET_NAME attribute should be configured for every custom entity. It serves to correctly generate output files, and validation checks this requirement. A row number is added to the SHEET_NAME to differentiate processed data. The actual row in the imported sheet does not have to correspond with this number. Therefore, reduce the number given in "Process Data From Row" to get the actual row number in the imported sheet.
Date fields should be of the domain DATE. Numeric fields should be of the domain NUMERIC or FLOAT. Text fields should be of the domain TEXT or STRING.

Create Excel Mapping

An Excel Mapping can be either of the type IMPORT or EXPORT. Both should be configured to have an end-to-end import/export solution.

The combination of NAME, TYPE, and VERSION must be unique throughout the entire ProcessMaker IDP installation.
Version is optional and serves mainly to distinguish between several versions of the same mapping.

Custom Entity Structure

When creating an Import Mapping, choose the data layout of the import sheet. This can be either Vertical or Horizontal.

A vertical sheet contains one set of data per sheet.
A horizontal sheet contains multiple sets of data on different rows.

Note: For a horizontal mapping, the "Process Data From Row" attribute must be provided.

Create Import Mapping

Follow the steps blow to create an import mapping:

Create Excel Sheet for Import

The sheet name can be arbitrary and does not participate in processing. The mapping must contain at least one sheet to be processed.

Create Sheet Identifier for Import

After creating a sheet, you must provide sheet identifiers. The sheet must contain at least one sheet identifier record, but there can be more if needed. The sheet identifier should be unique.
Note: A specific FIELD can be used only in one sheet identifier per sheet.

Create Field Mapping for Import

The field in an Excel document and the reference to a datastore attribute must be provided for the mapping. When creating import mapping fields, note that if you refer to a cell with a formula (e.g., SUM) that refers to empty cells, the processed cell value will be 0 (or 0.0) but not null, due to default Excel behavior.

For a vertical layout, the field must contain both column and row characters (e.g., D24). For a horizontal layout, only the column name needs to be provided for the field (e.g., D).
Note: The combination of ATTRIBUTE and FIELD is unique per sheet.

Create Export Mapping

Rules and Guidelines

A template file is required and should be supplied in the form.
Only .XLSX and .XLSM formats are supported for the template.
An arbitrary file (e.g., text file, image, song, movie, etc.) with just a valid extension is considered invalid and will not be processed.
The template file should have exactly one sheet matching the sheet identifiers. Other sheets (e.g., comments/documentation sheets, sheets with pictures/charts) present within a template will not cause issues and are allowed.
Select the desired document type in the Export Document Type attribute. It will be set as the Document Type attribute for each generated file.

Follow the steps below to create an export mapping:

Create Excel Sheet for Export

The export mapping must contain one sheet at most to be processed in ProcessMaker IDP. Mappings with two or more sheets (regardless of whether they have sheet identifiers configured) will be ignored, and the output files will not be generated for them. The sheet name can be arbitrary and does not participate in the export.

Create Sheet Identifier for Export

The export mapping sheet must have at least one sheet identifier. The sheet identifier must be contained in the value of the referenced Excel cell for a positive match. Matching rules are the same as for import sheet identifiers.

Create Field Mapping for Export

Each value that needs to be placed into the export template requires a field mapping, and thus an attribute in the custom entity. Use the entity types involved in the import mapping to configure Excel fields for export. The field attribute points to a cell within the template file of the export mapping. The combination of entity and attribute references defines which value from the datastore will be written to a field of the export template file.

Generating Output Files

Rules and Guidelines

Each uploaded Excel file that passes validation without errors yields some instances of entity types to be created.
In general, each sheet defined in an import mapping leads to the creation of entity instances.
These entity instances can later be placed into generated Excel files according to the export mapping.

Exporting Vertical Layout Data

The output file is generated if there is at least one instance of at least one entity type used in an export mapping. If there is an instance of one entity type but instances of other entity types used in an export mapping cannot be found, a file will still be generated. If there is more than one instance of an entity type, a separate file is generated for each entity instance.

Exporting Horizontal Layout Data

Not currently supported.

Upload a File

Rules and Guidelines

Use the Files and Folder module to store your import sheet.
Only the file types .XLSX and .XLSM are supported.
Not all software is capable of saving Excel documents in the format supported by ProcessMaker IDP.
Saving an Excel file with an extension different from .XLSX and .XLSM and then applying the correct extension will make it un-processable by ProcessMaker IDP.

Here is a list of programs that produce Excel files in a correct format:

LibreOffice
Office365

The MIME type should be as following: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

PreviousCreate a New Entity NextOCR Service

Last updated 10 months ago

Excel Import and Export enables users to seamlessly import data from an Excel sheet. The data can then be validated, stored, and exported to a standardized Excel sheet format.

Rules and Guidelines

Multiple sheets in one workbook must have different sheet identifiers to be identified by different mappings.
Multiple sheets in one workbook with the same sheet identifier are not supported.

Viewing Validation Rules

Validation results are stored in the entity VALIDATION_ENTITY_RESULT.
One instance of a validation entity will be created per uploaded file regardless of the number of sheets in the workbook.
An attribute configured above will contain a single message, aggregating the results of the validation for all sheets.
A sheet is considered valid if you see [Validation successful] next to the sheet name. For example:
Any other message indicates that a sheet is not configured properly and will explain why, e.g.,
- [Sheet 'Asset Manager Submission' validation result: [There was found more than one Sheet Identifier for the sheet 'Asset Manager Submission': [87bee507-d284-4dcd-bde4-b04a56b0aa08, bdb559fd-7ade-416e-a288-db54873ad073]]]
- [Sheet 'Notes' validation result: [No Sheet Identifiers were found for the sheet 'Notes']]
- [Sheet 'Pooled Fund' validation result: [Field D24 has type STRING, but attribute GROSS_RETURN_1_YEAR of LGPS_DOCUMENT entity is NUMERIC], Sheet 'Segregated Mandate' validation result: [No Sheet Identifiers were found for the sheet 'Segregated Mandate'], Sheet 'Notes' validation result: [No Sheet Identifiers were found for the sheet 'Notes']]
If validation fails for a sheet, no data from that sheet will be processed, but correctly configured sheets will still produce a custom entity. The import sheet should be fixed before retrying.
In the User Interface, a green check mark (✓) is shown after successfully uploading a sheet:
Failure is shown as a red cross (×):

Store Data in a Custom Entity

To store data from the Excel import sheet, a custom entity is needed in the datastore.

Custom Entity Structure

A foreign key between this custom entity and FILE is needed.
The SHEET_NAME attribute should be configured for every custom entity. It serves to correctly generate output files, and validation checks this requirement. A row number is added to the SHEET_NAME to differentiate processed data. The actual row in the imported sheet does not have to correspond with this number. Therefore, reduce the number given in "Process Data From Row" to get the actual row number in the imported sheet.
Date fields should be of the domain DATE. Numeric fields should be of the domain NUMERIC or FLOAT. Text fields should be of the domain TEXT or STRING.

Create Excel Mapping

An Excel Mapping can be either of the type IMPORT or EXPORT. Both should be configured to have an end-to-end import/export solution.

The combination of NAME, TYPE, and VERSION must be unique throughout the entire ProcessMaker IDP installation.
Version is optional and serves mainly to distinguish between several versions of the same mapping.

Custom Entity Structure

When creating an Import Mapping, choose the data layout of the import sheet. This can be either Vertical or Horizontal.

A vertical sheet contains one set of data per sheet.
A horizontal sheet contains multiple sets of data on different rows.

Note: For a horizontal mapping, the "Process Data From Row" attribute must be provided.

Create Import Mapping

Follow the steps blow to create an import mapping:

Create Excel Sheet for Import

The sheet name can be arbitrary and does not participate in processing. The mapping must contain at least one sheet to be processed.

Create Sheet Identifier for Import

After creating a sheet, you must provide sheet identifiers. The sheet must contain at least one sheet identifier record, but there can be more if needed. The sheet identifier should be unique.
Note: A specific FIELD can be used only in one sheet identifier per sheet.

A single identifier yields a positive match if the value of the IDENTIFIER attribute is contained (case ignored) in the Excel cell referenced by the FIELD attribute.

Create Field Mapping for Import

The field in an Excel document and the reference to a datastore attribute must be provided for the mapping. When creating import mapping fields, note that if you refer to a cell with a formula (e.g., SUM) that refers to empty cells, the processed cell value will be 0 (or 0.0) but not null, due to default Excel behavior.

For a vertical layout, the field must contain both column and row characters (e.g., D24). For a horizontal layout, only the column name needs to be provided for the field (e.g., D).
Note: The combination of ATTRIBUTE and FIELD is unique per sheet.

Create Export Mapping

Rules and Guidelines

A template file is required and should be supplied in the form.
Only .XLSX and .XLSM formats are supported for the template.
An arbitrary file (e.g., text file, image, song, movie, etc.) with just a valid extension is considered invalid and will not be processed.
The template file should have exactly one sheet matching the sheet identifiers. Other sheets (e.g., comments/documentation sheets, sheets with pictures/charts) present within a template will not cause issues and are allowed.
Select the desired document type in the Export Document Type attribute. It will be set as the Document Type attribute for each generated file.

Follow the steps below to create an export mapping:

Create Excel Sheet for Export

Create Sheet Identifier for Export

Create Field Mapping for Export

Generating Output Files

Rules and Guidelines

Each uploaded Excel file that passes validation without errors yields some instances of entity types to be created.
In general, each sheet defined in an import mapping leads to the creation of entity instances.
These entity instances can later be placed into generated Excel files according to the export mapping.

Exporting Vertical Layout Data

Exporting Horizontal Layout Data

Not currently supported.

Upload a File

Rules and Guidelines

Use the Files and Folder module to store your import sheet.
Only the file types .XLSX and .XLSM are supported.
Not all software is capable of saving Excel documents in the format supported by ProcessMaker IDP.
Saving an Excel file with an extension different from .XLSX and .XLSM and then applying the correct extension will make it un-processable by ProcessMaker IDP.

Here is a list of programs that produce Excel files in a correct format:

LibreOffice
Office365

The MIME type should be as following: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
It is recommended to check the MIME type of an Excel file using the .