Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

amien
Valued Contributor

Can Qlik Sense read .JSON files?

I know there is a REST Connector. But i need to enter a URL there. With HTTP or HTTPS protocol. Is it possible to link to a JSON folder or file?

Thanks in advanced

6 Replies
quriouss
Contributor III

Re: Can Qlik Sense read .JSON files?

Yes, absolutely it can (having just done it) but depending on your data structure it might be best not to as it can still involve a lot of editing.

If your JSON data is in a file you should be able to just load it as any other flat table (csv, etc.) but you might have to split it out into sub-tables by hand.

Worst case is probably having to load the JSON into something like MongoDB then load into Qlik from there (that's where I started!!!)

amien
Valued Contributor

Re: Can Qlik Sense read .JSON files?

Thanks for your reply.

What kind of data source do i need to add? When i add a folder, i dont see .Json in the files of files. Or do i need to add a Rest Connector? If so, what do i fill in in the URL box?

jochem_zw
Contributor II

Re: Can Qlik Sense read .JSON files?

‌you have to use The General web connector.

Qlik General Web Connector ‒ Qlik Connectors

quriouss
Contributor III

Re: Can Qlik Sense read .JSON files?

I think that will only connect to web sources (http / https) and the poster is asking about reading from a file.

(I may have also mislead the original poster - I was loading from a QVD, so the JSON transformation had already been done by the REST connector).

Have a look here, which seems like it might help - loop through each row in your file and import the fields one at a time (probably only works if the JSON is un-nested).

Alternatively, just change the filename to *.csv and go to the file-connector and see what happens.

And, another option is to import into Excel then save the table from there and reload into Qlik.  This will work as a one-off, not so sure it's a long-term solution.

Connect to a JSON file - Excel

(My preferred option would be something like MongoDB though - all the work is done automagically then).

stevenkoppens
New Contributor III

Re: Can Qlik Sense read .JSON files?

If you want a easy scripting solution, you could use Python to convert to CSV:

import pandas

df = pandas.read_json("example.json")

df.to_csv('example.csv')

swijnans
New Contributor III

Re: Can Qlik Sense read .JSON files?

You can read json files directly from a file 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 library/connection to your directory
Community Browser