Loading Data into Pivot

You can easily load data into DHTMLX Pivot. For now, it supports two data formats: JSON and CSV. You can use Pivot with any RESTful server side: Java, .Net, PHP, Node.js, etc.

Pivot allows loading data in several ways:

  • load data from external resources
  • load inline dataset

External Data Loading

It is possible to load data into Pivot from an external file or a server-side script after the component has been initialized. If this is the way you need, the load method should be your choice. The method takes the path to a necessary file as a parameter.

Loading JSON data

By default, Pivot loads data in the JSON format:

var myPivot = new dhx.Pivot("container", {
    fields: {
        // initial pivot structure
    },
    fieldList: [
        // the full list of fields
    ]
  }
);
 
pivot.load("dataset.json");

Loading CSV data

You can also load data in the CSV format. For this, you need to call the load method and pass the name of the format ("csv") as the second parameter:

pivot.load("dataset.csv", "csv");

Using data drivers

Pivot provides special data drivers that allow you to define special options for parsing data into Pivot after loading. In order to use a data driver, you need to pass the constructor of a driver as a second parameter of the load() method. Currently, you can make use of a CSV data driver.

CsvDriver

You can use this driver to configure the process of parsing data from a CSV file. The constructor function of the driver takes an object with configuration options as a parameter.

For example, you can use string ids while loading data in the CSV format into Pivot fields:

pivot.load("dataset.csv", new dhx.dataDrivers.csv({
    names:["name","year","continent","form","gdp","oil","balance","when"]
}));

Possible attributes of the driver configuration object

  • names - (array) an array of values used as ids of Pivot fields during parsing from a CSV file;
  • nameByHeader - (boolean) false by default. Defines whether the values in the first line of data in a CSV file will be applied as the ids of Pivot fields;
  • skipHeader - (number) 0 by default. Skips the specified number of header lines in CSV data during parsing (e.g.skipHeader:2). Note that skipHeader with a value other than 0 (e.g.skipHeader:2) can't be used together with the nameByHeader:true;
  • rowDelimiter - (string) a newline ("\n") by default. A separator between rows, can be a tab - "\t", or any other value;
  • columnDelimiter - (string) a comma (",") by default. A separator between columns, can be a semicolon - ";", or any other value.
pivot.load("dataset.csv", new dhx.dataDrivers.csv({
    nameByHeader: true,
    rowDelimiter: "\t",
    columnDelimiter: ";"
}));

Related sample:  Load data in json and csv formats - DHTMLX Pivot

Loading Inline Data

You can also load inline data into Pivot both during and after initialization.

An example of a suitable data set is given below:

var dataset = [
    { "name": "India",      "year": 2005,   "continent": "Asia",    "gdp": 808.744 },
    { "name": "Chad",       "year": 2011,   "continent": "Africa",  "gdp": 9.345 }, 
    { "name": "Belarus",    "year": 2011,   "continent": "Europe",  "gdp": 55.136 },
    // more fields
]

There are two ways of loading inline data into Pivot available:

  • with the help of the data property

Use this way to load data during the initialization of Pivot.

var myPivot = new dhx.Pivot("container", {
    data: dataset,                     fields: {
        // initial pivot structure
    },
    fieldList: [      
        // the full list of fields
    ]
});

Related sample:  Basic initialization - DHTMLX Pivot

This way lets you populate Pivot with data after the component's initialization.

myPivot.setData(dataset);

The method takes an array with data objects as a parameter.

Related sample:  Set data - DHTMLX Pivot

Optimizing Dataset Size

Aliases instead of repeated values

With dhtmlxPivot, you can reduce the number of duplicate data values by using aliases. It allows making a data set more compact, which enhances the speed of data loading.

Aliases allow you to use ids instead of real data values repeated through a data set. They present an object that contains a set of key:value pairs, where the key is the id that repeatedly occurs in a data set and the value is the real data value that should be displayed in the pivot.

For example, there are aliases for the form of government in a dataset containing countries:

aliases: {
    1: "Constitutional monarchy",
    2: "Republic"
}

In this example, 1 and 2 are the ids of data values and "Constitutional monarchy" and "Republic" are real content of these values.

Implementation of aliases

Let's now study the ways aliases can be applied in a Pivot data set. They differ depending on the method of loading data into Pivot.

  • inline data loading

Before loading inline data, you need to define ids instead of real values for the related fields in a data set. In the example below, the "form" field contains ids instead of data values:

"aliased_dataset.js"

var dataset = [{
    "name": "Argentina",
    "year": 2005,
    "continent": "South America",
    "form": 2,
    "gdp": 181.357,
    "oil": 1.545,
    "balance": 4.699,
    "when": "4/2/2015"
}, {
    "name": "Australia",
    "year": 2005,
    "continent": "Australia",
    "form": 1,
    "gdp": 732.091,
    "oil": 8.014,
    "balance": -41.878,
    "when": "6/3/2016"
},
  // more data 
]

In this case aliases can be defined right inside a necessary field object in the Pivot configuration object:

var myPivot = new dhx.Pivot(document.body, {
    data: dataset,
    fields: {
        // fields
    },
    fieldList: [
        { 
          id: "name", label: "Name" },
        { id: "year", label: "Year" },
        { id: "continent", label: "Continent" },
        {           
          id: "form", label: "Form",                                
          aliases: { 1: "Constitutional monarchy",2: "Republic"}                }
        // other fields
    ]
});

Related sample:  Subfields - DHTMLX Pivot

  • loading from an external file

While loading data from an external file, you need to prepare a data set in a similar way, by setting ids in place of data values for necessary fields. Besides, you need to specify the "aliases" object in the same file with data.

For example, in case of JSON and the "form" field, the content of your file can be as follows:

"aliased_dataset.json"

{
    // setting the aliases
    "aliases": {
        "form":{
            "1":"Republic",
            "2":"Constitutional monarchy"
        }
    },
    // setting the data set
    "data": [
        {
            "name": "Argentina",
            "year": 2005,
            "continent": "South America",
            "form": "1",
            "gdp": 181.357,
            "oil": 1.545,
            "balance": 4.699,
            "when": "4/2/2015"
        },
        {
            "name": "Australia",
            "year": 2005,
            "continent": "Australia",
            "form": "2",
            "gdp": 732.091,
            "oil": 8.014,
            "balance": -41.878,
            "when": "6/3/2016"
        },
        // more data        
    ]
}

Then you need just to load the file as usual:

pivot.load("dataset_aliased.json");

Related sample:  Dataset with aliases - DHTMLX Pivot

Back to top