Read an excel file and save it in datatable

My robot has to read a different excel file everyday and perform some steps with the data extracted, the sheet name is different everytime the file changes. I was trying to use read range activity and save it on a datatable variable.

My questions are:

How can i read a different sheet name everytime the robot runs?
how can i access the data in the proccess scope? Because this datatable will only exists when the robot is on execution, right?

You can have the excel file in a particular folder and using
Assign string, filelist=directory.GetFiles("location","*.xlsx") you can get the path of the excel,
Make sure that there is only the required excel files in your speciifed location

To get the sheet name you can follow the below post

Also to get the data extracted from excel you might need to store it somewhere like a queue

I’m trying to do this way but look at the error message when i try to read range


It says: “the process cannot access the file because it’s being used by another process”

This is the process

Just for test i’m trying to read range and write it in another file

Hi @guilherme.dias ,

Instead of the Workbook Read Range, Use the Read Range from Excel

The Error Happens because the Workbook Read Range is not part of the Excel Application Scope, and Hence it is Trying to Read the Same file that is already being Accessed by Excel application Scope.

1 Like

Just remove the excel application scope and use workbook read range and write range activities

It worked! Thank you!

Can you helpe me again? i’m trying to delete a column before read range, because there are 2 columns named “Quantidade” in the excel file, how can i do this?

@guilherme.dias ,

You could Try the Below Component where the Column Names will renamed if there are duplicates.

You can find the solution for the same in the below post

It was easyer to rename the column name using write cell, because this column will always be h1.

My question at this point is: after read range executes successfully, how can i access the datas stored in datatable? will the first value of the column in excel becomes the name of the column in datatable? I’ve marked this option in read range activity


@guilherme.dias , Yes. If you do Mark Add Headers, the First Row in Excel is Considered to be the Datatable Column Names.

Additionally, using the Datatable variable, we can access the row values of Each Column Names in the below way :

DatatableVar(0)(0)   //Accessing using Indices, First Row First Column Value
DatatableVar(0)("Column1")  //Accessing using Column Name, First Row, Column1 Value

Ok, thank you! All my questions were solved!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.