Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

Unlike most of the other connectors in QVSource which are designed for and connect to specific APIs, this connector can extract data from any URL (for example, JSON or XML).

It therefore opens up the possibility of connecting to a huge range of potential APIs which have never even been considered for QVSource.

The trade off is that the user will need more knowledge of the underlying API in order to configure it correctly. You should also ensure that your usage of the API complies with their terms of service.


Features

This Connector provides the following features:

  • Allows you to make GET, POST, PUT or DELETE requests to any URL.
    • Allows you to subsequently get a table containing all the response headers from this request (Connector version 1.0.5 and later).
  • Allows you to read files (e.g. JSON files) directly from your local machine.
  • Allows JSON data to be automatically converted to XML data. This can then be either:
    • Loaded natively into QlikView.
    • Converted into a structured table for QlikView (optionally using an XPATH expression to extract a sub document).
  • Allows you to make requests to SOAP Web Services.
  • Allows the request url to be encrypted, allowing you to avoid distributing sensitive parameters in your QlikView load scripts.
  • Allows a user set a cookie to be sent (could be useful for accessing secured web resources)
  • Allows a custom http headers to be set (version 0.9.3 and later).
  • Allows a minimum time to be set between requests (useful for some APIs which enforce limits on how many requests can be made per second).
  • Allows optional basic authentication credentials to be specified. Note that if you are trying to connect to an OAuth2 based API, this might still be possible with this connector if the service provides a mechanism for you to generate your own access token in their web interface which you can then send (e.g. as a header or url parameter) using this connector. The Survey Monkey example illustrates this.
  • Allows the response to be cached up to a user specified time to minimise requests to the API (for example where the results change infrequently or never).
  • Allows the content type of the request to be configured.
  • Allows the User Agent of the request to be configured.
  • Allows high volume of requests to be made on parallel threads using the asynchronous feature (for the XXXXToTable tables only).
  • Allows the response of an API (or any http request) to be saved directly to the local file system. For example you can download images and then binary load them into your QlikView application.
  • Allows you to access the headers returned from the response using the ResponseMetaData table.
  • Allows you to set a timeout for the request (increase or decrease it from the default of 180,000ms or 3 minutes) (Version 1.0.6.5 of Connector and later).
  • Allows you to configure a retry for the request in the event of a failure after a specified number of milliseconds (Version 1.0.6.5 of Connector and later).

The data that is returned can then be streamed directly into QlikView or Qlik Sense as XML, making use of Qlik's native XML parsing functionality. Alternatively there are tables in the connector which will attempt to convert the data into QVX tables.

Examples

Additional to this main documentation page, the following other specific examples of using this connector can be found (note that some of these use the previous version of this connector with a slightly different UI):
  • This blog post with detailed information on connecting to the Zeit Online API.
  • Here where we have a wiki article showing how this Connector can be used to connect to the Asana API.
  • This example illustrating how it can be used to call a SOAP Web Service.
  • This example illustrating how it can be used to access the Zendesk API.
  • This example illustrating how it can be used to access the Desk.com API.
  • This example illustrating how it can be used to access the Eloqua API.
  • This example illustrating how it can be used to access the Survey Monkey API.
  • This example illustrating how it can be used to access the RavenDB HTTP API.
  • Below is an example of using Google's Geocoding API.
  • This example illustrating connecting to Trello.
  • Wufoo.

And we have also seen successful connectivity to:
  • The appFigures and App Annie APIs.
  • The EDI-Soft Shipping Exporter (a SOAP Web Service).
  • The siteconfidence API.
  • The DocuShare API.
  • The Twinfield API.
  • The Silverpop Engage API (Don't forget to add xml= in front of your POST request to https://apix.silverpop.com/XMLAPI, this was not visible in the version of the API docs at the time. You may also need to set the content type to text/xml;charset=UTF-8 or application/x-www-form-urlencoded - we have not received definitive confirmation on the correct combination here).
  • The New Relic API - note when using their API explorer set the header input in the QVSource Connector to X-Api-Key:XXXXX and if there is a -d parameter in the curl example this becomes a query parameter at the end of the request URL.

And heard of users successfully connecting QlikView or Qlik Sense to:
You could also use this connector to connect to the many APIs hosted on Mashape by setting the X-Mashape-Key: XXXXXXXXXXXXXXXXXXXXXXXXX header (although we also have a dedicated Mashape Connector in beta, this General JSON/XML/SOAP Web API Connector has more features at time of writing).

Usage

The screen shot below shows the connector configuration screen:

Image

And the tables which the connector currently provides.

Example

In this example we will try using the Google Geocoding API.

PLEASE NOTE: You should always ensure that you are adhering to an APIs terms or use. This is used as a simple demo to illustrate the functionality of this connector only.

Method 1 - Working with the Raw API Output

We enter one of the urls in the documentation page into the URL input in the connector. In this example we use:
http://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&sensor=false

Image

We can then select the JsonToXmlRaw table and view the QVSource processed response - Note that QVSource has converted the JSON response to XML for us and provided us with a URL which we can then use in QlikView:

Image

To use this we first click the option to copy the URL to the clipboard. In QlikView we then find and click the 'Web Files' button in the QlikView Script editor:

Image

We then copy the generated URL:

Image

And paste it into the QlikView File Wizard:

Image

And then on the next step ensure that Xml is selected:

Image

And we now have access to QlikView's built in XML parsing and loading functionality.

Method 2 - Using QVSource to generate a table

The other option we have is to use QVSource's ability to attempt to convert the response into a table. If we just select the 'JsonToTable' table then we see the following:

Image

This is because the response of the API can actually be interpreted as a number of different tables and QVSource is only extracting one of them (in this case a single 1x1 table with the status response).

To better understand this we can simply paste the QVSource generated URL into a browser and observer the actual response (note that this is the JSON response from the API after it has been converted to XML by QVSource):

Image

If we wish to just pull out the location as a single table we can enter:
DATA/results/geometry/location

into QVSource as shown here and then run the table again:

Image

And we can load this into QlikView in the usual QVSource way of copying the load script from the 'QlikView Load Script' tab:

Image

NOTE: That after further testing you will see that sometimes the API returns multiple results elements. In this case the above would need further refinement. You could for example load all the matching results into your QlikView application or perhaps use the xpath DATA/results[1]/geometry/location to return only the first match.

Troubleshooting

xmlParseCharRef Error

If you get an error which looks something like this:
error on line XXXX at column Y: xmlParseCharRef: invalid xmlChar value Z

With one of the tables (likely the JsonToXmlRaw table when viewing the response in either a browser or attempting to load it into QlikView), it might be because the API is returning a an invalid character which is not allowed in XML.

For example, in one case it appeared that one of the values in the JSON response was a control character \0007 (which appears to the the unicode BELL character) which was converted to  in the XML response, making it unable to be parsed.

We have not yet built any feature into the connector to parse these out as we believe it is an error that they are returned by the API.
As of version 1.0.6 of this connector, for requests to the JsonToXmlRaw table, you may manually add the parameter cleanXML=true to your request which causes a regex to be run to removed to remove certain invalid characters ({&#(0|1|x1|2|x2|3|x3|4|x4|5|x5|6|x6|7|x7|8|x8|11|xb|12|xc|14|xe|15|xf|16|x10|17|x11|18|x12|19|x13|20|x14|21|x15|22|x16|23|x17|24|x18|25|x19|26|x1a|27|x1b|28|x1c|29|x1d|30|x1e|31|x1f)}).

SSL Issues (System.Net.WebException/System.IO.IOException

If you see the following error:
System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a send. System.IO.IOException: Received an unexpected EOF or 0 bytes from the transport stream.

Along with the following stack trace in your QVSource log file:

System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a send. ---> System.IO.IOException: Received an unexpected EOF or 0 bytes from the transport stream. at System.Net.FixedSizeReader.ReadPacket(Byte[] buffer, Int32 offset, Int32 count) at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest) at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest) at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest) at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)

This might be because the service you are trying to connect to uses an older version of SSL (e.g. SSL2). Due to recent exploits it is generally recommended that only the latest TLS version of SSL is used.

SSL Issues (The remote certificate is invalid according to the validation procedure)

If you see either of the following:
The remote certificate is invalid according to the validation procedure
Or
Could not establish trust relationship for the SSL/TLS secure channel

In your log files or error messages then QVSource is unable to ensure the validity of the SSL certificte. This is not something we can fix in QVSource (as doing so would make the product less secure) however the posts here, here and here point to a workaround which involves saving the certificate to your local machine and installing it into the local trusted root certificate authorities store. Note you do this at your own risk and we cannot guarantee it will fix the issue.

Additional Notes

If JSON is returned with element names beginning with a number, e.g.:

{ 12-12-2013: "value" }

QVSource will convert this to to N_12-12-2013 to make it a valid XML element name.

Change Log

1.2.3 - 06/04/16
  • The JsonToXmlRaw and JsonToTable tables should now better handle cases where JSON contains double colons and $'s in element names (e.g. {"$", "value"} or {"name1::name2", "value"}).

1.2.2 - 14/02/16
  • Some parameters no longer unnecessarily appear in the generated URL if they have their default values.
  • Fixed bug with Encrypt URL input parameter not working in Web Edition.

1.2.1 - 04/02/16
  • POSSIBLE BREAKING CHANGE: Removed support for using cleanXML parameter in request URL (see earlier in change log) as this has now been superceded by removeInvalidChars parameter which should be used instead.
  • Removed 'Remove Invalid Characters' input parameter from JsonToTable and RawXmlToTable tables.
  • Added code to sanitise XML before processing in the case it might contain invalid characters.

1.2.0 - 22/01/16
  • POTENTIALLY BREAKING CHANGE: We had some custom code to replace certain special characters which are valid JSON but invalid XML with safe values for XML. We have now removed this code and internally use some system level .NET code which should account for all potential invalid values but now uses different replacements. These replaced values might ultimately end up being table column names depending on whether you are using a connector table which provides raw XML or a structured table and you may need to adjust your scripts accordingly according to the following. This is actually a QVSource wide change but the most likely place it will impact is in certain specialist uses of the General Web Connector.
    • Any element name starting with 0 - 9 was previously replaced with N_?, e.g. if in JSON you had {100: "test"} this '100' would have been converted to N_100. This will now be converted to _x003?_ where ? is the digit. e.g. {100: "test"} will now have its name converted to _x0031_00.
    • - would have been replaced with _dash_, will now be replaced with _x002D_
    • ' ' (space) would have been replaced with _space_, will now be replaced with _x0020_
    • / would have been replaced with _slash_, will now be replaced with _x002F_
    • + would have been replaced with _plus_, will now be replaced with _x002B_
    • ! would have been replaced with _exclamation_, will now be replaced with _x0021_
    • @ would have been replaced with _at_, will now be replaced with _x0040_
    • ' would have been replaced with _apostrophe_, will now be replaced with _x0027_
    • ` would have been replaced with _apostrophe2_, will now be replaced with _x0060_
    • < would have been replaced with _lessthan_, will now be replaced with _x003C_
    • > would have been replaced with _greaterthan_, will now be replaced with _x003E_
    • , would have been replaced with _comma_, will now be replaced with _x002C_
    • ? would have been replaced with _questionmark_, will now be replaced with _x003F_
    • ( would have been replaced with _openbracket_, will now be replaced with _x0028_
    • ) would have been replaced with _closebracket_, will now be replaced with _x0029_
    • * (asterix) would have been replaced with _star_, will now be replaced with _x002A_
    • Anything else which was not covered before should be converted correctly to its matching unicode character.

1.1.3 - 14/12/15
  • Added feature allowing @file=drive:\path\to\file.txt for the POST parameter value so that the connector will pick up the text from here instead of the parameter value itself. This is because occasionally (particularly for SOAP requests) the URL generated to QVSource can be very long and exceed the length supported by QlikView or Qlik Sense.

1.1.2 - 30/11/15
  • Last request time (for the minimum time between requests feature) is now stored in settings rather than in memory against connector instance (which fixes compatibility for new web edition).

1.1.1 - 25/09/15
  • Added an "Is File" input parameter to the RawResponse and SaveToDisk tables - you can check this if you know the endpoint is returning a binary file and prevent the connector from attempting to interpret it as an API response (e.g. JSON, XML).

1.1.0 - 10/09/15
  • Headers Delimiter input parameter added. This can be useful if you have multiple custom HTTP headers to add and one of them contains a comma (which is the default delimiter).

1.0.9 - 18/08/15
  • Added a cookie container which maintains any cookies set for the lifetime of the running application (e.g. resends them with new requests) and a new Cookies table which allows these to be extracted.

1.0.8.0 - 30/07/15
  • Added a regular expression filter option to filter out part of the response before being sent to client. This is an experimental feature which may be removed if unsuccessful.

1.0.7.0 - 13/04/15
  • Added PATCH as a HTTP verb option.

1.0.6.9 - 11/02/15
  • flattenXML parameter renamed to flattenHierarchies.

1.0.6.8 - 09/02/15
  • Added new flattenXML check box to JsonToTable and RawXmlToTable tables (only used when XPATH supplied) which attempts to flatten out any nested XML hierarchies.

1.0.6.7 - 04/02/15
  • JsonToXmlRaw, RawXmlToTable and JsonToTable tables now throw more informative exception if the API returns an empty response.

1.0.6.6 - 03/12/14
  • Added note to Request Method (verb) parameter.

1.0.6.5 - 24/11/14
  • Minor performance improvements.
  • Allows you to set a timeout for the request (increase or decrease it from the default of 180,000ms or 3 minutes) (Version 1.0.6.5 of Connector and later).
  • Allows you to configure a retry for the request in the event of a failure after a specified number of milliseconds (Version 1.0.6.5 of Connector and later).
  • Progress form now shown as request is being made.

1.0.6.4 - 17/09/14
  • @ symbol in JSON key names (which is invalid if converted to XML) now replaced with _at_ when converted to XML.
  • Minor edit to request stamp label.

1.0.6.3 - 22/08/14
  • Added a new 'Remove Invalid Characters' input check box to many tables. This adds a removeInvalidChars=true paramater (which replaces/supercedes the previous cleanXML parameter which could be manually added for the JsonToXmlRaw table).

1.0.6.2 - 21/08/14
  • Added � (\u0000) to the list of characters removed when &cleanXML=true is added as a parameter to the JsonToXmlRaw table.

1.0.6.1 - 28/07/14
  • Added note to RawResponse table suggesting users try the SaveToDisk table if the response from the RawResponse table cannot be read successfully.

1.0.6 - 03/07/14
  • Added option to add cleanXML=true to the request query to the JsonToXmlRaw table to remove certain invalid characters (actually this regex string {&#(0|1|x1|2|x2|3|x3|4|x4|5|x5|6|x6|7|x7|8|x8|11|xb|12|xc|14|xe|15|xf|16|x10|17|x11|18|x12|19|x13|20|x14|21|x15|22|x16|23|x17|24|x18|25|x19|26|x1a|27|x1b|28|x1c|29|x1d|30|x1e|31|x1f);}),

1.0.5 - 28/05/14
  • Added cache time input to SaveToDisk table.
  • Added ResponseMetaData to extract headers from a previous request's response.
  • Fixed bug with cookie input parameter.

1.0.4 - 10/02/14
  • User Agent input added.

1.0.3 - 03/01/14
  • Should now correctly show other xml text encodings (e.g. "ISO-8859-1"). Note that the XML preview window for raw the response tables still might not show the correct characters. Please check the plain text version or browser response.

1.0.2 - 18/09/13
  • Cookie container now set on http request. This allows the request to follow, for example, the export to CSV link for a publicly shared google spreadsheet.

1.0.1 - 09/09/13
  • Fixed bug with encrypted urls.

1.0.0 - 04/09/13
  • Initial version and replacement of V1 of this connector.
  Name Size
- browser rendering.png 10.04 KB
- config.png 36.03 KB
- gecode xml result.png 71.19 KB
- geo table.png 12.47 KB
- gmap url.png 14.49 KB
- grab url.png 9.92 KB
- load script.png 63.92 KB
- OK table only.png 45.92 KB
- qvsource url.png 10.27 KB
- tables.png 11.52 KB
- web files button.png 6.26 KB
- xml option.png 16.88 KB
- xpath.png 47.54 KB


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd