How to load local JSON file into Qlik Sense

[rohitk1609]

Hi techies,

I have a use case where I need to load json data into Qlik. my json file is locally stored.

I am sharing the json data below because I can't load json file here.

please copy and paste below data in note pad and save it with .json format.

{ "CountLogs": [ { "Counts": [ { "Name": "Line 1", "RegisterId": 0, "Value": 121 }, { "Name": "Line 2", "RegisterId": 1, "Value": 127 }, { "Name": "Zone 1", "RegisterId": 2, "Value": 156 }, { "Name": "ABC", "RegisterId": 3, "Value": 382 }, { "Name": "LineExclude", "RegisterId": 4, "Value": 125 } ], "LogEntryId": 353493, "Timestamp": "2018-12-03T14:21:00Z" } ], "FriendlyDeviceSerial": "20025" }

Thanks, Rohit 

[Vegar]

Sorry, you can not intepret the JSON file using Qlik Sense scripting. Qlik Sense does not come with shipped with a JSON parser, you will need to use the Qlik REST Connector.

In order for Qlik Sense to read your file you could try to host the file in a web environment where you can access it using the Qlik REST connector. 

Good luck

[rohitk1609]

Can you tell me please how to host a local Json file in web environment ?

Thanks,

Rohit 

[Frank_Hartmann]

you can use python to do this.

If this is ok for you just respond and i try to help you setting up the script and the prerequisites!

[rohitk1609]

Please help me to setup the script which we can use in Qlik Sense and load data from json local file

[Frank_Hartmann · Accepted solution]

1. Enable Legacy Mode for Sense ( in order to be able to execute EXECUTE statements in QS Script)

-->    Open C:\Users\{user}\My Documents\Qlik\Sense\Settings.ini in a text editor (normally the File is empty)

Change StandardReload=1 to StandardReload=0.

2. Install Python and Pandas (https://www.youtube.com/watch?v=Iqjy9UqKKuo)

3. Place the attached Python script into an folder and add following script line as the beginning of you script and adapt the path where you saved the test123.py!

Execute cmd /C "cd C:\users\admin\desktop\ && test123.py"

 this will generate an Output.csv to the folder in which you placed the python script. You will have to place the data.json (if your json file is named different you will have to adapt line 5 in python script) also to this folder.

After that you can load the Output.csv in Qliksense which will give you your desired result!

Your script should look like this:

Execute cmd /C "cd C:\users\admin\desktop\ && test123.py"; //This will generate the Output.csv which is loaded in the next step


Data:
LOAD
    LogEntryId,
    "Timestamp",
    Name,
    RegisterId,
    Value
FROM [lib://Stamm/Output.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Result:

If you have further questions feel free to ask!

hope this helps

[rohitk1609]

Hi Frank,

Sorry for late reply. I got busy with other stuff. I tried your code and it works fine with attached JSON file. But when I tried with my real JSON file it fails. I think your written python code is not the generic one.

Can you please run your code on attached file?

I am failing to attach zip file on ticket so I move JSON code to text file.

[Frank_Hartmann]

You will need to use a different python script.

see attached files.

hope this helps!

[shaybitton]

Hi Rohit,

We used a different solution.

installed IIS on the server and create a virtual directory, browsing enabled and access it through Rest Connector.

Regards

Shay