Documentation

Load EXCEL File And Store Data: loadexcel

The Load EXCEL File And Store Data function loads an entire Excel file and stores its data in a variable (alias name) for further processing. This function is useful for handling structured data from spreadsheets within automation workflows.

Note: Ensure that the file being used is not open during the Case execution to avoid errors or conflicts.

Example Usage: Imagine you need to load an Excel sheet containing sales data so you can extract and process specific values. Instead of manually opening the file and searching for data, you can use this function to load the entire sheet into a variable and access it dynamically.

Steps to Configure:

1. Add a new step.
2. Select Run Utility Commands from the Action dropdown.
3. Leave the Screen Name and Element Key fields blank.
4. In the Parameter section, click Form and select Functions
   from the dropdown.
5. Choose Load EXCEL File And Store Data from the list of functions.
6. Provide the following details:
   • Absolute path of the Excel file: Enter the full path of the Excel file (e.g.,
     C:/Data/SalesData.xlsx).
   • Sheet Name or Sheet Number: You can provide either:
     • Excel file Sheet Number: Specify the sheet number to load (e.g., 1). OR
     • Excel file Sheet Name: Specify the sheet name to load (e.g., Sales Data Sheet)
   • Alias Name to access data: Enter a variable name that will reference the loaded sheet
     data (e.g., Sales Data).
   • Orientation: Specify how the data is structured:
     • H for Horizontal (row-wise data loading)
     • V for Vertical (column-wise data loading)
7. Click Save.

Note: The Alias Name is the variable where the loaded Excel sheet data is stored. Once the data is loaded, you can retrieve values using Get EXCEL Cell Value Using Column Name function or update values using Set EXCEL Cell Value Using Parameters function.

Expected Outcome on Execution:

• The entire sheet data is stored in the alias variable.
• The extracted data can be accessed dynamically in subsequent automation steps using the syntax ${alias variable}
• You can now retrieve specific value using the Get EXCEL Cell Value Using Column Name
• You can also update a specific cell value using Set EXCEL Cell Value Using Parameters

Using the Alias Name to Extract and Update Data

Once the data is loaded, you can:

• Retrieve a specific value using Get EXCEL Cell Value Using Column Name function.
• Update a specific cell using Set EXCEL Cell Value Using Parameters function.

Extracting Data Using Get EXCEL Cell Value Using Column Name

After the Excel file is loaded, you can access its data using the Get EXCEL Cell Value Using Column Name function by specifying the alias name, column name, and row index.

Example: You want to retrieve the Sales Amount from the second row (row index 1) of the previously loaded SalesData.xlsx file and store it in a variable called SalesValue.

Steps to Configure:

1. After configuring the Load EXCEL File And Store Data function, add a new step.
2. Select Set a Variable Value from the Action dropdown.
3. In the Element Key field, enter the name of the variable that will store the extracted value
   (e.g., EmployeeName).
4. Click Form to open the configuration form.
5. In the Parameter section:
   • Select Functions, then choose Get EXCEL Cell Value Using Column Name
   • Provide the Parameters in the following syntax:
   • SalesData.SalesAmount.1
   • Where:
     • Alias Name: SalesData
     • Key (Column Name): SalesAmount
     • Row Index: 1 (to select the second row)
6. Click Save.

Outcome on Execution:

• The value from the second row, first column (e.g., John Doe), is retrieved and stored in the variable name defined in the Element Key field (e.g., EmployeeName).
• The extracted data can be accessed dynamically in subsequent automation steps using the syntax ${variable name} (e.g., ${EmployeeName}).

The following screenshot shows how the Load EXCEL File And Store Data and Get EXCEL Cell Value Using Column Name, functions are used in an automation workflow:

Updating Data Using Set EXCEL Cell Value Using Parameters function

After the Excel file is loaded, you can update a specific cell using the Set EXCEL Cell Value Using Parameters function by specifying the alias name, key, column index, and new value.

Example: You need to update the Status column for the second row (row index 1) in the previously loaded SalesData.xlsx file by setting its value to Completed.

1. Add a new step and select Run Utility Commands from the Action dropdown.
2. Click Form, select Functions, and choose Set EXCEL Cell Value Using Parameters.
3. Provide the following parameters in the syntax:
   • Alias: Enter the alias assigned when loading the Excel file (e.g., SalesData)
   • Key = Value.Index: Specify the column name, new value, and row index in the format:
   • Status=Completed.2
   • Where:
     • Key (Column Name): Status
     • New Value: Completed
     • Row Index: 2 (to select the second row)
   • Sheet: Provide the Sheet Number (e.g., 2)
   • Orientation: Specify how the data is structured:
     • H for Horizontal (row-wise data loading)
     • V for Vertical (column-wise data loading)
4. Click Save.

Outcome on Execution: The value is updated in the specified Excel cell dynamically.

The following screenshot shows how the Load EXCEL File And Store Data and Set EXCEL Cell Value Using Parameters functions are used in an automation workflow: