Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi Everyone!
Thanks a lot for your answers.
A friend of mine (smarter than me) solved the problem, I guess. Let me set the solution here:
Something I've forgot to tell: it comes from a REST and not a file.
LIB CONNECT TO 'test (REST CONNECTION NAME)';
RestConnectorMasterTable:
SQL SELECT
"__KEY_ResultSet",
(SELECT
"__KEY_Tables",
"__FK_Tables",
(SELECT
"ColumnName",
"DataType",
"__FK_Columns"
FROM "Columns" FK "__FK_Columns"),
(SELECT
"__KEY_Rows",
"__FK_Rows",
(SELECT
"@Value",
"__FK_Cells"
FROM "Cells" FK "__FK_Cells" ArrayValueAlias "@Value")
FROM "Rows" PK "__KEY_Rows" FK "__FK_Rows")
FROM "Tables" PK "__KEY_Tables" FK "__FK_Tables")
FROM JSON (wrap off) "ResultSet" PK "__KEY_ResultSet";
[Columns]:
LOAD [ColumnName] AS [ColumnName],
[DataType] AS [DataType],
[__FK_Columns] AS [__KEY_Tables],
RecNo() as NUM
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Columns]);
[Cells]:
LOAD AutoNumber(RecNo(),[__FK_Cells]) as NUM,
[@Value],
[__FK_Cells] AS [__KEY_Rows]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Cells]);
[Rows]:
LOAD [__KEY_Rows] AS [__KEY_Rows],
[__FK_Rows] AS [__KEY_Tables]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Rows]);
[Tables]:
LOAD [__KEY_Tables] AS [__KEY_Tables],
[__FK_Tables] AS [__KEY_ResultSet]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Tables]);
Columns2Cells:
LOAD 0 as Dummy
AutoGenerate 0;
Join(Columns2Cells)
LOAD NUM, [@Value], [__KEY_Rows] as Rows
RESIDENT Cells;
Join(Columns2Cells)
LOAD ColumnName, NUM
RESIDENT Columns;
DROP Field Dummy;
[TablesX]:
LOAD RecNo() as Key,
ColumnName as ColName,
NUM as ColId,
[@Value] as Val,
Rows
RESIDENT Columns2Cells;
FinalTable:
LOAD 0 as Dummy
AutoGenerate 0;
FOR i = 1 to FieldValueCount('ColName')
LET vField = FieldValue('ColName', $(i));
LET vField1 = '[' & FieldValue('ColName', $(i)) & ']';
Join(FinalTable)
LOAD Rows,
Val as $(vField1)
Resident TablesX
Where ColName = '$(vField)';
NEXT
DROP Field Dummy;
DROP TABLE TablesX;
DROP TABLE Columns2Cells;
DROP TABLE Tables;
DROP TABLE Rows;
DROP TABLE Cells;
DROP TABLE Columns;
DROP TABLE RestConnectorMasterTable;
Feel Free to suggest any improvement you find.
this could be helpful:
Hi Frank,
I've red that thread and it's not the same case, unfortunatelly.
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
May be try this:
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,
Hi Bala,
I'll read it for sure, thanks.
Hi Christian,
nice script. works fine 🙂
do you have the same for python?
regards franky
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
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