Skip to main content

DHTMLX Pivot. Loading data from JSON, CSV formats example

Demo of loading external data into DHTMLX Pivot from JSON and CSV sources. Use the fetch API to retrieve remote data files, parse CSV with PapaParse, handle date conversions, and inject data via setConfig.

Live example​

const widget = new pivot.Pivot("#pivot", {
    fields,
    config,
});

function convert(data, headers, meta) {
  const header = headers.join(",") + "\n";
  const processedData = header + data;

  return Papa.parse(processedData, { 
    header: true,
    dynamicTyping: true,
    transform: (v, f) => {
      return meta && meta[f] === "date" ? new Date(v) : v;
    }
  })
}

function loadCSV() {
  const headers = [
    "name",
    "year",
    "continent",
    "form",
    "gdp",
    "oil",
    "balance",
    "when"
  ];
  
  // date fields must be explicitly marked for proper conversion
  const meta = {};
  fields.forEach(f => {
    if (f.type == "date") meta[f.id] = "date";
    if (f.id =="year") f.format = false; //cancel number formatting
  });

  const dataURL =
        "https://snippet.dhtmlx.com/codebase/data/pivot/01/dataset.csv";
    fetch(dataURL)
        .then(response => response.text())
        .then(text => convert(text, headers, meta))
        .then(data => {
            widget.setConfig({
            data: data.data,
            fields,
            config
        });
    });
}

function loadJSON() {
    const dataURL =
        "https://snippet.dhtmlx.com/codebase/data/pivot/01/dataset.json";

    fetch(dataURL)
        .then(res => res.json())
        .then(data => {
       		const dateFields = fields.filter(f => f.type == "date");
            data.forEach(item => {
                dateFields.forEach(f => {
                    const v = item[f.id];
                    if (typeof v == "string") item[f.id] = new Date(v);
                })
            });

            widget.setConfig({
                data: data,
                fields,
                config
            }); 
        });
}
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.4.1/papaparse.min.js"></script>

<!-- auxiliary controls for interacting with the sample -->
<link rel="stylesheet" href="https://snippet.dhtmlx.com/codebase/assets/css/auxiliary_controls.css">
<section class="dhx_sample-controls">
  <button class="dhx_sample-btn dhx_sample-btn--flat" onclick="loadCSV()">
    Load CSV
  </button>
  <button class="dhx_sample-btn dhx_sample-btn--flat" onclick="loadJSON()">
    Load JSON
  </button>
</section>

<!-- component container -->
<div id="pivot" style="width: 100%; height: calc(100% - 60px);"></div>

<!-- dataset -->

<script>
const fields = [
        { id: "name", label: "Name", type: "text" },
        { id: "continent", label: "Continent", type: "text" },
        { id: "form", label: "Form", type: "text" },
        { id: "gdp", label: "GDP", type: "number" },
        { id: "oil", label: "Oil", type: "number" },
        { id: "balance", label: "Balance", type: "number" },
        { id: "year", label: "Year", type: "number" },
        { id: "when", label: "When", type: "date" },
    ];
    
    const config = {
        rows: ["continent", "name"],
        columns: ["year"],
        values: [
        "count(oil)",
        { field: "oil", method: "sum" },
        { field: "gdp", method: "sum" },
        ],
    };
</script>

Real-world pivot table data rarely exists as a hardcoded JavaScript array. It typically comes from backend APIs, databases, or file exports in JSON or CSV format. Loading and parsing these formats correctly - especially handling date fields and numeric typing - requires careful data transformation before the pivot can process it.

DHTMLX Pivot accepts data as an array of objects, so any data source must be converted to this format. For JSON, the conversion is straightforward with fetch and res.json(), but date strings need to be converted to Date objects. For CSV, a parsing library like PapaParse handles header mapping, dynamic typing, and custom transforms for date fields.

This example provides two buttons - "Load CSV" and "Load JSON" - that fetch data from remote URLs. The CSV loader uses PapaParse with dynamicTyping: true for automatic number conversion and a custom transform function for date fields. The JSON loader iterates through date-typed fields and converts string values to Date objects. Both loaders call widget.setConfig() to inject the parsed data.

Solution overview​

Initialize the pivot with an empty dataset, then use loadCSV() or loadJSON() to fetch remote data. For CSV, use PapaParse to parse the text with headers and dynamic typing. For JSON, convert date strings to Date objects manually. Both functions call widget.setConfig({ data }) to load the result.

Key points​

  • CSV parsing with PapaParse: Use PapaParse with header: true and dynamicTyping: true to automatically map CSV columns to object properties and convert numeric strings to numbers
  • Date field conversion: Mark date fields in a metadata object and use PapaParse's transform callback or manual iteration to convert date strings to Date objects before loading
  • Dynamic data injection: Call widget.setConfig({ data, fields, config }) to replace the pivot's dataset at runtime - the widget recalculates all aggregations automatically
  • Format control: Set format: false on numeric fields like "year" that should not receive number formatting (thousands separators), preserving them as plain integers

API reference​

  • Pivot setConfig(): Method to update pivot configuration and data at runtime.
  • Pivot fields: Configuration for defining field types, labels, and formatting.
  • Pivot configuration: Properties for defining rows, columns, and value aggregations.

Additional resources​