Creating an Excel Layout Using Layout Builder

To create an Excel file layout using Schema Builder, you need to upload the data file in Adeptia Connect. 

Steps to create an Excel Layout

1. Go to Account and click Manage Objects.
2. Click LAYOUTS > Excel.
   
   
   
   
3. Click CREATE Excel Layout.
   
   
   
   
4. On the Create Layout screen, select the Layout Builder option from the drop-down list of Select Layout UI Type field.
   
   
   
   
5. Click Next. The Create New Excel Layout screen is displayed in a new page.
   
   
   
   
   1. Type the name and description of the new mapping activity in the Name and Description text boxes.
      
      Name: Only alphanumeric and underscore are allowed and you must start the layout name with an alphabet or underscore.
      Description: Only alphanumeric and few special characters(_ - . : , @ $ ? \\ and space) are allowed and you must start the description with an Alphabet/Underscore/Number.
      
   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.
      The Password and Confirm Password text box will be enable, if the Secure check box is selected.

   4. Select the Data Header Present checkbox if you want to add the titles of the fields in your excel file.
   5. Define the Definition Mode either using definition file or entering the fields sequentially.
      
      Using Import Definition File
      
      1. Select Import Definition File.
      2. Select the type of file from the Definition File drop down list box.
      3. Browse and upload the data file in Upload Data File field.

      4. If the uploaded excel file has multiple sheets, a Process All Sheets check box appears.
         This check box appears only if the uploaded excel file has multiple sheets. 

         1. Select this check box to process all sheets of the uploaded excel file and click Continue.

         2. Specify the Start Row and Start Column No. of the sheets in respective text boxes.
            
            The Start Row and Start Column No. text boxes specify the row and column from where the Adeptia Connect fetch the data. For example, if you have an excel file in which first four rows of sheet1 are blank. Specify 5 in sheet1 Start Row No. Another scenario can be that the data is present in the first 4 rows, but you do not want to fetch those records.

      5. Click Continue.

         
         

      Using Entering the Fields Sequentially
      1. Select Enter the Fields Sequentially.
      2. Click Continue.
      3. On Table Data screen, type the name of each field in the Field Name text box.
      4. Type the original name of each field in the Original Name text box.

      5. Select the type of data from the Type drop-down list box. The supported data types by Excel Layout 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.

      6. If data type is Date, select the format of date and time from the Format and Sub Format drop-down list boxes respectively.

         If data type is Currency, select the required currency from the Format drop-down list box. The currencies supported by excel schema are:
      7. 1. Dollar($)
         2. USD
         3. CAD
         4. CNY
         5. EUR
         6. GBP
         7. JPY

      8. Plain Text only is supported as Data Mode. 

      9. To define the hierarchy, select Define Hierarchy checkbox and select the merge criteria from the Merge Criteria drop-down list box.
         
         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 drop-down list on the basis of which record are merged.

      11. Click Save to save the excel layout.

   
   

6. By default, the first sheet will be displayed in File View area. However, If the excel file has multiple sheets, you can select the required sheet from the drop-down list.

7. Select the type of layout from the drop-down list of Reference Layout Type field that you want to take as a reference.
8. Select the layout which you want to use as a reference file from the drop-down list of Reference Layout field.
9. Select the Project from the drop-down list of Project. This option is visible only when GAC is enabled.
10. Expand Advanced Properties to view the advanced properties of Excel layout.
    1. Type the row start position in the Row Start Position textbox. Row Start Position specifies which row of the Excel Sheet is counted as first row.
       
       For example, if you enter value as 5 in the Row Start Position, 5^th row of the Excel Sheet is counted as the first row. If this layout is used at source end, the data from the 5^th row onward is taken for processing.
    2. Similarly, select the column start position from the Column Start Position field.
    3. In case, you want to skip certain bottom row from the excel file then define the row count from Bottom Row Skip Count field.
    4. Type the enclosing character that you want to remove from the data file in the Handle Enclosing Character textbox. The supported characters are (" , < , > ).
    5. Define the Character-set encoding for your excel layout from the drop-down list of Character Set Encoding field. The available options are UTF-8, UTF-16, and ISO-8859-1.
       
       In case, the data you are processing through Adeptia Connect contains characters that are a part of another character set encoding, for example, UTF-8, then you need to change the character set encoding.
    6. In case, an extra header is present in the input file then select the Restrict Extra Fields checkbox to validate the number of headers of the input file. 
    7. In case, the number of fields in the data file is less than the number of fields defined in layout, select Allow Less Fields checkbox to process the data.
    8. Select Match Header Names checkbox to validate the header names of the input file with layout.

    9. Dynamic Header Present checkbox is an advanced feature of Excel layout, which is used to parse an excel, if:
       FieldNames defined in the source layout and the Data Headers (Column Name) of the excel file is same but not in the same order.
       FieldNames defined in the source layout and the Headers of the excel file is 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 layout) will only have Root rather than any Record entry. For instance, if the headers of the source layout  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 layout will be blank.                     

       On selecting the Dynamic Header Support checkbox, Allow Ignore case checkbox appears. Select Allow Ignore case checkbox to ignore the casing mismatches in the header of the layout and the data file.

    10. Click Optimize XLSX File Processing for processing an .xlsx file in streaming mode. Selecting this option would process large *.xlsx files without consuming much memory.
    11. 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 selecting the Filter Invalid XML Characters checkbox.
    12. Similarly, if the data contains blank rows, you can filter them by selecting the Filter Blank Row(s) checkbox. This option is applicable only when you use the layout at source end.
    13. Click Save.
11. Once the required properties are defined, you can create the Table Data and Control Total Data.
    
    
    

    Steps to define the Table Data:

    1. In File View Area, select the columns which you want to define as a header.
    2. Right-click and select New Table option to display the Table Data screen.
       
       
       
       
    3. On Table Data screen, 
       
       
       
       
       1. You can edit the Field Name value. If reference layout is defined, click the field to get the suggestions based on the reference layout.

       2. Select the type of data from the Data Type drop-down list. The supported data types for Excel Layout 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.

       3. If data type is Date, select the date and time format from the Format and Sub Format drop-down list respectively.

       4. If data type is Currency, select the required currency from the Format drop-down list. The supported currencies for Excel Layout are:
          • Dollar($)
          • USD
          • CAD
          • CNY
          • EUR
          • GBP
          • JPY
       5. Plain Text only is supported as Data Mode. 

       6. Click Save to save the table data.

    4. You can see the created Table data in Detail Panel. 

       Click  button to open table data in data view panel. Also, click  button to delete the table data.
    
    Steps to define the Control Total
    You can define the control total to parse a single column value to the source side. Before defining the control total value, you must specify these values:

    • Define the Row Start Position in case you want to parse value in header. The value must be more than 1.

    • Define the Bottom Row Skip Count in case you want to parse value in footer. The value must be more than 0.
      
      
    Follow these steps to define the control total:
    1. In File View area, select the columns which you want to parse to the source side. 
    2. Right-click and select Control Total option to display the Control Total Data screen.
       
       
       
       
    3. Select the In Header button if you want to parse the value in header. Similarly, select In Footer button to parse the value in footer.
       
       
       
       

    4. Define the row and column values. 

       In case, you have selected In Footer then you must update the Row value as per Bottom Row Skip Count value. For example, if you defined Bottom Row Skip Count value as 3 then row column value must be from 1 to 3 based on the selected row.
    5. Click Save.

    6. You can see the created Control Total in Details Panel. 
       
       
       
       

       Click  button to open control total in data view panel. Also, click  button to delete the control total.

12. You can view the Control Total and Table Data in Data View panel by clicking Data View  button from the toolbar. You can also edit and delete form this panel.

13. If you want to see the layout in XSD format, click XSD View  button from the toolbar. You can also download the XSD file by clicking Download XSD button. 

14. You can test the layout by clicking Test Schema  button from the toolbar. Click here to get more details.

15. Click Save to save the Excel layout.

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 the above figure, for ID 7812, there are three policies and similarly for ID 2311 there are four policies. While defining schema, you can specify the criteria based on which the schema can define the hierarchy. For example, you can select 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. 
 

Steps to define hierarchy

1. 1. While creating schema, select Import Definition File and then select Data from the Definition Files drop-down list box..
   2. Click Upload File. The Schema File Upload screen is displayed.
   3. Click Browse and select the file, you want to upload. Path of the selected file is shown in the Browse File text box.
   4. Now click Upload File. Name of the uploaded file is shown in the File Name list and list of sheets of the excel file are displayed.

   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 drop-down list and all the sheet names of the selected excel file are populated in this drop-down list.
   8. Select the sheet name from the Sheet Name drop-down list box. All the fields of the selected sheet are populated.

   9. To define the hierarchy, select Define Hierarchy checkbox and select the merge criteria from the Merge Criteria drop-down list box.

      
      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 drop-down list on the basis of which record are merged.
   11. Click Save to save the excel schema.

Next Step

Testing an Excel Layout

See Also

Limitations