Function: WebGetSingleFromTable
Extract a single value (number, text, etc.) from a source reference, using query conditions that identify both a table and the row(s) and column that contain the specific data value of interest.
Usage
-
HTTP GET
https://www.xs.codexframework.com/api/WebGetSingleFromTable?cc=CLIENT_CODE
&url=ENCODED_URL
&query=TABLE_CRITERIA_QUERY
&tableColumnName=TABLE_COLUMN_NAME
&tableValueFilter=TABLE_VALUE_FILTER
&tableValueSort=TABLE_VALUE_SORT
&sdt=SERVER_DATE_TREATMENT
Parameters
-
CLIENT_CODEThis is an authorization token that identifies your xSkrape.com account to which any credit charges will be applied. You can locate this code by logging into your xSkrape.com account, under My Account - Queries. (Remember: it's your duty to secure your client code against unauthorized use.) Required.
-
ENCODED_URLA reference to the source data to be queried. Can be a reference that is HTTP or HTTPS based. Can optionally be an expression that supports loading data across multiple requests (or performs other special parsing), as described here. The text needs to be URL encoded. Required.
-
TABLE_CRITERIA_QUERYA query specification to isolate a specific table from the raw source data. Details about the query syntax are covered here. If there are multiple tables that meet the given criteria, the first table is used. Optional. If this is omitted, uses "tableindex=1" as the default value. The text needs to be URL encoded.
-
TABLE_COLUMN_NAMEEither the resolved column name from the matched table (as show for example in Page Explorer), or the column number (position-based, starting at zero). One can optionally apply one of the following aggregates against the column: SUM, AVG, MIN, MAX, COUNT, FIRST, LAST. (e.g. "SUM(ColumnName)") Required.
-
TABLE_VALUE_FILTERA filter expression that can be applied to the table to help isolate a candidate row or rows of interest. The format for the expression is more or less the same as described here. Optional.
-
TABLE_VALUE_SORTA string that contains the column name followed by "ASC" (ascending) or "DESC" (descending). Columns are sorted ascending by default. Multiple columns can be separated by commas. Optional.
-
SERVER_DATE_TREATMENTValid values include "fromdate" which implies there is no date conversion undertaken (or an embedded explicit time zone within a matched date is used), or names a specific time zone which is assumed to apply to any matched date. The time zone name if given must be one of the standard time zone names listed here. Optional.
Return Value(s)
- Returns JSON in all cases (success and failure).
- Format: { "Success":true|false, "Message":"message", "Source":"source", "NoData":true|false, "RobotsTxtWarning":true|false, "Truncated":true|false, "dt":"datatype", "v":"value" }
- Success: Indicates overall success or failure status of the request.
- Message: In the case of failure, provides an error message.
- Source: In the case of failure, identifies the origin of the message.
- NoData: Indicates whether the query returned any data or not.
- RobotsTxtWarning: If true, indicates that the accessed web site has a robots.txt file that may imply disallowed access (check with the site administrator or terms of use).
- Truncated: If true, indicates that the data result was truncated due to metering settings on your xskrape.com user profile.
- DataType: A string that identifies the type of the data value returned. (Valid values are based on .NET data types, e.g. "int32", "decimal", etc.)
- Value: The string representation of the data value returned from the query. Date handling is described in more detail here.
Remarks
This function can be used to extract a single value from a tabular data source such as can be embedded in a larger HTML page or Excel workbooks - or "native tabular" sources such as comma/tab separated files. As opposed to WebGetSingle, focus is given to identifying the value(s) of interest through "stronger" references such as column names, filter expressions, etc. Also, aggregations can be applied over multiple rows. Use of sorting along with functions such as FIRST and LAST can identify "top 1" values.
Examples
-
cURL
1. Pull data from a HTML page - use a row filter and an aggregate over a paged grid
In this example we use the "curl" command line tool to illustate calling the WebGetSingleFromTable API.
curl --data-urlencode "cc=5e....2e" --data-urlencode "url={ url:""https://www.codexframework.com/xskrape/sampledata1?page={0}"", method:""step_sequential"", stop_on:""any"", arguments:[{start:1, step:1}] }" --data-urlencode "tableColumnName=AVG(Quantity)" --data-urlencode "tableValueFilter=Quantity > 400" -G "https://www.xs.codexframework.com/api/WebGetSingleFromTable"
Results:
{"v":"1663", "dt":"Int32", "Success":true, "Message":null, "Source":null, "NoData":false, "RobotsTxtWarning":false, "Truncated":false}
Of note, we've omitted most of our client token: you would substitute your own, as found when you log into your xSkrape.com account. Various parameters are encoded as part of our request to WebGetSingleFromTable, and the result shows a successful completion (Success = true). The value our query was trying to fetch is based on treating an HTML paged table as a consolidated row source, where we're filtering for items with a Quantity more than 400, returnig the average quantity value. This is returned as an integer as shown in the resulting JSON.
-
jQuery
2. Pull data from a HTML page - use a row filter and an aggregate over a paged grid
This is effectively the same example as above, except implemented using a JQuery get call.
<input type="text" id="result" />
<script type="text/javascript">
var requestUrl = "https://www.xs.codexframework.com/api/WebGetSingleFromTable";
var parm = {
cc: "5e.....2e",
url: '{ url:"https://www.codexframework.com/xskrape/sampledata1?page={0}", method:"step_sequential", stop_on:"any", arguments:[{start:1, step:1}] }',
tableColumnName: "AVG(Quantity)",
tableValueFilter: "Quantity > 400"
};
$.get(requestUrl, parm).done(function (data) {
if (data.Success) {
$("#result").val(data.v);
} else {
$("#result").val(data.Message);
}
});
</script>
Related
- WebGetSingleFromTable as documented for the xSkrape Excel Web Add-in
- WebGetSingleFromTable as documented for the xSkrape Excel Desktop Add-in
- WebGetSingleFromTable as documented for SQL Server