You can use an Excel Schema activity to define how to read data from and write data to an Excel excel file. To do so, you need to specify the name of the Excel excel sheet and enter data in the required fields, to enable identification of those fields.
While creating an Excel Schema you can also define a hierarchy (parent-child relationship) between the records. You can define a parent-child relationship only when you create the schema susing data file.
This feature is available in:
Enterprise | Premier | Professional | Express |
|---|---|---|---|
√ | √ | √ | √ |
...
...
Type the password for the protected excel file and confirm it in the Password and Confirm Password text boxes respectively.
| Info |
|---|
| The Password and Confirm Password text box will be enable, if and only if, the Secure check box is selected. |
...
Data Header usually contains the name of the fields in an excel file. If schema is used at the source end and data header is present in the file, select the Data Header Present check box. If the schema is used at the target end, and the Data Header Present checkbox is checked, the Header will be written in the target excel file.
| Info |
|---|
| Name of the Headers in the data file and the Field Names in schema must be same and in same order. If they are not same, then you need to use Dynamic Header Support option. To know how to use Dynamic Header Support, refer to the Using Dynamic Header Support section. |
...
Specify the Start Row No. and Start Column No. of the sheets in respective text boxes and then click Process Sheet(s). This will read the sheets and field names.
| Info |
|---|
| The Start Row No. and Start Column No. text boxes specifies the row and column from where the Adeptia Suite starts fetching the data. For example, if you have an excel file in which first 4 rows of sheet1 are blank. Therefore, in that case, specify 5 in sheet1 Start Row No. Another scenario can be that the data is there in first 4 rows, but you do not want to fetch those records. |
...
The Process All Sheets check box will appear. Select this check box to process all the sheets in the uploaded excel file.
| Info |
|---|
| This check box is displayed while creating excel schema only and can be viewed at the source schema only. |
...
If you don't want to process all sheets and Process All Sheets check box is not selected, select the sheet name of the excel file for which you are creating this schema activity from the Sheet Name drop down list box. Fields of the selected sheet are populated.
| Info |
|---|
| You can view and edit the fields in the table. |
...
Select the type of data from the Type drop-down list box. The supported data types by Excel schema are:
Data Type | Description |
|---|---|
String | Select this data type if the field accepts a string value. |
Number | Select this data type if the field accepts a numeric value. |
Date | Select this data type if the field accepts a Date or Date and Time value. |
Currency | Select this data type if the field accepts a currency value. |
| Info |
|---|
| Excel Schema supports the Currency data type. |
...
If data type is Date, select the format of date and time from the Format and SubFormat drop-down list boxes respectively.
| Info |
|---|
| In case you define a date format in any column of the Excel file then, please ensure that every row of that column should have the same definition of date format as in the first row otherwise the Excel schema generates an error record. In case, you select a date format as *3/14/2001, and use the schema at the source end then, the Excel schema parses the date as per the local date format. The Adeptia Suite does not support this date format when you use the schema at the target end. You should define the time format as hh:mm:ss. |
...
- Dollar($)
- USD
- CAD
- CNY
- EUR
- GBP
- JPY
...
| Info |
|---|
|
| Info |
|---|
| If you delete the last fieldname of a record and save the schema, this action will delete the entire field. For example, a record has three fields – Name, Description, and Age. If you delete Age then, the schema will delete the entire field. |
...
Dynamic Header Support check box is an advanced feature of Excel and Text Schema, which is used to parse an excel or text file, if:
FieldNames defined in the source schema and the Data Headers (Column Name) of the excel/text file are same but not in the same order.
FieldNames defined in the source schema and the Headers of the excel/text file are not same. They may or may not be in the same order.
If the headers are not matched between Schema Definition and Source File then xml (output of schema) will only have Root rather than any Record entry. For instance, if the headers of the source schema are Id, Name, and Age and the headers of the data file are Emp Id, Emp Name, and Emp Age then the output of the schema will be blank.
On selecting the Dynamic Header Support check box, Allow Ignore case check box appears. Select this checkbox to ignore the casing mismatches in the header of the schema and the data file.
...
| Info |
|---|
|
...
If schema is used at the source end and you want to convert the data from Encrypted Mode to Plain Text, select the secret key activity from the Decryption Secret Key drop-down list box. The selected secret activity is used to decrypt the data.
| Info |
|---|
For information on how to create secret key activity, refer to Creating Secret Key Activity. |
...
Defining Field Hierarchy
You can define hierarchy (parent-child relationship) between the records of an excel file using Define Hierarchy option of excel schema. To understand how to define hierarchy let us assume that you have an excel file which contains records of insurance policies of families.
...
As you can see in figure 238, for ID 7812 there are three policies and similarly for ID 2311 there are four policies. While defining a schema, you can specify the criteria based on which the schema can define the hierarchy. For example you can select that if the ID field is blank, merge the record in the previous record, which is having ID. Similarly, you can define the criteria for the matching record. For example if value of the ID field is matching with that of the previous record, merge the record with the previous record.
Define Hierarchy option works only when you define the schema using data file.
Steps to define hierarchy
- While creating schema, select the Import Definition File radio button and then select Data from the dropdown list.
- Click the Upload File button. The Schema File Upload screen is displayed.
- Click Browse and select the file, you want to upload. Path of the selected file is shown in the textbox Browse File.
- Now click Upload File button. Name of the uploaded file is shown in the File Name list and list of sheets of the excel file is shown (refer to Figure 248).
Specify the Start Row No. and Start Column No. of the sheets in respective fields and then click Process Sheet(s). This will read the sheets and field names.
Info The Start Row No. and Start Column No. fields specify the row and column from where the schema should start fetching data. For example, if in an excel file the first 4 rows of sheet1 are blank and you want to exclude them from your selection then, you have to specify 5 in the Start Row No. field for sheet1. Similarly, there can be some data in the first 4 rows, but you do not want to fetch those records.
- Once the processing is done, click Finish to close the Schema File Upload screen and return to create schema page.
- Once the file is uploaded the Sheet Name field is converted into the dropdown list and all the sheet names of the selected excel file are populated in this dropdown list.
- Select the sheet name from the dropdown list Sheet Name. All the fields of the selected sheet are populated.
To define the hierarchy, select the Define Hierarchy checkbox and select the merge criteria from the dropdown list Merge Criteria.
Info Currently two merge criteria are supported:
Matching Child Record: The schema merges the records in case the values in the Key fields are matching.
Blank Child Record: In case the value of the key field is blank then, the schema merges the record with a previous record whose key field is not blank.- Select the field name from the Merge Key dropdown list on basis of which record are merged.
- Click Save to save the excel schema.
Prerequisites
- Data Header must be present in the Excel/text file
- Data Type must be same in the Excel/text Schema and the excel/text file
...
| Code Block | ||
|---|---|---|
| ||
<?xml version="1.0" encoding="ISO-8859-1"?>
<HeaderMap type="Name">
<Map>
<SchemaHeader>Name</SchemaHeader>
<DataFileHeader>EMP_Name</DataFileHeader>
</Map>
<Map>
<SchemaHeader>DOB</SchemaHeader>
<DataFileHeader>EMP_DOB</DataFileHeader>
</Map>
<Map>
<SchemaHeader>Age</SchemaHeader>
<DataFileHeader>EMP_Age</DataFileHeader>
</Map>
<Map>
<SchemaHeader>Address</SchemaHeader>
<DataFileHeader>EMP_Address</DataFileHeader>
</Map>
</HeaderMap> |
where:
SchemaHeader is the FieldName defined in the Excel Schema.
DataFileHeader is the name of the Header in excel file.
Excel Schema reads this XML file from process flow context. Therefore, you have to pass this XML file to the process flow context. The following figure shows a sample process flow to depict this scenario:
...
Click Edit to define the variable name and the value to be set. The Edit Context Variables screen is displayed.
...
Click the New ( 
) icon. The Context Variable Information dialog box is displayed.
...
Make sure to create a stream from file source to Excel Schema activity.
...
using data file.
Following are the major subsections:
| Section Name | Description |
|---|---|
| Processing a single sheet of an excel file. | |
| Processing multiple sheets of an excel file. | |
| Processing unstructured file (excel and pdf). |