topic Re: How to load json data in qliksense in Connectivity & Data Prep

How to load json data in qliksense

shus_lopez — Fri, 02 Apr 2021 08:07:40 GMT

Hello everybody, i need to load data from json file in qlik sense, but i dont know how to do, i read in this forum maybe with rest connector but i dont achieve yet.

Re: How to load json data in qliksense

stevedark — Sat, 04 Nov 2017 17:22:28 GMT

Hi Jesus,

You are correct that you need to use the REST connector. This is a separate download that you will need to make and install on the same machine you are running Sense. You can download it from here:

https://da3hntz84uekx.cloudfront.net/RESTConnector/1.0/12/_MSI/QlikRESTConnector_x64setup.exe

The REST connector can only load from HTTP or HTTPS files, so you will need to ensure that your JSON file can be accessed in this way. You can do this by putting your JSON file on a web server, or a cloud service that allows you to get a download link.

I have just tested this by putting a JSON file into dropbox, requesting a link, changing the download parameter from 0 to 1 and using that as the endpoint for the rest connection.

As the file is not secured at this point (it just has an impossible to guess file path) you can just enter than into the URL of the REST connector and leave all other fields blank.

Hope that gives you enough pointers to solve this. Post back if not.

Cheers,

Steve

Re: How to load json data in qliksense

nikhilgarg — Wed, 06 Jun 2018 12:01:19 GMT

Hey Steve,

i am not able to load my JSon file this way. Can you help me knowing the steps to implement this?

Re: How to load json data in qliksense

stevedark — Sat, 09 Jun 2018 15:50:43 GMT

What have you tried so far? Is your JSON file available at a URL that the Qlik load script can see?

Is it data that can be shared, if so if you place it at a URL that is publicly available then I can test. If not you could try replicate the file with dummy data to try it.

Re: How to load json data in qliksense

nikhilgarg — Mon, 11 Jun 2018 06:31:40 GMT

Hey Steve,

It works fine. There was some structure issue.

Re: How to load json data in qliksense

stevedark — Mon, 11 Jun 2018 07:31:50 GMT

Glad it's working. Thanks for posting here and letting me know.

Re: How to load json data in qliksense

arvind1494 — Mon, 11 Jun 2018 08:06:33 GMT

Rest connector is the best way

Re: How to load json data in qliksense

Suus — Wed, 11 Jul 2018 14:20:48 GMT

You can read json files directly from a directory.

In QlikView this option was available from table load. In Sense it dissapeared.

The script still works though 😉

Here's an example

YSIS_BarthelScore:

LOAD

PatientID

, Barthelscore

, Datum_van_afname

, MomentAfname

from [lib://YSIS/barthelscore.json] (json, codepage is 1252, embedded labels);

If you dont know the column names first perform a select*
Create a connection to your directory

Re: How to load json data in qliksense

stevedark — Sun, 15 Jul 2018 11:39:38 GMT

That's a neat solution. Thanks for sharing Suzan.

Steve

Re: How to load json data in qliksense

petter — Sun, 15 Jul 2018 11:48:56 GMT

Just be aware that JSON is per definition required to be UTF-8 or it is not JSON. So codepage 1252 should be avoided actually.

One limitation of the json LOAD for QlikView and Qlik Sense is that it is not handling any kind of nesting. That means that it is only good for "flat" JSON files.

Re: How to load json data in qliksense

Ajeeth11 — Fri, 28 Dec 2018 11:17:59 GMT

Hi all,
how to load nested JSON into qlik sense?
Can you please help me out.

Thanks,
Ajeeth

Re: How to load json data in qliksense

petter — Mon, 31 Dec 2018 07:31:46 GMT

It is not always that sticking to Qlik only is the right approach. Maybe use a tool like jq to parse the nested JSON into a structure that Qlik can easily read:

Reshaping JSON with jq

Re: How to load json data in qliksense

petter — Sun, 30 Dec 2018 09:52:31 GMT

If you use the Apache Drill connector that's included it can parse from file system and nested structures. The downside is that you have to install the free Apache Drill driver additonally.

An alternative could be to use the Presto connector - but it comes with the additonal pain of installing Presto just as you have to do with Drill...

Re: How to load json data in qliksense

Ajeeth11 — Tue, 05 Feb 2019 06:09:50 GMT

Hello petter,

Can you please explain briefly? .I have installed apache drill driver but it is asking host name details?

Note: my json file is in local only.

Thanks,

Ajeeth

Re: How to load json data in qliksense

petter — Tue, 05 Feb 2019 06:33:42 GMT

You computer where you installed the Apache Drill driver will be the host so the host name is localhost (or IP address 127.0.0.1 as the host name) or your computers Windows computername.

Since you installed Drill on your own computer you will also be able to read JSON files from local drives. This is a setup I have installed several times.

The only thing that is a drawback with installing Drill is from a simplicity perspective since you have a so-called Drill Bit on your computer running as a service. So there are more moving parts than you would expect from a normal driver. You will have to start the Drill Bit each time you use Qlik or have it running as a service all the time.

Re: How to load json data in qliksense

Ajeeth11 — Wed, 06 Feb 2019 07:02:40 GMT

Hello Petter,

Thanks for your Quick response.
While configuring mapr drill driver,I am getting error called "No connection could be made because the target machine actively refusing it".
Can you please help me out for this.while configuring what all the steps you followed?

Thanks a lot petter.

Regards,
Ajeeth

Re: How to load json data in qliksense

petter — Wed, 06 Feb 2019 08:28:58 GMT

You might have a firewall that blocks the port that is necessary for Drill to use.

https://drill.apache.org/docs/ports-used-by-drill/

So it seems that port 8047 needs to be opened in your computers firewall...

Re: How to load json data in qliksense

divyasavanth — Fri, 08 May 2020 11:41:52 GMT

Hi Shevedark,

How do i get this JSON file which is a URL into qliksense. Please suggest. I want to give URL as the input

https://gicwebsrv.csuchico.edu/gicarcsrv01/rest/services/CPUC/CPUC_2019_Dynamic/MapServer/13/query?where=1%3D1&outFields=*&outSR=4326&f=json

Thanks,

Divya