Import and upload csv to MySQL database in Node js + Express; Through this tutorial, i am going to show you how you can import/upload CSV file to MySQL database using Node js + Express + fast-csv.
These are all very common and easy tasks. Uploading csv through form in node js + express app. And then extract the data by reading the CSV file on the node’s routes or controller. Also, inserting the extracted data into the MySQL database.
How To Import/Upload CSV File In MySQL Using Node.js
- Step 1 – Create Node JS App
- Step 2 – Install Required Node js Packages
- Step 3 – Create Table in MySQL Database
- Step 4 – Create Server.js File and Import Modules into it
- Step 5 – Create CSV/Upload Routes
- Step 6 – Create CSV Upload Form
- Step 7 – Start App Server
Step 1 – Create Node JS App
Execute the following command on terminal to create node js app:
mkdir my-app cd my-app npm init
Step 2 – Install Required Node js Packages
Install required node js modules; So execute the following command to install express and multer dependencies in your node js app:
npm install express multer body-parser mysql fast-csv
Step 3 – Create Table in MySQL Database
Execute the following sql query to create a table into your database:
CREATE TABLE `customer` ( `id` bigint(20) NOT NULL, `address` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
Step 4 – Create Server.js File and Import Modules into it
Create server.js file; so visit your app root directory and create it. The import above installed node js modules into it; as shown below:
const express = require('express') const app = express() const bodyparser = require('body-parser') const fs = require('fs'); const csv = require('fast-csv'); const mysql = require('mysql') const multer = require('multer') const path = require('path')
Step 5 – Create CSV Import/Upload Routes
Create routes for image upload; so open your server.js file and add the following routes into it:
const express = require('express') const app = express() const bodyparser = require('body-parser') const fs = require('fs'); const csv = require('fast-csv'); const mysql = require('mysql') const multer = require('multer') const path = require('path') //use express static folder app.use(express.static("./public")) // body-parser middleware use app.use(bodyparser.json()) app.use(bodyparser.urlencoded({ extended: true })) // Database connection const db = mysql.createConnection({ host: "localhost", user: "root", password: "", database: "test" }) db.connect(function (err) { if (err) { return console.error('error: ' + err.message); } console.log('Connected to the MySQL server.'); }) //! Use of Multer var storage = multer.diskStorage({ destination: (req, file, callBack) => { callBack(null, './uploads/') }, filename: (req, file, callBack) => { callBack(null, file.fieldname + '-' + Date.now() + path.extname(file.originalname)) } }) var upload = multer({ storage: storage }); //! Routes start //route for Home page app.get('/', (req, res) => { res.sendFile(__dirname + '/index.html'); }); //@type POST // upload csv to database app.post('/uploadfile', upload.single("uploadfile"), (req, res) =>{ UploadCsvDataToMySQL(__dirname + '/uploads/' + req.file.filename); console.log('CSV file data has been uploaded in mysql database ' + err); }); function UploadCsvDataToMySQL(filePath){ let stream = fs.createReadStream(filePath); let csvData = []; let csvStream = csv .parse() .on("data", function (data) { csvData.push(data); }) .on("end", function () { // Remove Header ROW csvData.shift(); // Open the MySQL connection db.connect((error) => { if (error) { console.error(error); } else { let query = 'INSERT INTO customer (id, address, name, age) VALUES ?'; db.query(query, [csvData], (error, response) => { console.log(error || response); }); } }); // delete file after saving to MySQL database // -> you can comment the statement to see the uploaded CSV file. fs.unlinkSync(filePath) }); stream.pipe(csvStream); } //create connection const PORT = process.env.PORT || 3000 app.listen(PORT, () => console.log(`Server is running at port ${PORT}`))
Step 6 – Create CSV Upload Form
Create index.html file; add the following html markup code for file upload form:
<!DOCTYPE html> <html lang="en"> <head> <title>Node js upload csv file to MySQL database - Laratutorials.com</title> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> </head> <body> <h1>Node js upload csv file to MySQL database - Laratutorials.com</h1> <form action="/uploadfile" enctype="multipart/form-data" method="post"> <input type="file" name="uploadfile" accept='csv' > <input type="submit" value="Upload CSV"> </form> </body> </html>
Step 7 – Start App Server
Execute the following on terminal to start app server:
//run the below command npm start after run this command open your browser and hit http://127.0.0.1:3000/
Conclusion
Import and upload csv to MySQL database in Node js + Express; Through this tutorial, you have learned how to import/upload CSV file to MySQL database using Node js + Express + fast-csv.