CSV Import and Export Functionality in DataViewsJS

  • 0 Comments

DataViewsJS is a powerful JavaScript data presentation and datagrid platform for creating complex and visually interesting data views. In some cases, importing CSV files (or other data) and exporting CSV files is needed for universally sharing the data.

Here, we'll go over how to create a simple application that imports data from a CSV file and directly from a data source, as well as how to export that data to a CSV file.

Project Setup

Before writing any code, we will first need to import all the libraries we will use in the application. We need to navigate to the project's folder in a terminal and enter the following command:

npm i @grapecity/dataviews.common @grapecity/dataviews.core @grapecity/dataviews.csvexport @grapecity/dataviews.grid alphavantage file-saver jquery

We can then add references to these files and a script file to hold all of the script code we will write in an HTML file:

<!DOCTYPE html>
<html lang="en">
  <head>

    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>DVJS CSV Read and Write</title>

    <link href="./node_modules/@grapecity/dataviews.core/dist/gc.dataviews.core.min.css" rel="stylesheet" type="text/css" />
    <link href="./node_modules/@grapecity/dataviews.grid/dist/gc.dataviews.grid.min.css" rel="stylesheet" type="text/css" />

    <script src="./node_modules/@grapecity/dataviews.common/dist/gc.dataviews.common.min.js" type="text/javascript"></script>
    <script src="./node_modules/@grapecity/dataviews.core/dist/gc.dataviews.core.min.js" type="text/javascript"></script>
    <script src="./node_modules/@grapecity/dataviews.csvexport/dist/gc.dataviews.csvexport.min.js" type="text/javascript"></script>
    <script src="./node_modules/@grapecity/dataviews.grid/dist/gc.dataviews.grid.min.js" type="text/javascript"></script>

    <script src="./node_modules/alphavantage/dist/bundle.js" type="text/javascript"></script>
    <script src="./node_modules/file-saver/dist/FileSaver.min.js" type="text/javascript"></script>
    <script src="./node_modules/jquery/dist/jquery.min.js" type="text/javascript"></script>
  </head>
</html>

We can also get the page content setup; in this case, we want to have three buttons:

  • Import a CSV file
  • Import data from a data source
  • Export DataViewsJS to a CSV file

We should also add the DIV element for the DataViewsJS instance:

<body class="theme-default">
  <button id="ImportCSV" type="button" onclick="CSVImport()">Import a CSV file</button>
  <button id="ImportDataSource" type="button" onclick="DataSourceImport()">Import from Data Source</button>
  <button id="ExportCSV" type="button" onclick="CSVExport()" style="display:none">Export to a CSV file</button>
  <div id="grid" class="grid" style="width: 1300px; height: 800px;"></div>
</body>

Setup Script Code

In this sample, we are going to be using a free stock API known as Alpha Vantage. Specifically, we are going to export historical market data from Alpha Vantage and manipulate the data in DataViewsJS. You may also refer to this article that covers some of the other stock API options and financial modeling best practices.

In order to access the data from that API, you will need an API key.

We are also going to be using a GitHub repo that is essentially a JavaScript wrapper for the Alpha Vantage API.

We can initialize that interface with our key:

/**
 * 
 * @param {String} key
 * Alpha Vantage API key
 */
const alpha = alphavantage({ key: '<YOUR KEY HERE>'});

Import CSV Files

In the case of this application, we are importing a CSV file into DataViewsJS that was downloaded from Alpha Vantage.

A link to that file can be found here.

You just need to replace <Your Key Here> with the Alpha Vantage API key. For simplicity, I have already included the CSV file in the demo zip file.

For this functionality, we want to hide the buttons for import/export, import the CSV file in our application, convert the CSV to an array of JavaScript objects, and set that array as a data source for DataViewsJS.

We can accomplish this with the jQuery AJAX call:

// Import CSV file and convert to an array of JavaScript objects
function CSVImport() {
    // Hide the buttons
    document.getElementById("ImportCSV").style.display = "none";
    document.getElementById("ImportDataSource").style.display = "none";
    var data = [];
    $.ajax({
        type: "GET",  
        url: "currency_monthly_BTC_USD.csv",
        dataType: "text",       
        success: function(response)  
        {
            data = CSVToJSObject(response);
            initializeDataView(data);
        }   
    });
}

The CSVToJSObject function parses through the CSV content and creates an array of JavaScript Objects:

// Convert the CSV content to an array of JavaScript Objects
function CSVToJSObject(csv) {
    var delimiter = ',';
    var lines = csv.split('\r\n');
    var result = [];
    var headers = lines[0].split(delimiter);
    for (var i = 1; i < lines.length; i++) {
        var obj = {};
        var line = lines[i].split(delimiter);
        for (var j = 0; j < headers.length; j++) {
            obj[headers[j]] = line[j];
        }
        result.push(obj);
    }
    return result;
}

When we have the array, we initialize the DataViewsJS instance with that:

// Initialize DVJS with the data source
function initializeDataView(data) {
    dataView = new GC.DataViews.DataView('#grid', data, new GC.DataViews.GridLayout());
}

CSV Import and Export Functionality in DataViewsJS

Import Data Source and Export to CSV

The other way we can import the data is directly from a data source, and this is where the custom Alpha Vantage wrapper comes into play: we can call the API to get the data and then write a custom function to parse through that data and make it more like the CSV file we have:

function DataSourceImport() {
    // Hide the buttons
    document.getElementById("ImportCSV").style.display = "none";
    document.getElementById("ImportDataSource").style.display = "none";
    // Use AlphaVantage GitHub API to get data directly from the AlphaVantage source
    alpha.crypto.monthly('btc', 'usd').then(data => {
        var importedData = parseData(data);
        initializeDataView(importedData);
    });
    document.getElementById("ExportCSV").style.display = "block";
}

Comparing the data to the CSV from before shows that the JavaScript object array is slightly different, so we need to have a function that parses through that data to make it fit our requirements:

// Parse through the data from the site and create an array of JavaScript Objects in the correct format
function parseData(data) {
    var dataToParse = data["Time Series (Digital Currency Monthly)"];
    var parsedData = [];
    var dataToParseKeys = Object.keys(dataToParse);
    var dataToParseValues = Object.values(dataToParse);
    for (var l = 0; l < dataToParseKeys.length; l++) {
        var newEntry = {};
        var timestampDate = new Date(dataToParseKeys[l]);
        timestampString = (timestampDate.getMonth()+1) + '/' + timestampDate.getDate() + '/' + timestampDate.getFullYear();
        newEntry["timestamp"] = timestampString;
        for (var k = 0; k < Object.keys(dataToParseValues[l]).length; k++) {
            var currentData = dataToParseValues[l];
            newEntry["open_a_USD"] = currentData["1a. open (USD)"];
            newEntry["open_b_USD"] = currentData["1b. open (USD)"];
            newEntry["high_a_USD"] = currentData["2a. high (USD)"];
            newEntry["high_b_USD"] = currentData["2b. high (USD)"];
            newEntry["low_a_USD"] = currentData["3a. low (USD)"];
            newEntry["low_b_USD"] = currentData["3b. low (USD)"];
            newEntry["close_a_USD"] = currentData["4a. close (USD)"];
            newEntry["close_b_USD"] = currentData["4b. close (USD)"];
            newEntry["volume"] = currentData["5. volume"];
            newEntry["market_cap_USD"] = currentData["6. market cap (USD)"];
        }
        parsedData.push(newEntry);
    }
    return parsedData;
}

CSV Import and Export Functionality in DataViewsJS

Once that data has been loaded into DataViewsJS, we can write a function to export the DataViewsJS instance to a CSV file:

function CSVExport() {
    var selection = "all";
    var csv = dataView.export({ format: 'csv', selection });
    window.saveAs(new Blob([csv], { type: 'text/plain' }), 'data.csv');
}

That is all that is needed to import and export CSV files with DataViewsJS!

Check out the latest release of DataViewsJS here and our DataViewsJS Documentation.

Kevin Ashley - Spread Product Manager

Kevin Ashley

Product Manager