How to Import and Export CSV Files Using Node.js

What You Will Need

• Node.js
• NPM:
• @mescius/spread-sheets
• FileReader
• node-canvas
• https://www.npmjs.com/package/mock-browser

Controls Referenced

• SpreadJS - JavaScript Spreadsheet Component
• Documentation | Online Demo Explorer

Tutorial Concept

Learn how to use a JavaScript spreadsheet component to import a CSV file, change data, and export it back to CSV in a Node.js application.

In the dynamic world of web development, Node.js has established itself as a pivotal runtime environment for server-side scripting and automation. For web developers leveraging Node.js, managing data in various formats, like CSV, is a common yet critical task. Whether you're importing data, performing analytics, or structuring information in a tabular format, the ability to efficiently import, process, and export CSV files can significantly enhance your workflow.

In this blog, we’ll introduce you to SpreadJS, a powerful JavaScript spreadsheet component, and explore how SpreadJS can streamline CSV importing and exporting in a Node.js application.

How to Read and Export CSV Files from a Node.js Application:

• Get Started with SpreadJS Spreadsheets and Node.js
• Use the SpreadJS NPM Package
• Read the CSV File into Your Node.js Application
• Change Data
• Export to CSV

With the power of SpreadJS, performance is not affected, whether using SpreadJS alone or with Node.js.

To follow along, you can download the sample for this project.

Get Started with SpreadJS Spreadsheets and Node.js

To begin, we’ll need to install Node.js and the Mock-Browser, Canvas, and FileReader.

While you can use most IDEs to create this application, we'll use Visual Studio 2022 in this blog. Once Visual Studio is open, create a new application using Create a new project, and then search for "Blank Node.js Console Application." Provide a name and specify a location to create the project.

This will automatically create the required files and open the “app.js” file, which is the only file we will change.

To install the packages in the project, right-click the "npm" header in the Solution Explorer, click Install New npm Packages, and search and install each package for "Mock-Browser," "Canvas," and "FileReader."

Once those are installed, the dependencies should update in the package.json file:

    {
      "name": "spread-jsnode-csv",
      "version": "0.0.0",
      "description": "SpreadJSNodeCSV",
      "main": "app.js",
      "author": {
        "name": ""
      },
      "devDependencies": {
        "eslint": "^8.21.0"
      },
      "eslintConfig": {},
      "dependencies": {
        "canvas": "^2.11.2",
        "filereader": "^0.10.3",
        "mock-browser": "^0.92.14"
      }
    }

In this sample, we'll use the File System Module of Node.js. We can load that in:

    var fs = require('fs');

To use SpreadJS with Node.js, we can load the Mock-Browser that we installed:

    var mockBrowser = require('mock-browser').mocks.MockBrowser;

Before loading the SpreadJS script, we'll need to initialize the mock-browser. Initialize the variables that we may need to use later in the application, particularly the "window" variable:

    global.window = mockBrowser.createWindow();
    global.document = window.document;
    global.navigator = window.navigator;
    global.HTMLCollection = window.HTMLCollection;
    global.getComputedStyle = window.getComputedStyle;

Initialize the FileReader library:

    var fileReader = require('filereader');
    global.FileReader = fileReader;

Use the SpreadJS NPM Package

The SpreadJS package will need to be added to the project. You can add this to your project by right-clicking the "npm" section of the Solution Explorer and selecting Install New npm Packages. You should be able to search for "MESCIUS" and install the following package:

@mescius/spread-sheets

Once the SpreadJS npm packages have been added to the project, the package.json should be uploaded automatically with the correct dependencies:

    {
      "name": "spread-jsnode-csv",
      "version": "0.0.0",
      "description": "SpreadJSNodeCSV",
      "main": "app.js",
      "author": {
        "name": ""
      },
      "devDependencies": {
        "eslint": "^8.21.0"
      },
      "eslintConfig": {},
      "dependencies": {
        "@mescius/spread-sheets": "^17.1.5",
        "canvas": "^2.11.2",
        "filereader": "^0.10.3",
        "mock-browser": "^0.92.14"
      }
    }

Now, we will require that in the app.js file:

    var MC = require('@mescius/spread-sheets');

When using the npm package, the license key also needs to be set for both:

    MC.Spread.Sheets.LicenseKey = "<YOUR KEY HERE>";

In this application, we'll show the user which version of SpreadJS they are using. To do this, we will require the package.json file and then reference the dependency to display the version number:

    var packageJson = require('./package.json');
    console.log('\n** Using SpreadJS Version "' + packageJson.dependencies["@grapecity/spread-sheets"] + '" **');

Read the CSV File into Your Node.js Application

Now, we will add code to import a CSV file into SpreadJS. In the case of our application, we will load a CSV file local to our application into a string and then import that CSV string into SpreadJS:

    var spread = new MC.Spread.Sheets.Workbook();

    console.log('\nOpening CSV and Manipulating Spreadsheet\n');

    try {
        var csvString = fs.readFileSync('./content/data.csv')
            .toString()
            .split('\n')
            .map(e => e.trim())
            .map(e => e.split(',').map(e => e.trim()));
        console.log("Initial String: " + csvString);
        importCSVFile();
    } catch (e) {
        console.error("** Error manipulating spreadsheet **");
        console.error(e);
    }

    function importCSVFile() {
        spread.getSheet(0).setCsv(0, 0, csvString, "\r", ",");
    }

The contents of this file include only a few numbers for simplicity:

Change Data

Now that the CSV is loaded into SpreadJS, we can make any changes. For brevity, we will just change the value of the first cell:

    function changeData() {
        spread.getSheet(0).setValue(0, 0, 3);
    }

Export to CSV

After importing a CSV and changing some of the data, we will export it back to CSV. We will use the getCsv() function of SpreadJS and then the FileReader writeFile() function:

    function exportCSVFile() {
        csvString = spread.getSheet(0).getCsv(0, 0, 1, 10, "\r", ",");
        fs.writeFile("export.csv", csvString, (e) => {
            if (e) throw e;
            console.log("\n** File exported. **");
        })
    }

Since we are using Visual Studio 2022, we will just run the application using the Start button. The following will show in the console:

If we check the files, we will find a new “export.csv” file in the application folder, which now contains the following content:

Conclusion

Utilizing SpreadJS in conjunction with Node.js demonstrates another example of the versatility and extensibility of SpreadJS! Check out our blog page for more articles, videos, and tutorials. Be sure to check out our demos and documentation as well!