Page Comparison

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:

...

1. On the Develop tab, go to Services > Schema > Excel.
   
   

   

    

2. Click Create New and do the following:
   1. Type the name and description of new excel schema in the Name and Description text boxes respectively.
   2. Select the Secure check box if the excel file that you are using is a password protected file.

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

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

   5. Define the schema either using definition file or entering the fields sequentially.
      Using Definition File
      
      1. Select Import Definition File. 
      2. Select the type of file from the Definition File drop down list box and click Upload File to select the required file. 
         
         
         
         
      3. Click Choose file to select the excel file and then click Upload file. The list of sheets will be displayed.
         

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

      5. Click Finish.

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

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

      Using entering the Fields Sequentially
      1. Type the name of the sheet of the excel file in the Sheet Name text box .
      2. Type the name of each field in the Field Name text box .

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

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

      5. h
      6. y
   6. htr
   7. j
3. .

. 

Image Removed


 

 

4. ..

5.  

6. .

7. .
8. .
9. :
10. .
11. .
12. .
    

     

     

     

13. Info

14. 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
15. 1. 1. If data type is Currency, select the required currency from the Format drop-down list box. The currencies supported by excel schema are:
           
          • Dollar($)
          • USD
          • CAD
          • CNY
          • EUR
          • GBP
          • JPY

       2. Select the mode of data,

    whether

    1. 1. whether Encrypted

    or

    1. 1.  or Plain Text

    from the dropdown list Data Mode

    1. 1.  from the Data Mode drop-down list box. 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

    1. 1. select Encrypted from the Data Mode drop-down list box. If the schema is used at the target end and you want to send the encrypted data to the target,

    select dropdown list

    1. 1. select Encrypted

    from the Data Mode

    1. 1.  from the Data Mode drop-down list box.
          
          

          Info
          If you select Encrypted in the Data Mode then

    ,

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

    ,

    1. 1. you must set the Data Action property in the Process Designer, while creating the process flow.

    To know more about

    1. 1. For information on Process Designer, refer to

    the 

    1. 1. Working with Process Flow

     section

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

    1. 1. text boxes respectively and then

    click on the 

    1. 1. click Add Row

     button

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

    1. 1. text boxes respectively and then click

    on the 

    1. 1. Remove Row

     button

    1. 1. .

    | 

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

    Click

    1. 1. Image Added

           

    2. Expand Advanced Properties
    . The following screen is displayed
    1. .
       
       Image Modified
       
    Enter the Row Start Position in the
    1. 
       
    2. Type the row start position in the Row Start Position
    field
    1.  text box. Row Start Position specifies which row of the Excel Sheet is counted as first row. For example, if you
    enter
    1. type 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.
    2. Similarly,
    enter the Column Start Position in the
    1. type the column start position in the Column Start Position
    field
    1.  text box.
     
    2. Dynamic Header Support

    check

    1.  check box is an advanced feature of Excel and Text Schema, which is used to parse an excel or text file, if:

       FieldNames

    defined

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

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

    1. the Dynamic Header Support check box, Allow Ignore

    case

    1. case check box appears. Select this checkbox to ignore the casing mismatches in the header of the schema and the data file.

    2. 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
    1. select Allow Less Fields
    check
    1.  check box to process the data.
       
       

        

       Info

       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.

    2. 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
    1. selecting the Filter Invalid XML Characters
    checkbox
    1.  check box.
    2. Similarly, if the data contains blank rows, you can filter them by
    checking the
    1. selecting the Filter Blank Row
    (s) checkbox. This option is applicable only when you use the schema at source end. By default it remains uncheckedIf you want to remove enclosing characters (e.g. " , < , > ) from the data file while parsing, enter
    1. (s) check box. This option is applicable only when you use the schema at source end. 
    2. Type the enclosing character that you want to
    remove, in Handle Enclosing Character field. Currently following enclosing characters are supported:
    1. Double Quote (")
    2. Less than symbol (<)
    Greater than symbol (>)
    3. remove from the data file in the Handle Enclosing Character text box. The supported characters are (" , < , > ).
    4. If schema is used at the target end and you want to convert the data from Plain Text to encrypted mode, select the secret key activity from
    the
    1. the Encryption Secret Key
    dropdown
    1.  drop-down list box. The selected secret key activity is used to encrypt the data.

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

    1. the Decryption Secret

    Key dropdown list

    1. Key drop-down list box. The selected secret activity is used to decrypt the data.

       Info

    To know,

    1. For information on how to create secret key activity, refer to

    the

    1. Creating

    Secret Key Activity section in the Administrator Guide.
    To learn more about Advanced Properties

    1. Secret Key Activity. For information on Advanced Properties, refer to Changing Advanced Properties

    section

    1. .

    Click the
    1. Click Save
    button
    1. .

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.

...

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

...