Page Comparison

You can use an Excel file to define the structure of your Source or Destination layout. To create an Excel file layout, you need to specify the format of the Excel file.

Follow the steps below to create an excel layout using the default option:

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. 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 and 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 the required file, and click Open to upload the file.
      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. Select the sheet from the Sheet Name field. Fields of the selected sheet are populated in the table.

         If the uploaded excel file is with data on 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. Select the sheet from the Sheet Name field. Fields of the selected sheet are populated in the table.

         3. View and edit the fields of the file in the table. 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.
      2. Type the name of the sheet of the excel file in the Sheet Name text box.
      3. Type the name of each field in the Field Name text box.

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

         
         Excel Schema supports the Currency data type.

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

      6. 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. Select the mode of data, whether Encrypted or Plain Text 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 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 Encrypted from the Data Mode drop-down list box.
         
         • 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 text boxes respectively and then 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  text boxes respectively and then click Remove Row.

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

      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.

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

   8. Expand the Validation the Validation Properties.

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

      2. 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.
      3. Select Match Header Names check box to 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 casing mismatches in the header of the layout and the data file.

   9. Expand the Advanced the Advanced Properties.

      1. Define the Characterthe 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.
         

         Tip
         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 text box. Row Start Position specifies which row of the Excel Sheet is counted as first row.

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

         
         

      3. Similarly, type the column start position in the Column Start Position text box.

      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. 
         

         Note
         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 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 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 Encryption Secret Key drop-down list box. The selected secret key activity is used to encrypt the data.

      11. 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 Decryption Secret Key drop-down list box. 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