How to load JSON data held in a TXT file please? [Example provided]

[jblomqvist]

Hello all,

I have a text file where the data is in JSON format.

The data looks like this:

JSON FILE - DATA GENERATION [{"call_id":"1123335900-1496666340000-3","Region":"North America","user_id":5893,"call_reason":"[50]","call_source":"Internet","call_time":254.8,"customer_type":"General","q_score":98,"unix":997765577}]

The "JSON FILE - DATA GENERATION" is included in the file.

[micheledenardi]

Load
InitialField,
mid(InitialField,index(InitialField,':')+1,index(InitialField,',')-index(InitialField,':')-1)       as call_id,
mid(InitialField,index(InitialField,':',2)+1,index(InitialField,',',2)-index(InitialField,':',2)-1) as Region,
mid(InitialField,index(InitialField,':',3)+1,index(InitialField,',',3)-index(InitialField,':',3)-1) as user_id,
mid(InitialField,index(InitialField,':',4)+1,index(InitialField,',',4)-index(InitialField,':',4)-1) as call_reason,
mid(InitialField,index(InitialField,':',5)+1,index(InitialField,',',5)-index(InitialField,':',5)-1) as call_source,
mid(InitialField,index(InitialField,':',6)+1,index(InitialField,',',6)-index(InitialField,':',6)-1) as call_time,
mid(InitialField,index(InitialField,':',7)+1,index(InitialField,',',7)-index(InitialField,':',7)-1) as customer_type,
mid(InitialField,index(InitialField,':',8)+1,index(InitialField,',',8)-index(InitialField,':',8)-1) as q_score,
mid(InitialField,index(InitialField,':',9)+1,len(InitialField)-index(InitialField,':',9))           as unix
;
LOAD
replace(replace(replace(replace(replace(replace(@1,'JSON FILE - DATA GENERATION',''),'"',''),'[',''),']',''),'{',''),'}','') as InitialField
FROM

(txt, codepage is 1252, explicit labels, delimiter is '\t', msq);

[Michael_Tarallo]

Hello John - unfortunately we don't have a direct JSON file connector out of the box (that I am aware of) that could read JSON files locally from a drive or network share.  Also it seems that in order to get it to work with our web connectors I needed to remove the JSON FILE - DATA GENERATION - text - so this would need to be removed prior to processing.

I was able to get the data using our Web Connectors package Qlik Market and the JsonToTable endpoint in our General Web Connector. AND by putting the file on a server so it can be access by http - http://mtarallo.com/file.json -  so it is not loca.

This is most likely not what you are looking for, but wanted to share my progress.

I added bal‌ icd‌ daz‌ bam‌ - with hopes they may have some additional insight on this capability.

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

[Michael_Tarallo]

I see that micheledenardi   - provided a scripted approach - thanks Michele

[jblomqvist]

Hi Michael,

Is this connector available for free if I am using Qlik Sense Desktop?

I have Sept 2017 version and not sure how to install this web connector as this link is giving page not found http://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/2.2/Release-Info/Versio...

[rwunderlich]

Hi Michele,

Good solution.  Here's a generalized approach that doesn't use any hard coded fieldnames. I've used it when I have an arbitrary or changing number of attributes.

Data:

Generic LOAD

     RecId,

     Key,

     Value

;

LOAD

     RecId,

     PurgeChar(SubField(Pair, ':', 1), '"') as Key,

     PurgeChar(SubField(Pair, ':', 2), '"') as Value

;

LOAD

     RecId,

     SubField(CallRecord, ',') as Pair

;

LOAD

     TextBetween([@1:n],'{','}') as CallRecord,

     RecNo() as RecId

FROM JsonInput.txt

(fix, utf8, no labels);

// Assemble the Data.* tables into a single table

Final:

LOAD FieldValue('RecId', RecNo()) as RecId

AutoGenerate FieldValueCount('RecId');

FOR i = NoOfTables()-1 to 0 step-1

LET vTable = TableName($(i));

IF '$(vTable)' LIKE 'Data.*' THEN

     Join (Final) LOAD * Resident $(vTable);

     DROP Table $(vTable);

ENDIF

Next i

Another example and some explanation here:

Preceding Load is Elegant | Qlikview Cookbook

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[Michael_Tarallo]

Hi John - unfortunately it is not - it is listed under our premium connectors which are licensed.

General Web Connector ‒ Qlik Connectors‌ - in case you want more info,

BUT

I have a workaround you may be interested using the out-of-the-box REST Connector that COMES WITH Qlik Sense Desktop, Cloud Business and Enterprise.

If you put the file in DropBoX or Equivalent and create a URL to the file, you can use that URL with the REST Connector.

However, you must remove the JSON FILE - DATA GENERATION [ text first in order for the parser to pick it up - Rob and Michele provided great solutions showing you how to do that with the script syntax.

https://www.dropbox.com/s/i54d2ak4er2m9fg/file.json?dl=0

Change dl=0 to dl=1

https://www.dropbox.com/s/i54d2ak4er2m9fg/file.json?dl=1

Now this is open to anyone with the URL to the file - if you want more security you could use the file sharing services REST API as I documented in this video / blog:

Update your Qlike Sense Cloud Business workgroup data files with REST and Dropbox - Proof of Concept

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

[petter]

This JSON structure you can read directly from the file actually.

This tiny load script shows how:

[TEXT DATA]:
LOAD
    If( RecNo()=1 , Replace([@1:n],'JSON FILE - DATA GENERATION [','') , If(Right([@1:n],1)=']',Left([@1:n],Len([@1:n])-1),[@1:n]) ) AS L   
FROM 
[lib://Data/JSON-FILE-DATA-GENERATION.TXT]
(fix, utf8, no labels);


[JSON DATA]:
LOAD
*
FROM_FIELD 
([TEXT DATA], L) 
(json, utf8, no labels);


//DROP TABLE [TEXT DATA];

Here is how it looks like in the Data Model Viewer afterwards:

The JSON file has to have each JSON record on a separate line to work - have a look at the attached JSON/TEXT file.

[petter]

IF your JSON records are not separated by line breaks but by commas you could possibly use this alternative load script:

[TEXT DATA]:
LOAD
'{' & L & '}' AS L;
LOAD
Replace( SubField( Replace([@1:n],'JSON FILE - DATA GENERATION [{','') , '},{' ) , '}]' , '') AS L
FROM
[lib://Data/JSON-FILE-DATA-GENERATION.TXT]
(fix, utf8, no labels);

[JSON DATA]:
LOAD
*
FROM_FIELD
([TEXT DATA], L)
(json, utf8, no labels);

//DROP TABLE [TEXT DATA];

Only lines 2 to 8 are different from the first load script.

[rwunderlich]

Wow Petter! json is not listed as a format-spec in the help.

Format specification items ‒ Qlik Sense

Any more undocumented format-spec you'd care to share with us?

-Rob