How to create a DataTable from Excel in UiPath
Creating a datatable is the most important step in automation process.
How we can create a datatable from Excel File?
Activity Name: Read Range
We have two different styles of reading an excel file and creating a data table.
First style is as shown below:
By using an Excel application Scope and a read range to create a data table.
Now let us see the properties of excel application scope
If the excel file has a password then you can pass that in the password property or if you want to edit the password you can do that in the Edit Password property.
If you check the AutoSave property, it will save the file automatically.
There is no input file but you want to write something then you need to check the CreateNewFile property which will create a new file.
Macrosetting has three properties:
- EnableAll – This enables all the macros for that excel.
- DisableAll – This disables all the macros for that excel.
- ReadFromExcelSettings – Which takes whatever the macro settings that are there for that excel.
If you don’t want to modify the excel file then check ReadOnly property.
If you want to see what is happening inside the excel then check Visible property.
Workbook property is used as an output argument which will be used as input for another excel application scope.
If you use this workbook output in the new excel application scope then the excel file path is not required.
ExistingWorkbook property is an input argument for the new excel application scope which is created in the workbook property of the previous excel application scope.
After the Excel Application Scope, we need to use Read Range Activity to create a data table.
Let’s see it’s properties as well now:
In Range property, you have to mention to which range of the excel it needs to read like(“A1:A13”) which will read from A1 to A13.
In the SheetName property, you have to mention which sheet to read if you have custom sheet names in an excel file.
If you want to read the headers from excel then check the AddHeaders property.
If you want to preserve whatever the format the initial excel is then check PreserveFormat property.
There are predefined filters in the excel file then use UserFilter property which keeps those filters as it is.
DataTable property is the output data table variable that stores the excel data.
Second style is as shown below:
Here there is no need for an excel application scope as we are using workbook activities.
Let’s see the properties now:
All the property’s functionality remains the same as discussed in the first style.
This is how you create a datatable from excel in UiPath.