Json to Qlik - How to load it when the Json is like that?

[rafaelvianna]

Hi guys!

After reading the session about loading the Json to Qlik I couldn't find a way to generate the data the way I wish with the json that is given to me. I hope someone have an idea (or similiar problem solution) to help me.

I have a result from a Json that's like this:

{

  "ResultSet": {"Tables": [  {

      "Columns":      [

                  {

            "ColumnName": "UserId",

            "DataType": "System.Int32"

        },

                  {

            "ColumnName": "Start date",

            "DataType": "System.DateTime"

        },

                  {

            "ColumnName": "End date",

            "DataType": "System.DateTime"

        },

                  {

            "ColumnName": "Duration",

            "DataType": "System.TimeSpan"

        }

      ],

      "Rows":      [

        {"Cells":          [

            9000,

            "2018-08-30T09:56:59.953",

            "2018-08-30T09:57:00.237",

            284

        ]},

        {"Cells":          [

            9000,

            "2018-08-30T09:57:00.237",

            "2018-08-30T09:58:00.937",

            60700

        ]},

        {"Cells":          [

            9000,

            "2018-08-30T09:58:00.937",

            "2018-08-30T10:26:01.67",

            1680733

        ]}

      ],

      "TableName": "Result table",

      "Hide": null,

      "Delete": false

  }]},

  "ShowReport": true,

  "invalidCharInXml": "\u001f"

}

As you can see, first I have my 'column names' and after I have the values. Usually, it doesn't come like this, but this specific code does, and I can't change it. I have to deal with the results.

And I'm trying to load it on QV like a regular excel file columns. In this sample, it would be like:

UserID     Start Date                             End Date                              Duration

9000        2018-08-30T09:56:59.953   2018-08-30T09:58:00.937     284

9000        2018-08-30T09:57:00.237   2018-08-30T09:58:00.937     60700

...

But I just can't do that. Anyone have any idea of how to deal with that?

[Frank_Hartmann]

this could be helpful:

Load Data by Script with JSON

[rafaelvianna]

Hi Frank,

I've red that thread and it's not the same case, unfortunatelly.

[cwolf]

You can use Powershell or Python to convert json to csv.

For example with a powershell script:

param
(
     [Parameter(Mandatory=$true)][string]$JsonFile
)

$jf=Get-ChildItem -Path $JsonFile

if($jf.Exists){
    $obj=(Get-Content $jf.FullName -Encoding UTF8) -join "`n" | ConvertFrom-Json
    $cols=$obj.ResultSet.Tables[0].Columns
    $rows=$obj.ResultSet.Tables[0].Rows

    $csv=@()
    $s=""
    for($i=0;$i -lt $cols.length;$i++){
        $s+=',"'+$cols[$i].ColumnName +'"'
    }
    $csv+=$s.TrimStart(",")

    for($i=0;$i -lt $rows.length;$i++){
        $s=""
        for($j=0;$j -lt $cols.length;$j++){
            $s+=',"'+ $rows[$i].Cells[$j] + '"'
        }
        $csv+=$s.TrimStart(",")
    }

     Set-Content -Path ($jf.DirectoryName+"\"+$jf.BaseName+".csv") -Value $csv -Encoding UTF8 -Force
}

Call:

powershell c:\temp\json2csv.ps1 -JsonFile c:\temp\ResultSet.json

- Christian

[balabhaskarqlik]

May be try this:

Qlikview and Json api

[rafaelvianna]

Hi Christian,

That's a nice option, even though my skills on python are lacking at this momment.

Do you think that use some 'middle' is the only option?

BR,

[rafaelvianna]

Hi Bala,

I'll read it for sure, thanks.

[Frank_Hartmann]

Hi Christian,

nice script. works fine 🙂

do you have the same for python?

regards franky

[Frank_Hartmann]

Sorry, i was not aware that it is a nested json file. my solution only works for non nested json files.

But have a look into Christians solution which works very well. But be aware that you will have to run powershell 3.0 on your machine!

hope this helps

[cwolf]

Hi Frank,

as python:

import os,json,csv
from argparse import ArgumentParser

parser=ArgumentParser()
parser.add_argument("-jf","--jsonfile",dest="jf")
args=parser.parse_args()

if  not args.jf is None and os.path.isfile(args.jf):
    print("Ok!")
    with open(args.jf,mode="r",encoding="utf-8") as f: obj=json.load(f)
    cols=obj['ResultSet']['Tables'][0]['Columns']
    rows=obj['ResultSet']['Tables'][0]['Rows']
    header=[]
    for col in cols: header.append(col['ColumnName'])
    with open(os.path.splitext(args.jf)[0]+".csv",mode="w",encoding="utf-8") as f:
        writer =csv.writer(f,delimiter=",",quotechar='"',quoting=csv.QUOTE_ALL,lineterminator='\n')
        writer.writerow(header)
        for row in rows: writer.writerow(row['Cells'])

Call:

python.exe c:\temp\json2csv.py -jf c:\Temp\ResultSet.json

- Christian