Solved: Looping through excel data

vaibhavtandon87 · ‎07-08-2021

Hi community,

I

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:

tkuehara · ‎07-08-2021

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".

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:

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

The flow would look something like this:

View solution in original post

TJP-RPA · ‎07-08-2021

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 · ‎07-08-2021

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:

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:

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):

vaibhavtandon87 · ‎07-08-2021

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

vaibhavtandon87 · ‎07-08-2021

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!

tkuehara · ‎07-08-2021

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".

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:

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

The flow would look something like this:

vaibhavtandon87 · ‎07-08-2021

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!

tkuehara · ‎07-09-2021

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.

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