Power Automate Desktop is a tool that allows you to automate tasks on your computer, such as filling web forms, manipulating files, or working with Excel. One of the actions that you can use in Power Automate Desktop is Read from Excel Worksheet, which reads data from a specified range of cells in an Excel workbook and stores it in a variable. You can then use this variable in other actions, such as Populate text field on web page or Write to Excel Worksheet.
However, some users have reported that the Read from Excel Worksheet action truncates the data that it reads from Excel, meaning that it cuts off some of the characters or numbers in the cells. This can cause problems if you want to use the full data in your automation flow. For example, if you want to fill a web form with an email address that is stored in Excel, but the action only reads part of the email address, the web form will not accept it as a valid input.
There are several possible reasons why the Read from Excel Worksheet action truncates the data, and some possible solutions to fix it. Here are some of them:
- The data in Excel is too long for the variable type that you have chosen in Power Automate Desktop. For example, if you have chosen Text as the variable type, but the data in Excel is longer than 255 characters, the action will only read the first 255 characters and ignore the rest. To solve this, you can either choose a different variable type, such as Long Text, or split the data in Excel into smaller chunks and read them separately.
- The data in Excel contains special characters, such as commas, quotes, or line breaks, that interfere with the way the action reads the data. For example, if you have a cell that contains the text “Hello, world!”, the action might read it as two separate values, “Hello” and “world!”, instead of one. To solve this, you can either remove the special characters from the data in Excel, or enclose the data in quotes, such as “”Hello, world!””.
- The data in Excel is formatted in a way that the action does not recognize or support. For example, if you have a cell that contains a date, such as 1/1/2021, but the action expects a text value, the action might read it as a number, such as 44197, instead of a date. To solve this, you can either change the format of the data in Excel, or use a function, such as TEXT, to convert the data to the desired format.
To illustrate how to use the Read from Excel Worksheet action and avoid truncating the data, let’s consider the following scenario:
- You have an Excel workbook that contains a list of customers, their email addresses, and their orders. The workbook has three columns: Name, Email, and Order. The workbook has 10 rows of data, plus a header row. The workbook is saved as Customers.xlsx in your Documents folder.
- You want to use Power Automate Desktop to read the data from the workbook and fill a web form with the customer information. The web form has three fields: Name, Email, and Order. The web form is located at https://example.com/form.
Here are the steps to create an automation flow that reads the data from Excel and fills the web form without truncating the data:
- Launch Power Automate Desktop and create a new flow.
- Add a Launch Excel action and select the Customers.xlsx file from your Documents folder. This will open the workbook in Excel.
- Add a Loop Excel worksheet action and select the workbook and the worksheet that contain the data. This will create a loop that iterates over each row in the worksheet, starting from the second row (skipping the header row).
- Inside the loop, add a Read from Excel Worksheet action and select the workbook, the worksheet, and the range of cells that contain the data for the current row. For example, if the loop variable is %ExcelRow%, the range should be A%ExcelRow%:C%ExcelRow%. This will read the data from the three columns and store it in three variables: %ExcelColumn1%, %ExcelColumn2%, and %ExcelColumn3%.
- Inside the loop, add a Launch web browser action and select the web browser that you want to use, such as Chrome or Edge. This will open a new web browser window.
- Inside the loop, add a Navigate web page action and enter the URL of the web form, https://example.com/form. This will navigate to the web form in the web browser window.
- Inside the loop, add three Populate text field on web page actions and select the web elements that correspond to the three fields in the web form: Name, Email, and Order. For each action, enter the variable that contains the data for that field, such as %ExcelColumn1% for Name, %ExcelColumn2% for Email, and %ExcelColumn3% for Order. This will fill the web form with the data from the current row in Excel.
- Inside the loop, add a Submit web form action and select the web element that corresponds to the submit button in the web form. This will submit the web form with the data from the current row in Excel.
- Save and run the flow. The flow will loop over each row in Excel, read the data, fill the web form, and submit it, without truncating the data.
