Power Automate Desktop: Data Entry From Excel To Website

Power Automate Desktop: Data Entry From Excel To Website

Power Automate Desktop is a free application from Microsoft you can download to automate personal or business-related tasks on any Windows computer. One of the most popular use cases for Power Automate Desktop is taking data from an Excel and file and entering it into a form on website. Its absolutely amazing to watch as your computer quickly completes the boring task that used to take you hours. In this step-by-step tutorial I will show you how automate data entry from Excel to a form on a website.

Table Of Contents:
•  Introduction: The Insurance Claims WebsiteCreate An Insurance Claim Form With Microsoft FormsMake An Excel Spreadsheet With Insurance Claims DataTest Desktop Flow Actions To Read The Excel WorksheetLaunch A New Web Browser InstanceUse The Web Recorder To Capture Data Entry ActionsInsert A For Each Loop To Repeat Data Entry ActionsClose The Web Browser WindowRun The Power Automate Desktop Flow To Perform Data Entry




Introduction: The Insurance Claims Website

A construction company has a list of employee insurance claims in an Excel spreadsheet. Using Power Automate Desktop the company automatically enters each claim into a form on their insurance company’s website.


Create An Insurance Claim Form With Microsoft Forms

To do this example we need to build an insurance claim form to open in our web browser. We can use Microsoft Forms – a free form builder that comes with your Office 365 license for this purpose. Open http://forms.office.com/ and create a new form.

If you don’t have access to Microsoft forms just use the insurance claim form I created here.



The form should these 4 fields with the following data types:

  • 1. Employee Name (text)
  • 2 Claim Amount (text)
  • 3. Claim Date (date)
  • 4. Claim Type (text)



Once we are finished setting up the web form we click on the share button to obtain the form’s web address. We will use the web address to tell our Power Automate Desktop flow where the Insurance Claim Form can be found.




Make An Excel Spreadsheet With Insurance Claims Data

We also require an Excel spreadsheet with insurance claims data to enter into the website’s form. Open Excel and input the following data. When saving the spreadsheet make a note of the filename and folder path where it is stored. The path to my spreadsheet is:

C:\RPA\WriteExcelToWebForm\MedicalClaims.xlsx



If you don’t want to type this out manually, you can get the Excel file from my Github repository.




Get The Values From An Excel Worksheet

We have now created the insurance claims data in Excel and and the Insurance Claims web form in Microsoft Forms. It is time to build a Power Automate Desktop flow to perform data entry from Excel to a website. Open Power Automate Desktop and create a new flow.



Add the 1st flow action Set Variable. Input the folder path and file name in the To field.



Launch Excel using the 2nd flow action. Change the Launch Excel parameter to and open the following document then make the document path reference Excel_File_Path variable.



Next we will extract data from the opened Excel file. Insert a Read from Excel worksheet action, point it to the previous step’s Excel_Instance and retrieve all available values form the worksheet. Expand the Advanced options and set first line of range contains column names to true.



Then close the Excel workbook and do not save it.



The first 4 steps in our Power Automate flow should look like this.




Test Desktop Flow Actions To Read The Excel Worksheet

Before we go any further let’s make sure our Power Automate Desktop flow will successfully retrieve the Excel data and store it in a variable. Click the Play button and wait for the flow to run. The ExcelData variable should show 10 Rows, 4 columns once it is finished. Double click on the variable to inspect it.



We can see the data from the Excel worksheet has been saved to the ExcelData varaible.




Launch A New Web Browser Instance

After the insurance claims data is extracted from the Excel spreadsheet we must open a web browser to the insurance claims form. Insert a new Launch new Chrome (or other web browser) step. Choose new instance as the launch mode and supply the initial URL for Microsoft Form we built earlier.



Click the play button to run the flow to verify if the web form opens as expected.



The insurance claims web form should appear a few moments after the flow starts running. Do not close the browser tab with the form. We will require it to be open for the next step.





Use The Web Recorder To Capture Data Entry Actions

Creating the actions to do data entry from Excel to a website is made easy by Power Automate Desktop’s web recorder. When we turn on the web recorder it creates action for each task we performed on website (populating a text field, clicking on a link, etc.). Then when we are done recording those actions become part of our flow.

Click on the web recorder to start icon to start recording.



Select Google Chrome as the web browser, choose the web browser instance called Browser and pick the Insurance Claim Form tab. If you do not see Insurance Claim Form as a tab it is because you closed the browser tab with our Microsoft Form.



When the Web Recorder menu appears, click the Record button.



Now go ahead and fill in each field on the insurance claim form with fake values, submit the form, then click the link to start another response. As we do this we can see the actions generated in the web recorder. To finish the recording click the Pause button, then select Finish.



Any actions captured by the web recorder are added to the end of our main flow.




Insert A For Each Loop To Repeat Data Entry Actions

We only submitted one form with the web recorder but instead we want to submit many forms – one for each row in the Excel file. To accomplish this place a For Each loop around the steps autogenerated using the web recorder. Also, delete the flow action called Go To Webpage since we don’t want to open the form several times.



Edit the For Each loop to iterate over the ExcelData variable.




Edit Data Entry Actions To Input Values From Excel File

Right now the data entry actions for populating a text field on a web page are using hardcoded values we manually typed in. We want them to use values from the Excel file. To do this we must edit steps 7-10 of our Power Automate Desktop flow.



Change the 1st populate text field on web page action’s text parameter to CurrentItem[‘Full Name’].



Then update the 2nd action’s text parameter to CurrentItem[‘Claim Amount’].



Before continuing to the 3rd action we must convert the datetime value in the ExcelData variable’s Claim Date field to a text value. Add a new Convert datetime to text action to the flow and choose the standard format Short date.



Use the FormattedDateTime variable in the 3rd action’s text property.



Finally, complete the 4th action’s Text field with CurrentItem[‘Claim Type’]



The result of all our updates should make the flow look like this.




Close The Web Browser Window

We’re almost done. The last action we must do is to close the web browser once the loop has completed.



Add the Close web browser action to the end of the flow. The completed desktop flow should look like this.




Run The Power Automate Desktop Flow To Perform Data Entry

Press the play button to run the desktop flow. It will read-in data from the Excel file, open the the insurance claim form in Google Chrome, submit a form for each row in the table and then close the web browser.






Questions?

If you have any questions about Power Automate Desktop: Data Entry From Excel To Website please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest
20 Comments
Oldest
Newest
Inline Feedbacks
View all comments
ProfessorMor
ProfessorMor
6 months ago

Great stuff! You should consider adding some ads your blog. You’re a major contributor to the Power Platform Community and should definitely be rewarded for your hard work!

Valerie Fahel-Schaffer
Valerie Fahel-Schaffer
6 months ago

Oh my! I love this, Matt! I haven’t gotten into PAD yet, because I’m focused on so many other things, but this makes me want to go utilize it. Could it be used to update data in a Microsoft List just as easily (instead of a form)? Because we don’t have access to premium connectors in Power Automate (cloud), we have been downloading tables from MySQL in Joomla with forms made in Fabrik, and then cleaning up the data for our MS List, and then copying and pasting the cleaned data into grid view. If I could get some kind of automation going where new information entered on Joomla is automatically updated in my MS List(s), that would be great. Not for this year’s festival (which is April 2nd), but for next year’s for sure.

Valerie Fahel-Schaffer
Valerie Fahel-Schaffer
6 months ago

Awesome! I’ll give it a try when I have a chance!

Oluwatobi Yusuf
Oluwatobi Yusuf
6 months ago

Thanks so much for this article. I have always wanted to play with Power Automate Desktop (PAD) and I have now started.

Also, I must commend the fact that this blog is self-explanatory even for beginners. I would just like to suggest that you can embed a link to direct readers to a material or youtube video to install PAD in case they do not have PAD on their device.

Thanks

A-yeo
A-yeo
4 months ago

thanks so much for this useful artIcle. I am new to Power Automate and have tried your approach to create a flow that populates web fields. The error I am encountering is with a particular field that is expecting a time input in the hh:mm format while the populate text field is passing over the input as text . Is there a “num enter” key equivalent as in uipath ? In this instance the time field is not significant ie it’s ok to just pass over dummy data 00:00 . Any insight will be much appreciated . Thanks

Cathy Mai
Cathy Mai
4 months ago

Great article! I’d like to use power Automate to request access to software via a website. I normally would have to enter the requested software name then click request and then go to the next. But sometimes items already exist and that would trigger an error message telling me that this software is already installed – that error message needs to be clicked to close and then I can go to the next item. How do you handle this in Power Automate?

Ajay
Ajay
4 months ago

Wow! just came across your page , you are doing a great work, i found exactly what i was looking for in this article. you now have a follower.

mike
mike
3 months ago

you jump a lot of steps, do a video it is better

Andrea
Andrea
2 months ago
Reply to  mike

I disagree – I find Matthew’s tutorials much easier to follow and replicate than a video. Thanks again for your great content, Matthew!

Andrea
Andrea
2 months ago

Great tutorial as usual, Matthew! Just a note: for those of us whose countries follow the dd/mm/yyyy date format (aka, the *normal* format 😁), we need to use a custom datetime format in the “Convert datetime to text” step to convert the date to “MM/dd/yyyy”. Otherwise, we get errors in the form, as it’s set up for mm/dd/yyyy input.

William Viergever
1 month ago

Nice tutorial; thanks. I’ve got a situation where I need to input a single hospital’s financial data from an Excel tab into a single web form (already opened to corresponding URL for that hospital)… so no loops, etc. I’ve defined 4 contiguous ranges of Excel data via “Read from Excel” steps (see attached JOG)but in your example when you edit it to use your Excel data, you refer to column names … in my case I need to point each web form TXT box to use a particular cell in my Flow variable ranges, do you any other examples you might refer me to? Note: i have exiting VBA code that does all this, but with IE going away, i’ve been looking for alternatives and stumbled Power Automate and your website. Thanks

2022-08-22_17-22-48.jpg
Rupert
27 days ago

Thank you Matthew – almost there with my bulk import in to the Microsoft Teams Webinar tool (which offers no such thing) from my Excel sign up list from LinkedIn events.