Excel Layout

1. Click Configure > LAYOUTS > Excel.
2. Click Create Excel Layout.
   
3. On the Create Layout screen, select the Default option from the Select Layout UI Type field.
4. Click Next. 
5. On the Create Layout screen,
   
   
   
   
   1. In the Name and Description fields, type the name and description of the new excel layout.
      
      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. In the Password and Confirm Password fields, type the same password for the protected excel file respectively.

      The Password and Confirm Password fields will be enabled only if the Secure check box is selected.
   4. Select the Data Header Present check box if the data header is present in the excel file. Data Header usually contains the titles of the fields in an excel file. 
   5. In the Definition Mode field, select either Import Definition File or Enter the Fields Sequentially option.
      
      Using Import Definition File
      
      1. Select Import Definition File.
      2. In the Definition File field, select the definition type from the drop-down list and click Upload File, navigate to and select the required file, and click Open to upload.
      3. In the Process Excel Sheets window, specify the Start Row No. and Start Column No. of the sheets in respective text boxes and click Process Sheet(s).
         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 as 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.

      4. In the Sheet Name field, select the sheet. Fields of the selected sheet are populated in the table.

         If the uploaded excel file is with data in multiple sheets, a Process All Sheets check box appears.

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

            This check box appears only if the uploaded excel file has multiple sheets.

         2. In the Sheet Name field, select the sheet. Fields of the selected sheet are populated in the table.
            

      5. View and edit the fields of the file in the table, if required.

         In case of multiple sheets, the changes made in the table will be retained, if and only if, the Process All Sheets check box is selected.

         
         

      Using Entering the Fields Sequentially

      1. Select Enter the Fields Sequentially.
         

         If you select Enter the Fields Sequentially option, the Definition File field will be inactive.
      2. In the Sheet Name field, type the name of the sheet of the excel file.
      3. In the Field Name field, type the name of each field.

      4. Select the type of data from the Type drop-down list. 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.

      5. If the data type is Date, select the format of date and time in the Format and Sub Format fields respectively.

      6. If the data type is Currency, select the required currency in the Format field. The currencies supported by excel layout are:
         1. Dollar($)
         2. USD
         3. CAD
         4. CNY
         5. EUR
         6. GBP
         7. JPY

      7. Select the mode of data, whether Encrypted or Plain Text in the Data Mode field.
         If the layout is used at source end and encrypted data is coming from the source, select Encrypted in the Data Mode field. If the layout is used at the target end and you want to send the encrypted data to the target, select Encrypted in the Data Mode field.

         • 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 click Add Row. 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 click Remove Row.
           

   6. To define the hierarchy, select Define Hierarchy check box and select the merge criteria from the Merge Criteria field.
      The available merge criteria are: 

      Matching Child Record: The layout 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 layout merges the record with a previous record whose key field is not blank.

   7. Select the field name in the Merge Key field on the basis of which records are merged.

   8. Expand the Validation Properties.

      1. In case, an extra header is present in the input file then select the Restrict Extra Fields check box to validate the number of headers of the input file. 

      2. In case, the number of fields in the data file is less than the number of fields defined in the layout, select Allow Less Fields check box to process the data.
      3. Select Match Header Names check box to validate the header names of the input file with layout.

      4. Dynamic Header Present check box 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 check box, Allow Ignore case check box appears. Select Allow Ignore case check box to ignore the case mismatches in the header of the layout and the data file.            

   9. Expand the Advanced Properties.

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

      2. Type 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 type 5 in the Row Start Position, 5th row of the Excel Sheet is counted as the first row. If this layout is used at source end, the data from the 5th row onward is taken for processing. If this layout 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.
      3. Similarly, type the column start position in the Column Start Position field.

      4. 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. To use this feature, Define Hierarchy option must be unchecked. 
         

         As you select the Optimize XLSX File Processing check box, the Filter Blank Row(s) check box gets selected which you cannot deselect.
      5. Select Process Error Values check box if you want to process error values. This field gets active only when the Optimize XLSX File Processing check box is selected.

      6. Select Format Cell Formula Value check box if you want your data exactly as it appears in the excel file. It will first evaluate the formula and after that it will format that value in the same format as it is in excel file.

      7. 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 check box.
      8. Similarly, if the data contains blank rows, you can filter them by selecting the Filter Blank Row(s) check box. This option is applicable only when you use the schema at source end. 
      9. Type the enclosing character that you want to remove from the data file in the Handle Enclosing Character text box. The supported characters are (" , < , > ).
      10. If the layout 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 Encryption Secret Key field. The selected secret key activity is used to encrypt the data.

      11. If the layout 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 Decryption Secret Key field. The selected secret activity is used to decrypt the data.

      12. In the Project field, select the project.

   10. Click Save. 

See also

Creating an Excel Layout using Layout Builder