Click Below to subscribe

How to Import Export Excel & CSV In Node.js 2022

In this article, we gonna learn how to import and export excel and csv in node.js. 

1. Let's create a new express project using express generator.

npm i -g express-generator
express node-excel-csv --view=hbs

2. Create a folder config and inside this create a file database.js.


module.exports = { 
    host: process.env.DB_HOST,
    database: process.env.DB_DATABASE,
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD

3. Install dotenv npm package.

npm i dotenv

After Installation import dotenv in app.js


4. Create a .env file in the root and add these environment variables.

DB_HOST=localhost            // your database host
DB_DATABASE=node-excel-csv  // your database name
DB_USERNAME=root           // your database username 
DB_PASSWORD=              // your database password

5. Install sequelize and mysql2 npm packages.

npm i sequelize mysql2

6. Create a models folder and inside this create a connection.js and movie.model.js


const { Sequelize } = require('sequelize');
const config = require('../config/database'); 

const sequelize = new Sequelize(config.database, config.username, config.password, {
    dialect: 'mysql',
    operatorsAliases: 'false',
    logging: false

module.exports = sequelize


const { DataTypes } = require('sequelize');
const sequelize = require('./connection');

const Movie = sequelize.define('Movie', {
    movie: {
        type: DataTypes.STRING 
    category: {
        type: DataTypes.STRING 
    director: {
        type: DataTypes.STRING 
    rating: {
        type: DataTypes.FLOAT(8, 1) 
}, {
    underscored: true
module.exports = Movie;

Note:- Don't forget to create a movies table.

Above Schema Based on the import(excel or csv) file.

7. Install multer and uniqid npm packages.

npm i multer, uniqid

8. create a folder helpers and inside this folder create a file uploader.js

const multer = require('multer');
const uniqid = require('uniqid'); 
const path = require('path');

const storage = multer.diskStorage({
    destination: function(req, file, cb) {
        cb(null, 'storage/uploads/')
    filename: function(req, file, cb) {
        cb(null, + uniqid() + path.extname(file.originalname))
const upload = multer({ storage: storage });

exports.upload = (field) => upload.single(field); 

9. Create a storage folder and inside this create uploads and outputs folder.

10. Install xlsx npm package.

npm i xlsx

xlsx package provides a bunch of functions for reading and writing CSV/Excel files.

Parsing functions:-, read_opts) attempts to parse data

      XLSX.readFile(filename, read_opts) attempts to read filename and parse.

Note:- you can pass raw option to false  if you want formatted data.(example - formatted date), { raw: false })
XLSX.readFile(filename, { raw: false })

Writing functions:-

      XLSX.write(wb, write_opts) attempts to write the workbook

      XLSX.writeFile(wb, filename, write_opts) attempts to write workbook

Utility Functions:-


            book_new creates an empty workbook

            book_append_sheet adds a worksheet to a workbook


           aoa_to_sheet converts an array of arrays of JS data to a worksheet

           json_to_sheet converts an array of JS objects to a worksheet

          sheet_add_aoa adds an array of arrays of JS data to an existing worksheet.

          sheet_add_json adds an array of JS objects to an existing worksheet.


          sheet_to_json converts a worksheet object to an array of JSON objects.

          sheet_to_csv generates delimiter-separated-values output.


11. Create a folder controllers and inside this folder create movie.controller.js


const XLSX = require("xlsx"); 
const Movie = require("../models/movie.model")
const outputPath = 'storage/outputs' 

exports.index = async (req, res) => { 
    const movies = await Movie.findAll();
    return res.render('index', { movies });

exports.import = async (req, res) => { 
    const wb = XLSX.readFile(req.file.path); 
    const sheets = wb.SheetNames;
    if(sheets.length > 0) {
        const data = XLSX.utils.sheet_to_json(wb.Sheets[sheets[0]]);
        const movies = => ({
            movie: row['Movie'],
            category: row['Category'],
            director: row['Director'],
            rating: row['Rating']
        await Movie.bulkCreate(movies); 
    return res.redirect('/');

exports.export = async (req, res) => {
    const movies = await Movie.findAll({
        attributes: [
        raw: true

    const headings = [
        ['Id', 'Movie', 'Category', 'Director', 'Rating']

    const wb = XLSX.utils.book_new();
    const ws = XLSX.utils.json_to_sheet(movies, { 
        origin: 'A2', 
        skipHeader: true 
    XLSX.utils.sheet_add_aoa(ws, headings); 
    XLSX.utils.book_append_sheet(wb, ws, 'Movies');

    const buffer = XLSX.write(wb, { bookType: 'csv', type: 'buffer' }); 

    return res.send(buffer);

Note:- In this article, I am using import file headers if your file doesn't have a headers row then you can use indexes.

You need to pass header option with sheet_to_json

const data = XLSX.utils.sheet_to_json(wb.Sheets[sheets[0]], { header: 1 });
const movies = => ({
    movie: row[0],
    category: row[1],
    director: row[2],
    rating: row[3]

In the above code you can change the extension based on the file you want to import or export. (.xlsx, .xls, .csv)

Note:- If want to store your exported file on the server you can use writeFile function.

const wb = XLSX.utils.book_new();
const ws = XLSX.utils.json_to_sheet(movies, { 
    origin: 'A2', 
    skipHeader: true 
XLSX.utils.sheet_add_aoa(ws, headings); 
XLSX.utils.book_append_sheet(wb, ws, 'Movies');

const filepath = `${outputPath}/movies.csv`;
XLSX.writeFile(wb, filepath); 


12. Create routes.


const express = require('express');
const router = express.Router();
const movieController = require('../controllers/movie.controller');
const { upload } = require('../helpers/uploader');

router.get('/',                        movieController.index);'/import', upload('file'), movieController.import);
router.get('/export',                  movieController.export);

module.exports = router;

13. Open views/index.hbs and add the following code.

    <div class="row mb-2">
        <div class="col-sm-8 offset-2">
            <div class="row">
                <div class="col-md-6">
                    <form method="POST" action="/import" enctype="multipart/form-data"> 
                        <div class="input-group">
                            <div class="custom-file">
                                <input type="file" name="file" class="custom-file-input" id="inputGroupFile" required accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/">
                                <label class="custom-file-label" for="inputGroupFile">Choose file</label>
                            <div class="input-group-append">
                                <button type="submit" class="btn btn-primary float-right mr-2">Import <i class="fa fa-upload"></i></button>
                <div class="col-md-6">
                    <a href="/export" class="btn btn-primary float-right {{#unless movies.length}} disabled {{/unless }}" role="button" aria-disabled="true">
                        Export <i class="fa fa-download"></i>
    <div class="row">
        <div class="col-sm-8 offset-2">
            <table class="table">
                        <th scope="col">Id</th>
                        <th scope="col">Movie</th>
                        <th scope="col">Category</th>
                        <th scope="col">Director</th>
                        <th scope="col">Rating</th>
                    {{#each movies}} 
                            <th scope="row">{{ }}</th>
                            <td>{{ }}</td>
                            <td>{{ this.category }}</td>
                            <td>{{ this.director }}</td>
                            <td><span class="badge bg-warning text-dark">{{ this.rating }}</span></td>
                            <td colspan="5" class="text-center">No Movies Found.</td>

Note:- please checkout github repo -

14. Finally start the project.

npm start

Open http://localhost:3000 and try import and export.

Extras:- if you want to read a file without uploading then you can use multer.memoryStorage()

const multer = require('multer'); 
const upload = multer({ storage: multer.memoryStorage() });
exports.upload = (field) => upload.single(field);

with memoryStorage() you can access the file buffer.

const wb =; 
const sheets = wb.SheetNames;


Sample Excel CSV Files:-

Checkout my full node-excel-csv example.

If you facing any issues. don't hesitate to comment below. I will be happy to help you.


Leave Your Comment