Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a file with the following data with JSON format:
[{"id":"500","program":"prog003","number":"333525","datetime":"2017-01-03 15:45:15","zone":"north","amount":"100"}, {"id":"501","program":"prog003","number":"333526","datetime":"2017-01-03 15:48:30","zone":"north","amount":"300"}, {"id":"502","program":"prog003","number":"333527","datetime":"2017-01-03 17:25:23","zone":"south","amount":"150"}]
Do you know how can I load the data in a table by script? (Preferably with no connectors)
Thank you!!!
try like this:
tmp:
Load '{"arrayname":'&@1&'}'as @1
From
[C:\Users\frank\Desktop\responsePrueba-1.json]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
tmp1:
LOAD
json2csv(@1,'arrayname') as data_csv,
purgechar(subfield(json2csv(@1,'arrayname'),',',$(i)),'"') as FieldNames,
RowNo() as Row
Resident tmp;
NoConcatenate
final:
LOAD
RowNo() as Row,
* From_Field (tmp1, data_csv); DROP tables tmp, tmp1
Muchas Gracias Frank, pero sigo sin poder visualizar uno de los campos (que trae subcampos), me refiero a la columna 17 que se apertura en 3 registros mas que actualmente no se visualizan.
El campo es "ejes" y se apertura en tres subcampos "logro", "eje" y "eje_id", los cuales no se visualizan.
Estare atento. De igual forma, muchas gracias por tu aporte.
saludos
Ahh, ok. I was not aware that your json is nested.
try the attached solution with python script for nested json arrays
Here i already posted the script with some hints to regard (especially the installation of Python):
hope this helps