Welcome to Portal

?Unknown\pull-down

Welcome to Zoho Cares

Bienvenido a Soporte de Zoho

Search our knowledge base, ask the community or submit a request.

How to loop through Excel rows

Overview

This guide will demonstrate how to process data for each row in an Excel file within your RPA workflow. We'll achieve this by creating two workflows: a main workflow to read the Excel data and a sub-workflow to process each individual row.

Common use cases:

Data migration: Transferring data from Excel to other systems like CRMs, ERPs, or databases
Data transformation: Manipulating data within Excel, such as formatting, calculations, or filtering
Web form automation: Automating the filling in of web forms with data from each Excel row
API Interactions: Sending data from each Excel row to an API endpoint

Building the main workflow

1. Create a new flow
    1. Go to the My Flows page and click Create flow.
    2. Give your flow a name and a description, and then choose the folder where you want to store the workflow.
    3. Then, select the required RPA agent to run the workflow. RPA agents are desktop machines that execute the workflow. The flow will run on the selected agent.
      1. You can change the agent later in the workflow builder, but ensure that file paths and other configurations are compatible with the new agent.

2. Set the trigger
The first step in building a workflow is to select a trigger. A trigger is an event that initiates the workflow. For this example, we’ll use the File or Folder event trigger and set the trigger event as Created, so the RPA bot will monitor a specific folder, and every time a new file is added to the folder, the workflow will get triggered.


3. Open and read the Excel file
    1. Use the Open Excel task to specify the file path. You can use a dynamic file path (obtained from the trigger output), so this will be the file path for the new file that is added every time to the specific folder.
      1. You can also use a static file path in case you want the RPA bot to read from a specific predefined file path.
       
    1. Use the Read from Excel task to extract data from the desired sheet. Configure the sheet name, header row, and data format as needed. Here, we read all the data from the required sheet. Select Read data from > Entire sheet and configure the header row accordingly.
    2. Since we're using a dynamic file path from the trigger, we cannot directly fetch the sheet names from the file. Therefore, you'll need to manually specify the sheet name in the Custom Value for Sheet name field.
Notes
Note: If you plan to further process the extracted data in other cloud app actions, such as integrating with Slack or Zoho CRM, select Yes for the Use the output data in subsequent cloud app actions? option. This will transfer the data from the Excel file to the RPA server, where it can be manipulated and used by other cloud app actions. 

However, if your processing needs are limited to local operations, like copying data within Excel files or transferring data between Excel and an ERP system, you can select No. This will keep the data processing within your local environment, avoiding unnecessary data transfer to the RPA server.

4. Create a custom function
  1. Go to the LOGIC tab and click + Custom Function and then click Create custom function.
  2. Name the function, for example, "processExcelRow".
  3. Add an input parameter of the "String" type to receive the Excel data we read from the sheet. Example: "excel_data".



    Here's the code snippet for the custom function:
    1. void processExcelRow(string excelData)
      {
      data_list = input.excelData.toJSONList();  //convert the input string to a JSON list to iterate over each row.
      for each row_data in data_list
      {
      final_map = map();  // For each row, create a map to store the data.

      // Ensure the column names in row_data.get("Employee Name") match the column headers in your Excel sheet.
      final_map.put("Name", row_data.get("Employee Name"));  
      final_map.put("Email", row_data.get("Email Address"));
      final_map.put("Department", row_data.get("Department"));

      //Use the "Invoke URL" task to call the sub-workflow, passing the data map as a parameter.
      call_flow2 = invokeurl
      [
          url: "Replace sub workflow webhook trigger URL here"
          type: POST
          parameters: final_map
          
      ]; } }

  4. Now save the custom function. Return to the workflow builder screen and add this custom function to the main workflow. Configure the function's input with the data extracted from the Excel sheet.



Building the sub-workflow

1. Create a new flow
  1. Navigate to the My Flows page and click Create Flow.
  2. Give your sub-workflow a name, such as "Process Excel Row Data", and then choose the folder where you want to store the workflow.
  3. Finally, select the required RPA agent to run the workflow.
2. Set the trigger
  1. Choose the Webhook trigger as the trigger for this workflow. Copy the webhook URL using the copy button.


  2. Now, switch to the main workflow and locate the custom function you created for processing each Excel row.
  3. Within the invokeUrl task of the custom function, you'll find a placeholder URL. Replace it with the actual webhook URL you copied from the sub-workflow configuration in the previous step and save the function.


  4. Click the Test button within the sub-workflow's webhook trigger configuration. This allows you to send a sample payload to the workflow, simulating the data structure it expects from the main workflow. (While not mandatory, this step is highly recommended.)


  5. Switch back to the main workflow and use the Test and Debug feature.


  6. Place a sample Excel file containing data in the monitored folder.
  7. The main workflow will process the file, extract data from each row, and send the sample payload to the sub-workflow using the webhook URL.

  8. Click Done, and we've successfully tested the webhook trigger. We can now use the data from the payload in subsequent workflow actions to process it further.
3. Build the workflow
In this workflow, we'll define the actions that we want to perform on each Excel row. For example, you might want to fill out web forms, update databases, or send emails based on the data in each row.

An example of filling out a web form
  1. To automate web form filling, we'll use the RPA Recorder. Click on RPA Recorder, then Web Browser. Also ensure you have the Zoho RPA Chrome extension installed
  2. Use the web recorder to capture the steps involved in filling out the web form.
  3. Record actions like navigating to the website, typing data into input fields, and submitting the form.
  4. Stop the recording once you've captured all the necessary steps.


  5. Next, confirm the steps you recorded using the web recorder. If you need to delete any steps, you can do it from this screen. Alternatively, you can re-record, cancel this process, or start again.


  6. Edit the recorded steps, configure them with the input payload data from the initial webhook trigger test, and save the changes. Then thoroughly test the workflow to ensure it works as expected.


  7. Refine the workflow as needed, adding error handling and logging mechanisms.
  8. Lastly, activate both workflows. Whenever a new file is created in the specified folder, the main workflow will read the Excel sheet and then trigger the sub-workflow to fill out the web form for each row of Excel data. 


    1. For more details, refer to the sample bot execution below:






Helpful?00
Updated: 1 month ago
Share :
Follow

Subscribe to receive notifications from this article.