Copy Excel Data into a Web Application using a Loop
The following example extracts data from an excel spreadsheet, iterates through each row and inserts the data into a web application.
Import the process into your WinAutomation console and place the sample data 'Example_Sheet.xlsx' onto your desktop before you run the script.
The process automatically maps your desktop directory, launches Excel, and captures the data into a Data Table variable %ExcelData%. Note that you can add or remove data from the sheet as required and the 'Read from Excel Worksheet' action will accurately capture all of the data. When removing data from the Excel document to be processed, it is advised that you delete the entire row and not just the cell values. If you simply delete the populated cells without refilling the data, this may result in phantom characters in your worksheet (the cell will be "empty", but not "null").
The process proceeds to iterate through each row of data in the Excel spreadsheet and pass the respective cell values into the web application using a 'For Each Loop' and interactive Web actions. The controls for the web application were captured using the UI Spy (activated by pressing 'Select Control From Repository' > 'Add Control') within each interactive action. The extracted Excel data values are also incorporated into the interactive web actions and are used to populate text fields and select drop down list values.
In this example, each row from %ExcelData% is extracted into variable %CurrentItem%. Note that you can use the literal header names in the variable like
%CurrentItem["First_Name"]%, or you can use column index values if your data does not contain a header, or whether you just want to use column indexes. In this case %CurrentItem["First_Name"]% and %CurrentItem% would yield the same result when calling data in the loop action.
Additional information on the actions used throughout the process are included in the message tabs which are visible when opened in the Process Designer.