How to Convert JSON Data into a Two-dimensional Table and Export It to a CSV
Problem description & analysis
Below is data in JSON file json.json:
To convert the multilevel JSON file into a two-dimensional structure while splitting the two subfields under identities field and promoting them to the parent level, and store data as a CSV file as follows:
Solution:
We write the following script p1.dfx in esProc:
| A |
|
| 1 |
=file("json.json").read() |
| 2 |
=json(A1).users.new(displayName,givenName,surname,extension_user_type,identities.signInType:issuerType,identities.issuerAssignedId,extension_timezone,extension_locale,extension_tenant) |
| 3 |
=file("json.csv").export@tc(A2) |
Code description:
A1 Read data of the JSON file as a string.
A2 Parse the string into a multilevel table sequence according to the original structure while splitting and promoting identities.signInType and identities.issuerAssignedId to their parent level with the new function.
A3 Export A2’s result to json.csv.
See How to Call an SPL Script in Java to learn about the way of integrating the SPL code with a Java program.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
