Search a keyword inside a Data Table
Assume that you have a Data Table type variable %QueryResult% and want to know if it contains a word ‘searchword’.
The %QueryResult% variable contains a Data Table type variable which is a complex data structure, meaning that it is not a single value, but consists of multiple rows and columns. To search inside a Data Table you will need to iterate through its rows first (using a “For Each” loop action) and then iterate through its columns using another, nested “For Each” action. Attached is an example on how to do this.
However, this may be slow, especially for large query results. The most optimal approach would be to search for the keyword directly though an SQL statement, which would return a TRUE/FALSE value, instead of the full result table.
The .Columns property of a DataTable variable is a list containing the names of the columns of the DataTable.
So, how do you do what you stated below, as you did not supply an example of below, where you can search a datatable variable using a Select SQL action ???
"From John Koumentis
The most optimal approach would be to search for the keyword directly though an SQL statement, which would return a TRUE/FALSE value, instead of the full result table."
I have updated the Process accordingly, so as to efficiently demonstrate topic contents.
From your original post, i had implied that there was a special way to do a direct exex sql to a datatable variable ,without going through an excel file intermediate stage. Apparently, my implication of what you described was different.
A team member from my company has entered a feature request for a new action to do a direct search of a datatable variable which will be beneficial as a new action,