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_NAMEattribute 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 theSHEET_NAMEto 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.
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 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 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:
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
Last updated