Load long string from TextFile and split into Rows

[prees959]

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

[sunny_talwar · Accepted solution]

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);