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 to open the SchemaUI window. For more information on SchemaUI window, click here.
   
   
   
   
6. In Definition panel, type the name and description of the new Excel Layout in the Name and Description textboxes.
   
   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.
   
   
7. By default, Layout Type field reflects the same layout type which you are creating. For example, Excel.
8. Select the Data Header Present checkbox if you want to add the titles of the fields in your excel file.
9. Click Select and browse to upload the excel file. This displays the uploaded excel file in File View area.
   
   
   
   

10. The sheet name of uploaded excel file will be displayed in Sheets field.

    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.

11. Select the type of layout from the drop-down list of Reference Layout Type field that you want to take as a reference.
12. Select the layout which you want to use as a reference file from the drop-down list of Reference Layout field.
13. Select the Project from the drop-down list of Project. This option is visible only when GAC is enabled.
    
14. 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.
15. 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.

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

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

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

19. Click Save to save the Excel layout.

Next Step

Testing an Excel Layout

See Also

Limitations