Table of Contents

IMPORT JSON FILE

Category: Import / File


Description

Import a JSON object from a text file.


Action settings

Setting Description
Load file* Fully-qualified file name of the dataset (includes relative or absolute path).
JSONPath*Select the path in the JSON document to import from. The list contains the paths found in the file; <Root> imports the whole document. The objects or array items at the selected path become rows of the resulting table. Learn more about the path syntax.
Column namesThe method used to name columns in the incoming dataset.
Options: Property name, Full JSON path
Import only selected JSON propertiesCheck this to import only some properties under the selected JSONPath, then select them in the list. <Selected JSONPath> stands for the value at the path itself.

* Setting can be specified using a parameter.


Advanced options

Setting Description
EncodingASCII, ANSI (with code page), and other types of encoding. If you're not sure what to choose, try UTF-8 as it's the
most common Unicode encoding.
Parsing modeSelect how nested JSON objects and arrays are turned into rows and columns. Options: Dense, Sparse (see "Parsing modes" below).


Parsing modes

Mode Description
DenseDefault option. Properties of nested objects are placed in the same row as their parent's properties, and a new row is created only for each array item. See Example #1 below.
SparseEach nested object gets its own row, and an empty column is added for each array, so the result has more rows and more empty cells.


Examples

Example #1

Import a JSON file with nested objects and arrays.

Source file: 

[
  {
    "order": 1001,
    "customer": { "name": "Anna", "city": "Oslo" },
    "items": [
      { "sku": "A1", "qty": 2 },
      { "sku": "B2", "qty": 1 }
    ]
  },
  {
    "order": 1002,
    "customer": { "name": "Ben", "city": "Rome" },
    "items": [
      { "sku": "C3", "qty": 5 }
    ]
  }
]

Action settings:

JSONPath is <Root>
Column names is "Property name"
All properties are imported
Parsing mode is "Dense"


Result:

order name city sku qty
1001 Anna Oslo A1 2
1001 Anna Oslo B2 1
1002 Ben Rome C3 5

The properties of the nested customer object are placed in the same row as the order number. A new row is created for each item, and the order and customer values are repeated in it.


Remarks

This action can import multiple files. See Importing Multiple Files for more information.


See also