Data From JSON


Import from JSON function is used to get data from a JSON file format.

SheetKraft contains the Data From JSON option which can be accessed by the Import From Button present in the SheetKraft Toolbar (See figure)

json1

There is no UI Support for JSON as of now, and you will be directed to functions UI of Microsoft Excel (See figure)

json2

File Location: Data from single/multiple file(s) with similar structure can be imported by selecting the excel range that specifies the file paths.

Root: Can be left Blank.

Pattern: Arrangement of all the field/object names which will be required from the JSON file and it acts as the headers when the data is formatted in a tabular form

Exclude Column Headers: TRUE Excludes the Column headers, FALSE includes column header in the imported data.

Sample JSON Format:

json3

Before selecting the option for DataFromJson, the data in the JSON file should be made familiar with and only required objects/fields should be selected for importing. Those objects will acts as Pattern input for JSON. If no Pattern is provided, it will reflect the JSON data as it is stored (in text)

json4

Before selecting the option for DataFromJson, the data in the JSON file should be made familiar with and the required/relevant object names should be highlighted. The object names collectively are called pattern and if no pattern is provided, it will reflect the entire data as text.

json5

Part 2

There might be cases where objects are in array inside another object called a parent object. (See figure)

json6

Sheetkraft will normally reflect the object data as text

json7

To tabulate the data in the proper form, you can separate the parent object and the child object with a dot (.)

json8

json9

Or the other way around to it is to extract the text from the column with another DataFromJson formula with pattern representing the child object contained inside parent string. (See figure)

json10

And when there is an optional data, you assign the first option as [0] and then continue with [1], [2] and likewise, for each of the other option. (See figure)

json11

And after the pattern is selected with required columns,

json12