Excel - XML (Magic xpi 4.14)
This dialog box contains the following fields (The parameters in bold are mandatory):
|
|
|
|
Connection
|
|
Resource Name
|
The name of the resource that you selected in the connector's Properties pane. The resources are defined in the Settings dialog box's Resources section.
|
|
Operation
|
|
Excel File
|
Use the Wizard menu to enter the path the Excel file.
Alternatively, you can click on the  to open the Expression Editor and enter the path to the file manually.
|
|
Range
|
Use the Wizard menu to select the data range. It will prompt you to select a data range from the file selected above.
Alternatively, you click on the to open the Expression Editor and enter the data range manually.
|
|
Wizard
|
The Wizard option is enabled only when the Local Agent parameter at resource configuration is set to No.
The Wizard lets you open the Excel file and load its data for further processing. If you have already entered the excel file in the Excel File field above then the Wizard button will open this file. Otherwise the wizard will give you the File Selection dialog to select the Excel file from the File System.
On selecting the file successfully, the absolute file path and the selected range will be added to the fields above.
|
|
The Wizard does not support loading the excel file using the Environment variables or Logical names in the path name and requires absolute path to the excel file.
|
|
|
Operation
|
Select one of the following operations from the drop-down list:
|
|
Columns
|
|
Column Name
|
If you select the data range with headers, the Column names will be populated using the header names in the excel sheet.
If no headers are selected, the Magic xpi will set the column names in the format Column_1.... Column_n.
Use Add or Delete buttons to add or remove the columns.
|
|
If the excel file is not loaded using the Wizard, the column names are not auto-populated. You need to add those manually.
|
|
|
Data Type
|
The supported Data Types are:
-
String (Default)
-
Numeric
-
Datetime
-
Boolean
|
|
Option
|
|
Mode
|
This field is enabled only when you select the Write operation above.
Select one of the modes from the drop-down list.
-
Overwrite (Default)
-
Insert
These options are applicable for the Write operation. Selecting the Overwrite mode will write over the existing data cells whereas the insert mode will insert new row/columns in the Excel sheet. On selecting the Overwrite mode, you may lose the existing information the Excel sheet so use it carefully.
For Overwrite mode, Magic xpi will start over writing from the row index specified in the Start at Row value in the field below.
For Insert mode, new rows will be inserted from the row index specified in the Start at Row value in the field below and the existing rows in the sheets will be pushed below these inserted rows.
|
|
Start at Row
|
These filed is enabled only when you select the Write operation.
Enter the row number for/from which you want to overwrite or insert the data.
|
|
Return
|
|
Data Blob
|
This field is enabled only when you select the Read operation above.
Defines where you want to store the resulting JSON file. Select one of the following from the drop-down list:
-
Variable: Select a variable to hold the entity data that is returned by Excel.
Click  (ellipsis button) to open the Variables List. Select a variable where you want to store the data (the default is C.UserBlob).
-
File: Select a file to hold the entity data that is returned by Excel. Click to open the Expression Editor, or click  and enter the file path where this information is to be stored.
|
|
When the Local Agent parameter of the Excel resource is set to True, the resulting XML will be stored in a variable. In this case, the File dialogue will be disabled. If the File option is selected and you change value of the Local Agent parameter from False to True in the resource configuration, it will result in Step configuration error on reopening the step. To avoid this, set the value to Variable before doing change to the Local Agent parameter.
|
|
|
Data Format
|
This field is enabled only when you select the Read operation above.
Select one of the data formats from the drop-down menu to store the data returned.
|
|
Update Range
|
This field is enabled only when you select the Write operation above.
On overwriting or inserting the data, the updated range will be populated here.
Click (ellipsis button) to open the Variables List. Select a variable where you want to store the updated range (the default is C.UserBlob)
Using this range you can configure the next step.
|
On saving the step, the schema for the step will be generated under the <Project path>\Excel\Excel_1\schema folder.
· For write operation the schema will be generated in the format <Step Name>_<Step Id>.xsd. The Data Mapper for this step will be linked to this schema and you can map the values to be updated here.
· The Read operation will generate a JSON schema file with name EmptyJsonSchema.json and link to the Data Mapper for the step. This will be non-editable. The read operation will also generate the XSD schema file in the format <Step Name>_<Step Id>_Read.xsd. You can use this schema in the subsequent steps to map the data read from the Excel file.
|
|
-
Do not modify or delete the RootXLS.xlsm file under the <Magic xpi Installation>\Runtime\addon_connectors\Excel folder. This file is required for the Excel connector to function correctly.
-
If there is mismatch between the selected column range and the count of added columns then the lower value will be considered. For example, if you select 4 columns in the Range and add 5 columns to the table then the first 4 columns will only be considered while reading/writing the excel file.
|
