Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today

JSON Loader

Luminary
Luminary

JSON Loader

Hey,

I hope I can solve a lot of peoples headache with a better way of loading JSON data.

This method works by loading the JSON data into an table with a single field, and then placing it into a variable and then converting the value into xml which then is loaded. Works very well but is still a bit for the technical people. I will not provide any support.

In order to make this work, we need to detect the XML definition after JSON to XML conversion. Open the JSON Loader.qvw Application and the JSON url.

Copy the JSON output into the vData variable, and click the JSON to XML button. Now take the output in the left side and copy that into a .xml file and load it from qlikview.

The output script should look similar to this:

TAG:

LOAD adminCode3,

    adminName2

FROM [Desktop\xml.xml] (XmlSimple, Table is [XML/POSTALCODES/TAG]);

From this we need to extract 2 things. The list of fields (place into vFields), and the XML tree to load (place into vXMLTree).

In the edit script set the variables based on the above:

Let vUrl = 'http://api.geonames.org/postalCodeLookupJSON?postalcode=6600&country=AT&username=demo';

Let vFields = 'adminCode3,adminName2';

Let vXMLTree = 'XML/POSTALCODES/TAG';

Cross fingers and reload

Do this for each JSON source.

Enjoy Torben Seebach 4C Management Consulting

Tags (2)
Labels (1)
Attachments
Comments
Creator III
Creator III

Hi Christoffer,

Do you have any information on how to load this data.

Thanks

0 Likes
Contributor II
Contributor II

The new connector in Sense solved the problem for me.

0 Likes
Creator III
Creator III

Can you please tell me which connector you are using?

0 Likes
Contributor II
Contributor II

In QlikSense 3 there is a built-in REST API connector.

0 Likes
Specialist
Specialist

REST in Qlik sense will do it.

0 Likes
Contributor II
Contributor II

Hi Guys,

I am trying to read JSON data directly from a text file.

any one had success with that?

0 Likes
Contributor II
Contributor II

FYI
To Read JSON (text) files I installed MS IIS on my computer.

I then created a folder for the JSON files I had on the default web site.

Now I can use the REST Connector with "localhost\JSON_Folder" in the URL.

0 Likes
Contributor II
Contributor II

Hi Torben, thank you very much for this post, really helpfull. I have some issues with special characters so I added some of the problematic ones in the macro code:

      Case "<"

      name = name + "&lt;"

      Case ">"

      name = name + "&gt;"

      Case "&"

      name = name + "&amp;"

      Case "["

        name = name + "("

      Case "]"

        name = name + ")"

the same for value...

0 Likes
Creator
Creator

Hi Guys

I just tried the following with QV12

LOAD id,program

FROM

(json, codepage is 1252);

and it works fine with this file:

[{"id":"500","program":"prog003","number":"333525","datetime":"2017-01-03 15:45:15","zone":"north","amount":"100"},

{"id":"501","program":"prog003","number":"333526","datetime":"2017-01-03 15:48:30","zone":"north","amount":"300"}, {"id":"502","program":"prog003","number":"333527","datetime":"2017-01-03 17:25:23","zone":"south","amount":"150"}]

BUT Qlik support said this shouldn't work ... and I tested that more complex json structures will not be laoded

Contributor
Contributor

Worked for me

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2012-06-20 03:04 PM
Updated by: