Description
This activity is used to read the contents of an Excel table. When adding this activity to a workflow, an 'Assign value to variable' action block connected to this block also appears. The result of the reading operation is saved to this variable.
However, using the 'Assign value to variable' activity is not strictly necessary in this case. It is safe to delete this block and handle the result any other way. For example, it could be possible to save the table 'as is' by connecting the 'Read Excel file' to 'Save table' activity and selecting the Save the previous step result in the 'Save table' block parameters.
One more important thing to remember: do not forget to close the Excel application if the table you are going to read using this activity is currently open in the application. Otherwise, Studio will not be able to get access to the file and execute the activity.
Studio Pro supports Microsoft Excel .xls and .xlsx file formats.
As of the 8.3 update, there are two ways of interacting with MS Excel - Microsoft Native and Generic. Navigate to the menu "Settings" → "Microsoft activities" to choose a method.
Microsoft Native uses standard Microsoft methods to interact with the Excel application. It requires Excel to be installed on your computer and extends the capabilities of interacting with all Excel functionalities.
Generic does not require Excel to be installed and may have some Excel functionalities limitations.
Parameters
Path is the main parameter here. It defines the path to the Excel file that needs to be read. It is a required parameter.
Path parameter
The next parameter is Excel sheet name. It lets you read the value from a specific sheet within the Excel file. Leave it blank if you would like to read the whole file.
Excel sheet name parameter
And the last parameter is Get dictionary key. It defines how the table headers will be represented.
With Use table headers option the first row of the table is taken as a header.
With Use column letters option standard Excel headers will be used (for example, A, B, C, etc.).
We will show examples of differences between these options in the next article section.
Get dictionary key parameter
Results
Now let us take a look at how the results of this activity are represented.
We will use this table with two sheets as an example.
The source table with two sheets
And here are the results. In this example, we saved the results using the 'Assign value to variable' activity.
Variable containing the read table data
Click the 'View' button to see more detail. Here we can see the data from the read table and two tabs for the corresponding sheets within it. In this case, we used the Use table headers option. The headers were copied from the table as we wrote them.
The saved data from the table. Use table headers option was chosen
And now let us have a look at another representation where Use column letters option is used. Here the headers we made in the tables are not taken into consideration and replaced by the letters.
The saved data from the table. Use column letters option was chosen