Load long string from TextFile and split into Rows

prees959 — Sat, 16 Nov 2024 19:49:15 GMT

HI,

I have a json string within a text file that I would like to split into Rows.

when openined in notepad the string looks like :

{"APIResults":{"10008": {"ID": "9108471", "Value": "0", "Input": "N/A"}, {"10009": {"ID": "41066228", "Value": "0", "Input": "N/A"}, {"100010": {"ID": "174106", "Value": "0", "Input": "N/A"}, {"100011": {"ID": "601057808", "Value": "0", "Input": "N/A"}, {"100012": {"ID": "8030454", "Value": "0", "Input": "N/A"}}, "Summary": {"TotalCount": 8656, "ProcessingTimeSeconds": 156.242008}, "APIMessage": {"StatusCode": 200, "Message": "SUCCESS"}}

the row delimeter is a '},' so the resulting table should look like :

{"10008": {"ID": "9108471", "Value": "0", "Input": "N/A"},
{"10009": {"ID": "41066228", "Value": "0", "Input": "N/A"},
{"100010": {"ID": "174106", "Value": "0", "Input": "N/A"},
{"100011": {"ID": "601057808", "Value": "0", "Input": "N/A"},
{"100012": {"ID": "8030454", "Value": "0", "Input": "N/A"}

If possible I would like to omit the first part : {"APIResults":

and also the trailing details at the end :
"Summary": {"TotalCount": 8656, "ProcessingTimeSeconds": 156.242008}, "APIMessage": {"StatusCode": 200, "Message": "SUCCESS"}}

Is this possible using a load script?

Many thanks, Phil

Re: Load long string from TextFile and split into Rows

sunny_talwar — Thu, 24 Oct 2019 12:13:36 GMT

May be this

Table: LOAD Replace(FieldName, '{"APIResults":', '') as FieldName Where Not WildMatch(FieldName, '*Summary*', '*APIMessage*'); LOAD SubField(@1, '},') & '}' as FieldName FROM [Load long string from TextFile and split into Rows.txt] (txt, codepage is 1252, explicit labels, delimiter is '\t', msq);

topic Load long string from TextFile and split into Rows in QlikView

Load long string from TextFile and split into Rows

Re: Load long string from TextFile and split into Rows