Creating an Excel Layout using Layout Builder

This feature helps you to manually create an excel layout through an excel file directly. You can choose a sample or an exact source file to define the layout by custom selection. By default, this feature remains disabled in the product. You need to enable this feature before you start working on this. 

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

1. Click Configure > LAYOUTS > Excel.
2. Click CREATE Excel Layout.
   
   
   
   
3. On the Create Layout screen, select the Layout Builder option from the drop-down list of Select Layout UI Type field.
   
   
   
   
4. Click Next. The Create New Excel Layout screen is displayed in a new page.
   
   
   
   
   1. Type the name and description of the new layout 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. 

         Creating Excel layout with multiple sheets is applicable only at source layout.

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

         2. Specify the Start Row Position and Start Column Position of the sheets.
            The Start Row Position and Start Column Position 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.
            Details panel is not available in case of multiple sheets.

      5. Click Continue.

      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 of Select Sheet field.
         
         

      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:
         1. Dollar($)
         2. USD
         3. CAD
         4. CNY
         5. EUR
         6. GBP
         7. JPY

      7. Plain Text only is supported as Data Mode. 

      8. To define the hierarchy, select Define Hierarchy check box 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.

      9. Select the field name from the Merge Key drop-down list on the basis of which record are merged.

      10. Click Save to save the excel layout.
          
          

5. In Definition panel, select the type of layout from the drop-down list of Reference Layout Type field that you want to take as a reference.
6. Select the layout which you want to use as a reference file from the drop-down list of Reference Layout field.
7. Select the Project from the drop-down list of Project. This option is visible only when GAC is enabled.
8. 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.
9. 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.

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

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

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

13. Click Save to save the Excel layout.