Configuring XL2File Node

To configure XL2Filenode:

1. Node descriptions :This node will help in transforming excel file type data to CSV or TSV type data
2. Click on the XL2File node in Nodes Area and place it on the work area. The XL2File frame is displayed on the right pane.

   

   Nodes Configuration_Figure 100:

   Provide user defined name to the node.

3. Double-click on the XL2File configuration pane. The size of XL2File screen increases.

   

   Nodes Configuration_Figure 101:

4. Enter the mandatory details-
   • Sheet Name: Enter the name of excel sheet.
   • File extension: Enter the extension of the source file.
   • File Starts With: Enter the starting literal or string of excel file name.
5. Enter other required details-
   • Row Wise Parse: Select this radio button when the data in destination file needs to be displayed Row Wise.

   

   • Column Wise Parse: Select this radio button when the data in destination file needs to be displayed Column Wise.

   

   • Single Sheet: Select this checkbox when only single sheet needs to be transferred to a file. This checkbox is selected as a default. The fields-Append O/P, Process Selected Sheets, Skip First N Sheets, Skip Last N Sheets, Ignore Sheets Start With, Ignore Sheets End With are disabled.
   • Multiple Sheets: Select this radio button when the (excel)file has data in multiple sheets. This will enable fields Skip First N Sheets, Skip Last N Sheets, Ignore Sheets Starts With and Ignore Sheets Ends With.

   

   • Append O/P: Select this checkbox when data in multiple sheets is to be appended in a single file. Unselect this checkbox when data in multiple sheets is to be saved in different files.

     Note : Enter "$SHEET_NAME$" in the File Name Field, to get the sheet names as destination file names.

   • Process Selected Sheets: Enter specific number of sheets that needs to be transferred to a file. The number of a sheet is separated by comma and no space should be there after comma.

     Example: 2,5,6

   • Date Format: Select the required date format from list of values that appears on clicking the LOV button available beside this field. User can also provide Desired date format in the Format field . When clicked on Test button, it will display the Desired date format in Date field.

     

   • Transpose: Select this radio button when the data in destination file needs to be displayed as Transpose of data in the source file.
   • Skip Header Rows: Enter the number of Header Rows that should not appear in the destination file.
   • Skip Footer Rows: Enter the number of Footer Rows that should not appear in the destination file.
   • Skip First N Sheets: Enter the number of sheets starting from first and then successive sheets that needs to be skipped while transferring to a file.
   • Skip Last N Sheets: Enter the number of sheets starting from last sheet and then preceding sheets that need to be skipped while transferring to a file.
   • Ignore Sheets Start With: Enter the specific text with which the sheets name starts with, to ignore transferring of those particular sheets to a file.
   • Ignore Sheets End With: Enter the specific text with which the sheets name ends with, to ignore transferring of those particular sheets to a file.
   • From Directory: Click on the icon beside this field to open the Directory Chooser dialog box to choose the location of the source (excel) file that needs to be uploaded.
   • To Directory: Click on the icon beside this field to open the Directory Chooser dialog box to choose the location for the destination file.
   • Process Directory: Click on the icon beside this field to open the Directory Chooser dialog box to choose the location for the file that needs to be moved after the process is completed.
   • Error Directory: Click on the icon beside this field to open the Directory Chooser dialog box to choose the location for the file that needs to be moved when any error occurs while processing.
   • Destination File Name: Enter the name of the destination file. Specifying file name is not mandatory. If file name is not mentioned at this field then the destination file name will be same as excel file name. Specify the value at this field as $SHEET_NAME$ when the destination file should be created with sheet name .
   • File Type: Select the destination file type.

     CSV : Comma Separated Value.

     TSV : Tab Separated Value.

   • Destination File Name Suffix: Will give an option to append destination file name with Start Time/End Time.
   • Destination File Name Prefix: Will give an option to append destination file name with Start Time/End Time.

Transpose Functionality

1. Select the Transpose radio button to include transpose functionality. On selecting the Transpose radio button, another tab named, Advanced tab will be displayed beside the Basic tab.
2. In the case of Transpose Option, all the fields in Basic Tab as to be filled.

   

   Nodes Configuration_Figure 102: The Advanced Tab

   In Advanced tab, select the required Header Row value.

   1. Browse the excel file in which the transpose of columns is to be done.
   2. The column names in the excel file are displayed under Column Name section of the Source Panel.

   

   1. Select the corresponding checkbox in Repeated Column for a Column Name after which the transpose should occur.
   2. When Header Row is '1', enter the values for Header Name and Value Name in the Destination Panel. These values display as Column Names for non repeated (Transposed) Columns and values of the non repeated Columns that appears after the repeated Columns in the destination file. When Header Row is '2', enter the value for Header Name only in the Destination Panel. In the destination file, the header value will be displayed as the column name for non repeated columns that appears after the repeated Columns.
   3. Click Apply after entering the details.