Creating Excel Schema Activity

You can use an Excel Schema activity to define how to read data from and write data to an Excel file. To do so, you need to specify the name of the Excel sheet and enter data in the required fields, to enable identification of those fields.
While creating an Excel Schema you can also define 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:

Steps to create Excel Schema

1. On the homepage menu, click the Develop tab.

2. Go to > Services > Schema, and then click Excel. This action displays you the Manage Excel Schema screen (see Figure 245).

   

   Figure 245: Manage Excel Schema

3. Click the Create New link. This action displays you the Create Excel Schema screen.
4. Enter the name and description of new excel schema in the textboxes Name and Description fields respectively.
5. Enable the Secure checkbox if the excel file that you are using to create schema is a password protected file.
6. Enter the password for the protected excel file and confirm it.
7. Data Header usually contains the name of the fields in an excel file. If schema is used at source end, and data header is present in the file, select the Data Header Present checkbox.

8. 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 (see Figure 246). 

   
   Figure 246: Create Excel Schema

   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.

   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.

9. To define the schema using definition file, select the Import Definition File radio button; select the type of file from the dropdown list File Definition and click the Upload File button to select the required file. The Schema File Upload screen is shown (see Figure 247).

   
   Figure 247: Upload file

10. Click Browse and select the file, you want to upload. Path of the selected file is shown in the Browse File field.

11. 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 (see Figure 248 ).

    

    Figure 248: Specify Start Row and Start Column No.

12. 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.

     

    The Start Row No. and Start Column No. fields specify 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 you have to 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.

13. Once the processing is done, click Finish to close the Schema File Upload screen and return to create schema page.
14. Select the sheet name of the excel file for which you are creating this schema activity, from the dropdown list Sheet Name. Fields of the selected sheet are populated.
15. If you want to create schema by entering the fields manually, select Enter the Fields Sequentially radio button and follow the steps given below:
16. Enter the sheet name of the excel file in the textbox Sheet Name.
17. Enter the name of each field in the textbox FieldName.
18. Select the type of data from the dropdown list Type. The data types supported by Excel schema are listed in the table below.
    

    Table 2: Supported Data types

    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.

    Excel Schema supports the Currency data type.

19. If data type is Date, select the format of date and time from the dropdown lists Format and SubFormat respectively.

    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.

20. If you select the Currency data type then, you need to select the required currency from the Format dropdown list. The currencies supported by excel schema are listed below:
     
    • Dollar($)
    • USD
    • CAD
    • CNY
    • EUR
    • GBP
    • JPY

21. Select the mode of data, whether Encrypted or Plain Text from the dropdown list Data Mode. If the schema is used at source end and encrypted data is coming from the source, select Encrypted from the dropdown list Data Mode. Similarly if the schema is used at the target end and you want to send the encrypted data to the target, select Encrypted from the Data Mode dropdown list.
    
    

    • If you select Encrypted in the Data Mode then, you must select Encryption/Decryption secret key from the Advanced Properties.

    • If you select Encrypted in the Data Mode then, you must set the Data Action property in the Process Designer, while creating the process flow. To know more about Process Designer, refer to the Working with Process Flow section.
    • To insert rows, you need to specify the number and position of the rows that you want to add in the Number of Rows and at Position fields respectively and then click on the Add Row button. You can add a maximum of 99 rows at a time.

    • To remove rows, you need to specify the number and position of the rows that you want to delete in the Number of Rows and at Position fields respectively and then click on the Remove Row button.|

     

    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.

22. Click Advanced Properties. The following screen is displayed (see Figure 249).

    
    Figure 249: View Advanced Properties of Excel Schema

23. Enter the Row Start Position in the Row Start Position field. Row Start Position specifies which row of the Excel Sheet is counted as first row. For example, if you enter 5 in the Row Start Position, 5th row of the Excel Sheet is counted as the first row. If this schema is used at source end, the data from the 5th row onward is taken for processing. If this schema is used at the target end, data is copied into the 5th row onward. 1st to 4th row of the target excel sheet will remain blank.
24. Similarly, enter the Column Start Position in the Column Start Position field.

25. Select Dynamic Header Support check box to view Allow Ignore case.
    

    Using Dynamic Header Support
    Dynamic Header 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 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 same order. 

26. Select the Allow Ignore case checkbox to ignore the casing mismatches in the Headers of the Schema and the data file. This option is applicable only when the Dynamic Header Support is selected.

27. In case the number of fields in the data file is less than the number of fields defined in schema, you need to enable the Allow Less Fields checkbox to process the data.

    • When the number of fields in the data file are less than the number of fields defined in the schema then, the schema does not processes the data and gives an error during execution.

    • If you still want to process the data, then you need to enable Allow Less Field checkbox.
    • When you enable the Allow Less Field option then, the schema generates an empty tag for fields that are not present in the data file.
    • This option is applicable only when you use the schema at source end.
28. In case the input data contains some characters that are invalid in XML, then this may result in the mapping getting aborted. You can filter these invalid XML characters by checking the Filter Invalid XML Characters checkbox.
29. Similarly, if the data contains blank rows, you can filter them by checking the Filter Blank Row(s) checkbox. This option is applicable only when you use the schema at source end. By default it remains unchecked
30. If you want to remove enclosing characters (e.g. " , < , > ) from the data file while parsing, enter the enclosing character that you want to remove, in Handle Enclosing Character field. Currently following enclosing characters are supported:
    1. 1. Double Quote (")
       2. Less than symbol (<)
       3. Greater than symbol (>)
31. If schema is used at target end and you want to convert the data from Plain Text to encrypted mode, select the secret key activity from the Encryption Secret Key dropdown list. The selected secret key activity is used to encrypt the data.

32. If schema is used at 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 dropdown list. The selected secret activity is used to decrypt the data.

    To know, how to create secret key activity, refer to the Creating Secret Key Activity section in the Administrator Guide.
    To learn more about Advanced Properties refer to Changing Advanced Properties section.

33. Click the Save button.

Using Excel Schema to parse XLSX file
When you use excel schema to parse XLSX file, then you need to do some additional setting while creating process using this schema.
While creating the process flow, in Process Designer, double click the Excel schema that you are using in the process flow. Properties of the Excel Schema is displayed in the properties panel (see Figure 250 )

Figure 250: View Advanced Properties of Excel Schema

In value of File Type property, select Excel Workbook (*.xlsx).
When the value File Type property is set as Excel Workbook (*.xlsx), this schema can parse both types of excel file: XLS as well as XLSX file.

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 (see Figure 251).

Figure 251: Policy Details

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

1. While creating schema, select the Import Definition File radio button and then select Data from the dropdown list.
2. Click the Upload File button. The Schema File Upload screen is displayed (refer to Figure 247).
3. Click Browse and select the file, you want to upload. Path of the selected file is shown in the textbox Browse File.
4. 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).

5. 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.

    

   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.

6. Once the processing is done, click Finish to close the Schema File Upload screen and return to create schema page.
7. 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.
8. Select the sheet name from the dropdown list Sheet Name. All the fields of the selected sheet are populated.

9. To define the hierarchy, select the Define Hierarchy checkbox and select the merge criteria from the dropdown list Merge Criteria.

    

   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.

10. Select the field name from the Merge Key dropdown list on basis of which record are merged.
11. 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

To parse an excel/text file, whose Headers are same but not in order with the FieldNames of the Excel/text Schema, check the Dynamic Header Support checkbox in the Advanced Properties. Now the Excel/text Schema will parse the data from the respective columns.
To parse an excel/text file, whose headers are different and not even in order with the fieldnames of the Excel/text Schema, check the Dynamic Header Support checkbox in the Advanced Properties. Apart from this an XML file is used by the schema, which contains the mapping between the headers of the excel/text file and the fieldnames of the Excel/text Schema. Following is the sample XML (see Figure 252).

<?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>

Figure 252: XML to map Field Name with Data Header

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:

Figure 253: Sample Process Flow

In the process flow shown in Figure 253, a file source activity is used to read an excel file. An Excel Schema is used to parse the data from the excel file. FieldNames defined in the excel Schema and Headers in Excel file are different. To parse the data from the excel file, an XML file, which contains the mapping between FieldName and the Header is passed to the process flow context. To pass the XML file to process flow context, another File source activity (File_Src_ExcelHeaderXML) is used.
This file source activity reads the XML file from a specified location and passes it to the context target activity. In context Target activity, you need to define the value of the parameterName property. Figure 254 shows the properties of the Context Target activity.

Figure 254: Context Target Properties

In this process flow, the value of parameterName field is FieldName_Header_Mapping.
After context target, schema uses the PutContextVar action. This action sets the value of the variable Service.<ActivityName>.excelHeaderMappingXML in the XML Schema. Figure 255 shows the properties of PutContextVar action.

Figure 255: PutContextVar Properties

1. Click Edit to define the variable name and the value to be set. The Edit Context Variables screen is displayed (see Figure 256).

   

   Figure 256: Edit Context Variables

2. Click the New ( ) icon. The Context Variable Information dialog box is displayed (see Figure 257).

   

   Figure 257: Context Variable Information

3. Enter Service.<ActivityName>.excelHeaderMappingXML in the Variable Name field. For example, Service.Excel_Schema.excelHeaderMappingXML
   where 
   Activity Name is name of the Excel Schema Activity. For Example FieldName_Header_Mapping.
4. In the Variable Value field enter the value, which you defined in the parameterName in the Context Target activity.
5. Click Done twice to return to graph canvas area.

6. Make sure to create a stream from file source to Excel Schema activity.

   To Know how to create a process flow, refer to the Creating Process Flow section.