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_CODE
    This 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_URL
    A 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_QUERY
    A 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_NAME
    Either 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_FILTER
    A 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_SORT
    A 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_TREATMENT
    Valid 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