Hostgator Uploading Csv From Excel to Mysql

The Excel file is a spreadsheet file format created past Microsoft for use with Microsoft Excel. Yous tin can utilise the file to create, view, edit, analyse data, charts, budgets and more. In this tutorial, I will evidence you lot how to upload/import Excel file information into MySQL Database using Node.js & read-excel-file.

Related Posts:
– Upload/store images in MySQL using Node.js, Express & Multer
– How to upload/store images in MongoDB using Node.js, Limited & Multer
– Import CSV data into MySQL using Node.js
– Import CSV data into PostgreSQL using Node.js
– Import CSV file into MongoDB collection using Node.js

CSV file instead:
Node.js: Upload CSV file information into Database with Express

Node.js Residue APIs for uploading Excel Files

Assume that we have an .xlsx file that contains Tutorial information in Tutorials sheet as following:

We're gonna create a Node.js Application that provides APIs for:

• uploading Excel File to the Node.js Limited Server & storing data in MySQL Database
• getting list of items from MySQL table
• downloading MySQL table data as Excel file

Afterwards the Excel file is uploaded successfully, tutorials table in MySQL database will await like this:

If we get list of Tutorials, the Node.js Residuum Apis will return:

Node.js Rest API returns Excel File

If you ship request to /api/excel/download, the server will return a response with an Excel file tutorials.xlsx that contains information in MySQL tabular array:

How to do this?
You lot need to set the HTTP header:

          "Content-disposition" : "zipper; filename=[yourFileName]"  "Content-Blazon" : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"                  

Yous can discover step by stride for downloading Excel file in the tutorial:
Node.js Download Excel file example with exceljs

These are APIs to be exported:

Methods	Urls	Deportment
POST	/api/excel/upload	upload an Excel File
Go	/api/excel/tutorials	get List of items in db table
Become	/api/excel/download	download db data as Excel file

Technology

• express 4.17.1
• multer i.iv.2
• mysql2 2.i.0
• read-excel-file four.0.six
• sequelize 5.21.13

Project Structure

This is the project directory that we're gonna build:

– db.config.js exports configuring parameters for MySQL connection & Sequelize.
– models/index.js: uses configuration above to initialize Sequelize, models/tutorial.model.js for Sequelize Tutorial data model.
– middleware/upload.js: initializes Multer Storage engine and defines middleware function to save Excel file in uploads binder.
– excel.controllers.js:

• apply read-excel-file to read Excel file in uploads folder, so save data to MySQL database with Sequelize Model.
• export functions for retrieving all tutorials in database table

– routes/tutorial.routes.js: defines routes for endpoints that is called from HTTP Client, use controllers (along with middleware) to handle requests.
– server.js: initializes routes, runs Express app.

Setup Node.js Excel File Upload project

Open control prompt, modify current directory to the root folder of our project.
Install Express, Multer, Sequelize, Mysql2 with the post-obit control:

          npm install express multer sequelize mysql2 read-excel-file                  

The package.json file volition await like this:

          {   "name": "node-js-upload-download-excel-files",   "version": "1.0.0",   "description": "Node.js Upload/Import Excel files to MySQL Database and download Excel File",   "main": "src/server.js",   "scripts": {     "test": "echo \"Error: no test specified\" && leave 1"   },   "keywords": [     "node js",     "upload",     "import",     "download",     "excel",     "files",     "mysql",     "database"   ],   "writer": "bezkoder",   "license": "ISC",   "dependencies": {     "express": "^four.17.1",     "multer": "^one.4.ii",     "mysql2": "^2.1.0",     "read-excel-file": "^4.0.half dozen",     "sequelize": "^v.21.xiii"   } }                  

Configure MySQL database & Sequelize

In the src folder, we create a separate config binder for configuration with db.config.js file like this:

          module.exports = {   HOST: "localhost",   USER: "root",   Password: "123456",   DB: "testdb",   dialect: "mysql",   pool: {     max: v,     min: 0,     larn: 30000,     idle: 10000   } };                  

Outset five parameters are for MySQL connection.
pool is optional, it volition be used for Sequelize connection pool configuration:

• max: maximum number of connectedness in pool
• min: minimum number of connection in pool
• idle: maximum time, in milliseconds, that a connection can be idle earlier being released
• learn: maximum time, in milliseconds, that pool will endeavour to get connexion earlier throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

Now we initialize Sequelize in src/models folder.

Create src/models/index.js with the following lawmaking:

          const dbConfig = crave("../config/db.config.js"); const Sequelize = require("sequelize"); const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.Countersign, {   host: dbConfig.HOST,   dialect: dbConfig.dialect,   operatorsAliases: simulated,   pool: {     max: dbConfig.pool.max,     min: dbConfig.pool.min,     acquire: dbConfig.pool.acquire,     idle: dbConfig.pool.idle   } }); const db = {}; db.Sequelize = Sequelize; db.sequelize = sequelize; db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize); module.exports = db;                  

We're gonna ascertain Tutorial model in the adjacent pace.

Define the Sequelize Model

In models binder, create tutorial.model.js file like this:

          module.exports = (sequelize, Sequelize) => {   const Tutorial = sequelize.ascertain("tutorial", {     title: {       type: Sequelize.Cord     },     description: {       type: Sequelize.STRING     },     published: {       type: Sequelize.BOOLEAN     }   });   render Tutorial; };                  

This Sequelize Model represents tutorials table in MySQL database. These columns will exist generated automatically: id, title, description, published, createdAt, updatedAt.

After initializing Sequelize, we don't need to write CRUD functions, Sequelize supports all of them:

• create a new Tutorial: create(object)
• create multiple Tutorials: bulkCreate(objects)
• find a Tutorial by id: findByPk(id)
• get all Tutorials: findAll()
• update, remove Tutorials…

We're gonna utilise bulkCreate() and findAll() in our Controller.

Create middleware for uploading & storing Excel file

Inside middleware folder, create upload.js file with the following code:

          const multer = require("multer"); const excelFilter = (req, file, cb) => {   if (     file.mimetype.includes("excel") ||     file.mimetype.includes("spreadsheetml")   ) {     cb(null, truthful);   } else {     cb("Please upload but excel file.", false);   } }; var storage = multer.diskStorage({   destination: (req, file, cb) => {     cb(zippo, __basedir + "/resource/static/assets/uploads/");   },   filename: (req, file, cb) => {     panel.log(file.originalname);     cb(cypher, `${Date.at present()}-bezkoder-${file.originalname}`);   }, }); var uploadFile = multer({ storage: storage, fileFilter: excelFilter }); module.exports = uploadFile;                  

In the lawmaking above, we've washed these steps:
– Starting time, nosotros import multer module.
– Next, nosotros configure multer to apply Disk Storage engine.
– We also define a filter to only let file with excel format.

You lot can see that we have two options here:
– destination determines folder to store the uploaded files.
– filename determines the proper noun of the file inside the destination folder.
– We add the [timestamp]-bezkoder- prefix to the file'due south original name to make sure that the duplicates never occur.

Create Controller for uploading/importing Excel file

controllers/tutorial/excel.controller.js

          const db = require("../../models"); const Tutorial = db.tutorials; const readXlsxFile = require("read-excel-file/node"); const upload = async (req, res) => {   endeavour {     if (req.file == undefined) {       return res.condition(400).transport("Please upload an excel file!");     }     allow path =       __basedir + "/resources/static/assets/uploads/" + req.file.filename;     readXlsxFile(path).so((rows) => {       // skip header       rows.shift();       allow tutorials = [];       rows.forEach((row) => {         allow tutorial = {           id: row[0],           title: row[1],           clarification: row[2],           published: row[3],         };         tutorials.button(tutorial);       });       Tutorial.bulkCreate(tutorials)         .then(() => {           res.condition(200).transport({             message: "Uploaded the file successfully: " + req.file.originalname,           });         })         .catch((fault) => {           res.status(500).transport({             message: "Neglect to import data into database!",             error: error.message,           });         });     });   } take hold of (error) {     console.log(error);     res.status(500).send({       message: "Could not upload the file: " + req.file.originalname,     });   } }; const getTutorials = (req, res) => {   Tutorial.findAll()     .then((data) => {       res.send(information);     })     .grab((err) => {       res.condition(500).send({         message:           err.message || "Some error occurred while retrieving tutorials.",       });     }); }; module.exports = {   upload,   getTutorials, };                  

Now wait at the upload function:
– Starting time we get and bank check file upload from req.file.
– Next we apply read-excel-file to read Excel file in uploads binder, the data which is returned every bit rows will be inverse to tutorials assortment.
– So we use Sequelize model bulkCreate() method to save the tutorials array (id, title, clarification, published) to MySQL database.

The getTutorials() function uses findAll() method to render all Tutorials stored in the database tutorials tabular array.

Define Routes for uploading Excel File

When a client sends request for an endpoint using HTTP request (Post Excel file, Get tutorials), we need to determine how the server will response past setting upwards the routes.

These are our routes:

• /api/excel/upload: Postal service
• /api/excel/tutorials: Become

Create a tutorial.routes.js inside routes folder with content like this:

          const express = require("express"); const router = express.Router(); const excelController = require("../controllers/tutorials/excel.controller"); const upload = require("../middlewares/upload"); allow routes = (app) => {   router.mail("/upload", upload.single("file"), excelController.upload);   router.get("/tutorials", excelController.getTutorials);   app.utilize("/api/excel", router); }; module.exports = routes;                  

You can meet that nosotros use a controller from excel.controller.js.

Create Express app server

Finally, we create an Express server.

server.js

          const express = crave("express"); const app = express(); const db = require("./models"); const initRoutes = crave("./routes/tutorial.routes"); global.__basedir = __dirname + "/.."; app.utilize(express.urlencoded({ extended: true })); initRoutes(app); db.sequelize.sync(); // db.sequelize.sync({ forcefulness: true }).then(() => { //   console.log("Drop and re-sync db."); // }); let port = 8080; app.listen(port, () => {   console.log(`Running at localhost:${port}`); });                  

In the code to a higher place, we initialize Express Router and phone call Sequelize sync() method.

          db.sequelize.sync();                  

In development, you may need to driblet existing tables and re-sync database. Simply use force: true as following code:

                      db.sequelize.sync({ force: truthful }).and then(() => {   console.log("Driblet and re-sync db."); });                  

Run & Check

Commencement we need to create uploads folder with the path resources/static/assets.
On the project root folder, run this command: node src/server.js

Allow's employ Postman to make HTTP POST asking with an Excel file.

The effect in MySQL tutorials table:

Conclusion

Today nosotros've built a Residual Crud API using Node.js Limited to upload and import data from Excel file to Mysql database tabular array.

We also see how to use read-excel-file to read data from Excel file, Sequelize to remember items in database table without demand of boilerplate code.

If you want to add Pagination while getting information from MySQL table, you can observe the instruction at:
Server side Pagination in Node.js with Sequelize & MySQL

For downloading Excel file:
Node.js Download Excel file case with exceljs

Or working with CSV file instead:
Node.js: Upload CSV file data into Database with Express

Happy learning! Meet you again.

Further Reading

• https://www.npmjs.com/packet/express
• https://www.npmjs.com/parcel/multer
• https://sequelize.org/v3/api/model/
• read-excel-file

– Node.js Balance APIs with Express & MySQL
– Node.js Rest APIs with Express & MySQL (including Sequelize)

Fullstack:
– Vue.js + Node.js + Express + MySQL example
– Vue.js + Node.js + Express + MongoDB example
– Athwart 8 + Node.js Express + MySQL case
– Angular 10 + Node.js Express + MySQL example
– Angular xi + Node.js Express + MySQL instance
– Angular 12 + Node.js Express + MySQL example
– React + Node.js + Express + MySQL example

Security: Node.js – JWT Authentication & Dominance instance
Deployment:
– Deploying/Hosting Node.js app on Heroku with MySQL database
– Dockerize Node.js Express and MySQL instance – Docker Compose

Node.js & MySQL Associations:
– One-to-Many Relationship example
– Many-to-Many Relationship example

Source Lawmaking

You can observe the complete source lawmaking for this example on Github.

bellregiand.blogspot.com

Source: https://www.bezkoder.com/node-js-upload-excel-file-database/

Share this post