DataTable variables represent a type of variable that can store big pieces of information, and act as a database or a simple spreadsheet with rows and columns. They can be found in the Browse and Select a .Net Type window, under the System.Data namespace (System.Data.DataTable). For more information, see Browsing for .Net Variable Types.
These variables can be useful to migrate specific data from a database to another, extract information from a website and store it locally in a spreadsheet and many others.
To exemplify how you can use DataTable variables, we are going to create an automation that reads only two out of multiple columns from an Excel spreadsheet, and then transfers them to another spreadsheet that already contains other information.
The initial file is a database of people, transactions, dates, and products. In this example, we are going to extract their names and order dates and append them to an Excel spreadsheet that already contains similar information.
- Create a new sequence.
- Add an Excel Application Scope activity to the sequence. This activity is required for most of the Excel-related activities.
If you do not have Excel activities installed on your version of UiPath, use the Manage Packages functionality to get them.
- Create two DataTable variables,
datDate. These are going to be used to store information from the initial Excel spreadsheet.
- In the Properties panel, in the WorkbookPath field, type the path of the initial Excel file to be used, between quotation marks.
- Add two Read Range activities and place them one under the other, in the Excel Application Scope activity. These are used to get information from the initial spreadsheet.
- Select the first Read Range activity and, in the Properties panel, in the Range field, type "G7:G37". These are the Excel table coordinates that tell UiPath Studio from where to extract information.
- In the SheetName field, do not make any changes as the name of our sheet is the default one, Orders.
- In the DataTable field, type the name of the first DataTable variable,
datNamesList. This variable stores all the information available between the G7 and G37 rows.
- (Optional) Change the value in DisplayName field to Read Names, so you can easily tell apart this activity from the second one.
- Select the second Read Range activity, and in the Properties panel, in the Range field, type "C7:C37". These are the Excel table coordinates that contain the order date we want to extract.
- In the DataTable field, specify the
datDatevariable. This variable retains all the date information we require.
- In the Activities panel, under System > File > Workbook, add a Write Range activity to the Designer panel, under the Excel Application Scope. This activity is used to write the stored information to another Excel file.
The file used with the Write Range activity has to be closed when you run the project. If it is not closed, an error is displayed and the automation execution stops.
- In the Properties panel, in the WorkbookPath field, type the path of the Excel file to be used to store all the information gathered at the previous steps.
- In the DataTable field, type the
- In the SheetName field type Database, and in the StartingCell, type "B7.“ This is the starting cell in which information from the initial file is to be added.
- Add another Write Range activity and place it under the first one.
- In the Properties panel, fill in the WorkbookPath and SheetName fields as for the previous Write Range activity.
- In the Starting Cell field, type "A7".
- In the DataTable field, type the
- Press F5. Your automation is executed.
- Double-click the final Excel file. Note that the copied information is available, and correctly updated.