Csv Data Upload on Frontend or Backend

April 16, 2022 Post a Comment

A CSV (comma-separated values) file is a plain text file that contains data which format is described in RFC4180. In this tutorial, I will show you how to upload/import CSV file data into MySQL Database using Node.js Express & fast-csv.

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

Excel file instead: Node.js: Upload/Import Excel file data into Database

Node.js Rest APIs for uploading CSV Files

Assume that nosotros have an .csv file that contains Tutorial data equally post-obit:

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

uploading CSV File to the Node.js Express Server & storing data in MySQL Database
getting list of items from MySQL table
downloading MySQL table information as CSV file

After the CSV file is uploaded successfully, tutorials table in MySQL database will look like this:

If nosotros go list of Tutorials, the Node.js Rest Apis will return:

Node.js Rest API returns CSV File

If yous send asking to /api/csv/download, the server will render a response with a CSV file tutorials.csv that contains data in MySQL table:

How to exercise this?
You need to set the HTTP header:

          "Content-disposition" : "attachment; filename=[yourFileName]"  "Content-Blazon" : "text/csv"

You lot can find step past step for downloading CSV file in the tutorial:
Node.js Download CSV file example

These are APIs to be exported:

Methods	Urls	Actions
Mail service	/api/csv/upload	upload a CSV File
Get	/api/csv/tutorials	get List of items in db table
Become	/api/csv/download	download db information as CSV file

Technology

limited 4.17.1
multer 1.4.2
mysql2 ii.2.5
fast-csv iv.three.2
sequelize half-dozen.3.5

Projection Structure

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

– db.config.js exports configuring parameters for MySQL connexion & 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 CSV file in uploads folder.
– csv.controllers.js:

employ fast-csv to read CSV file in uploads folder, then salve data to MySQL database with Sequelize Model.
export functions for retrieving all tutorials in database tabular array

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

Setup Node.js Upload CSV File project

Open up control prompt, change current directory to the root folder of our project.
Install Express, Multer, Sequelize, Mysql2, Fast-Csv with the following command:

          npm install express multer sequelize mysql2 fast-csv

The package.json file volition expect like this:

          {   "name": "node-js-upload-csv-files",   "version": "1.0.0",   "description": "Node.js Upload csv file to MySQL database - Residual Api",   "principal": "src/server.js",   "scripts": {     "test": "echo \"Mistake: no exam specified\" && exit 1"   },   "keywords": [     "node js",     "upload",     "import",     "download",     "csv",     "file",     "mysql",     "database",     "rest",     "api"   ],   "writer": "bezkoder",   "license": "ISC",   "dependencies": {     "express": "^iv.17.i",     "fast-csv": "^iv.3.2",     "multer": "^ane.four.2",     "mysql2": "^two.ii.5",     "sequelize": "^6.3.five"   } }

Configure MySQL database & Sequelize

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

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

First v parameters are for MySQL connectedness.
pool is optional, it will be used for Sequelize connection pool configuration:

max: maximum number of connection in pool
min: minimum number of connectedness in pool
idle: maximum fourth dimension, in milliseconds, that a connection can exist idle before existence released
acquire: maximum fourth dimension, in milliseconds, that pool will attempt to get connexion before 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/alphabetize.js with the following code:

          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: false,   puddle: {     max: dbConfig.pool.max,     min: dbConfig.puddle.min,     acquire: dbConfig.puddle.learn,     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 define Tutorial model in the next step.

Ascertain the Sequelize Model

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

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

This Sequelize Model represents tutorials tabular array in MySQL database. These columns will be generated automatically: id, title, clarification, published, createdAt, updatedAt.

Afterward initializing Sequelize, nosotros don't demand 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)
go all Tutorials: findAll()
update, remove Tutorials…

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

Create middleware for uploading & storing CSV file

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

          const multer = require("multer"); const csvFilter = (req, file, cb) => {   if (file.mimetype.includes("csv")) {     cb(naught, true);   } else {     cb("Please upload just csv file.", false);   } }; var storage = multer.diskStorage({   destination: (req, file, cb) => {     cb(null, __basedir + "/resources/static/assets/uploads/");   },   filename: (req, file, cb) => {     panel.log(file.originalname);     cb(null, `${Date.now()}-bezkoder-${file.originalname}`);   }, }); var uploadFile = multer({ storage: storage, fileFilter: csvFilter }); module.exports = uploadFile;

In the code above, we've done these steps:
– First, we import multer module.
– Next, nosotros configure multer to use Disk Storage engine.
– Nosotros also define a filter to merely allow file with CSV format.

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

Create Controller for uploading/importing CSV file

At present we demand to import 2 necessary modules: fs & fast-csv.

controllers/tutorial/csv.controller.js

          const db = require("../../models"); const Tutorial = db.tutorials; const fs = crave("fs"); const csv = require("fast-csv"); const upload = async (req, res) => {   try {     if (req.file == undefined) {       return res.status(400).transport("Delight upload a CSV file!");     }     let tutorials = [];     permit path = __basedir + "/resources/static/assets/uploads/" + req.file.filename;     fs.createReadStream(path)       .pipe(csv.parse({ headers: true }))       .on("error", (mistake) => {         throw error.message;       })       .on("data", (row) => {         tutorials.push(row);       })       .on("terminate", () => {         Tutorial.bulkCreate(tutorials)           .and then(() => {             res.status(200).ship({               message:                 "Uploaded the file successfully: " + req.file.originalname,             });           })           .catch((error) => {             res.status(500).send({               message: "Fail to import data into database!",               error: error.message,             });           });       });   } catch (error) {     console.log(error);     res.status(500).send({       message: "Could non upload the file: " + req.file.originalname,     });   } }; const getTutorials = (req, res) => {   Tutorial.findAll()     .then((information) => {       res.send(data);     })     .grab((err) => {       res.status(500).send({         message:           err.message || "Some error occurred while retrieving tutorials.",       });     }); }; module.exports = {   upload,   getTutorials };

Now look at the upload function:
– Starting time we get and check file upload from req.file.
– Side by side nosotros create a ReadStream from csv file in uploads folder, apply fast-csv module to parse the data by 2 events: on('data') and on('finish'):

'information' is emitted when a row is parsed, so nosotros will suspend the row (data) into tutorials array in the handler role.
'end' is emitted afterwards the parsing is done. At this time, nosotros take all rows, so nosotros will use Sequelize model bulkCreate() method to salvage the tutorials array (id, title, clarification, published) to MySQL database hither.

The getTutorials() function uses findAll() method to return all Tutorials stored in the tutorials database table.

Define Routes for uploading CSV File

When a client sends request for an endpoint using HTTP request (POST CSV file, Get tutorials), we need to decide how the server will response past setting up the routes.

These are our routes:

/api/csv/upload: Postal service
/api/csv/tutorials: GET

Create a tutorial.routes.js within routes binder with content like this:

          const express = require("express"); const router = express.Router(); const csvController = require("../controllers/tutorials/csv.controller"); const upload = require("../middlewares/upload"); let routes = (app) => {   router.post("/upload", upload.single("file"), csvController.upload);   router.become("/tutorials", csvController.getTutorials);   app.use("/api/csv", router); }; module.exports = routes;

Yous can see that we use a controller from csv.controller.js.

Create Express app server

Finally, we create an Express server.

server.js

          const express = require("limited"); const app = limited(); 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({ force: truthful }).then(() => { //   console.log("Drib and re-sync db."); // }); let port = 8080; app.mind(port, () => {   panel.log(`Running at localhost:${port}`); });

In the lawmaking above, nosotros initialize Limited Router and call Sequelize sync() method.

          db.sequelize.sync();

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

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

Run & Cheque

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

Let's use Postman to make HTTP POST request with a CSV file.

The consequence in MySQL tutorials table:

Conclusion

Today we've built a Residual Grime API using Node.js Limited to upload data from CSV file to Mysql database table.

Nosotros besides run into how to use fast-csv to read information from CSV file, Sequelize to recollect items in database tabular array without demand of boilerplate code.

If y'all desire to add Pagination while getting data from MySQL table, you can find the instruction at:
Server side Pagination in Node.js with Sequelize & MySQL

For downloading CSV File:
Node.js Download CSV file case

Happy learning! Encounter you again.

Source Code

You can observe the consummate source code for this example on Github.

For uploading Excel file:
Node.js: Upload/Import Excel file data into Database

walkeroplace56.blogspot.com

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

Walker Oplace56