cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vaibhavtandon87
Helper IV
Helper IV

Looping through excel data

Hi community,

 

I

Read excel.jpg

Read excel data in loop.jpg

Submit button.jpg

   

am using for each loop to get data from excel sheet which has 2 columns and 5 rows but getting error 

 

"Variable 'ExcelData' doesn't have a property 'Name'."

 

The column names in excel are - Name, Class

 

Below are the screenshots of workflow:

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

No problem @vaibhavtandon87 

 

It means I'm reading the data table content one row at a time. This is achieved by the "For each" action. You could read as "For each row in excel data".

tkuehara_0-1625757819979.png

I left the default name created by PAD when I added the "For each" action (in this case, %CurrentItem3%), but you could use a more meaningful name (%ExcelRow%, for example). To change its name, click on the name and then edit it:

tkuehara_1-1625758058616.png

When you run, it will create a variable with two columns:

tkuehara_2-1625761501633.png

 

The flow would look something like this:

macro_flow.png

View solution in original post

11 REPLIES 11
TJP-RPA
Frequent Visitor

Hi @vaibhavtandon87,

 

I suggest, that you expand the "Advanced" tab in your "Read data from Excel worksheet" and select the "My data has headers" option.

tkuehara
Super User
Super User

Hi @vaibhavtandon87 

 

You'll need to make a few changes:

Change your "Read from Excel worksheet" action to include the first row and set it as a header row:

tkuehara_0-1625748397969.png

Also, I see you are using the value from the "Name" column to populate a web page field. You'll need to change it in order to read each row. Example:

tkuehara_1-1625748694673.png

I'm reading each row from the Excel and storing it in the %CurrentItem3% variable, thus the reference to %CurrentItem3['Column1']% and %CurrentItem3['Column2']% (those are the column names in my sample Excel file):

tkuehara_2-1625748883146.png

 

Thanks @TJP-RPA for your prompt response. Its already activated.

Thanks @tkuehara  for your prompt response. 

 

When you say 'I'm reading each row from the Excel and storing it in the %CurrentItem3% variable' what do you mean by that? In the read to excel step is the Currentitem3 3 variable created or you separately created that variable? If custom variable could you please explain how?

More screenshots of complete workflow and properties would be helpful.

 

Thanks for the help!

No problem @vaibhavtandon87 

 

It means I'm reading the data table content one row at a time. This is achieved by the "For each" action. You could read as "For each row in excel data".

tkuehara_0-1625757819979.png

I left the default name created by PAD when I added the "For each" action (in this case, %CurrentItem3%), but you could use a more meaningful name (%ExcelRow%, for example). To change its name, click on the name and then edit it:

tkuehara_1-1625758058616.png

When you run, it will create a variable with two columns:

tkuehara_2-1625761501633.png

 

The flow would look something like this:

macro_flow.png

Thanks @tkuehara  that worked!

 

One quick check - if we are using current item to iterate through in for loop then what is the co relation of read excel data parameters like start column, end row? What I mean is if iterate step is automatically parsing through all the items until last row/column having data then why to explicitly mention column numbers/ row number etc in read from excel step?  Thanks!

Glad to help @vaibhavtandon87 !

 

I'll try my best to explain it.

The read from Excel is the "bridge" to retrieve the data stored in your "physically stored data". You need to explicitly mention column/row numbers because PAD needs those parameters to "read" from the Excel file. Here you can also "filter" columns/rows, for example: if your worksheet has 5 columns but you only need data from the first 3 columns. Ater grabbing that data, PAD creates an in-memory data table structure which is  basically a virtual copy from the Excel file data.

tkuehara_0-1625843711497.png

The correlation among those items could be illustrated as follow (forgive the poor drawing skills lol):

excel_file_PAD.png

vaibhavtandon87
Helper IV
Helper IV

Thanks for the explanation, it really helps!

avm
New Member

I don't see "read from excel worksheet" as an option in Power Automate web - is it only available on desktop?  Thanks

It is definitely available on desktop, but this is a desktop forum.  You might have more luck in the Power Automate (Web) forum.

VJR
Super User
Super User

Hi @avm 

 

To read Excel data in Cloud Flows you can grab the Excel connector and see a list of available actions.

One of them is List rows present in a table.

And yes you will get more help on Cloud Flows here.

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (3,248)