How to save/import data from excel into database with NodeJS?

Question

i have a problem that how can i save data in excel file into the database. I have created the model in order to save data

Here is my model:

import Sequelize from 'sequelize';
import { sequelize } from '../config/database-config.js';

export const City = sequelize.define("city", {
    city_id: {
      type: Sequelize.INTEGER,
      primaryKey: true, 
      autoIncrement:true,
    },
    name: {
      type: Sequelize.STRING,
      unique: true,
      required:  [true, 'City name required'],
    },
    city_rank: {
      type: Sequelize.STRING,
      unique: true,
      required: [true, 'City rank required'],
    },
  }, { sequelize, freezeTableName: true}
)

export default City;

Routes

router.get("/saveExcel", exportCity);

For now, i need help to create controller and i hope anybody can help me. Some ideas would be great.

Basically, for example you have a bunch of excel file, now the problem is i have an issue that my project want me to import excel file to database and i do not know how to do it. — kato
– kato, Commented Sep 7, 2022 at 10:54
So the problem is that you don't know how to read an excel file, not how to save it. Start with googling "how to send a file to the backend nodejs". Then you will need to read the file - "nodejs read xlsx" — Konrad
– Konrad, Commented Sep 7, 2022 at 10:56

Karan Chokshi · Accepted Answer · 2022-09-07 11:39:23Z

1

Install xlsx npm module.

npm install xlsx

2.Import the xlsx module and below code snip

var XLSX = require('xlsx')
var workbook = XLSX.readFile('Master.xlsx');
var sheet_name_list = workbook.SheetNames;
var xlData = XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]);
console.log(xlData);

Now, you get the data in JSON form. you are using sequelize so using bulkCreate() method you can insert all records of excel into the database.

answered Sep 7, 2022 at 11:39

Karan Chokshi

1141 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kato Over a year ago

Could you add some more, i need more information

Karan Chokshi Over a year ago

Kato, can you please share which information you need?

kato Over a year ago

Whole controller to import data from excel

Karan Chokshi Over a year ago

you are getting excel data in xlData variable so you need just fire a query to insert those data if the excel header's name and your database entity attribute name is varied then only you need to manipulate the JSON object and then fire the query.

Collectives™ on Stack Overflow

How to save/import data from excel into database with NodeJS?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related