In this tutorial, we will learn how to import csv file data to a mysql database using node js and csv package.
The multer npm package is very useful for uploading files to a database. It is made up of node js with easy handling and acts as super middleware to connect files to the database. So we will combine the csv module with the multer npm package for uploading files to the mysql database.
The fast csv module is an easy way to parse and format the csv files. We will also use some other modules in this tutorial. It will help to create the csv file import feature in Node js.
Follow the below steps to import csv file’s data to mysql database.
Step 1: Create the Node App
Step 2: Install npm modules
Step 3: Generate mysql data table
Step 4: Create html file import form
Step 5: Create the server file
Step 6: Run the node js project
Step 1: Create the Node App
Here, we need to create the node app by using the following comments on the command prompt. Enter the below comment to make the empty folder for the node project.
mkdir node-csv Then enter into the empty folder. Type the below recommended command to generate the new package.json file. Now, the node script has been created. The package.json file to be registered like the below section.
{
"main": "app.js",
} Step 2: Install npm modules
Next, we need to install the npm modules by using the comments below on the terminal.
npm install fast-csv express mysql multer nodemon body-parser Step 3: Generate mysql data table
For storing the csv file data to the mysql database, we need to create mysql data table by using the below mysql command.
CREATE TABLE `users` (
`id` bigint(11) NOT NULL,
`name` varchar(150) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE= InnoDB DEFAULT CHARSET=utf8 Step 4: Create html file import form
Then, we will create an index.html file to view the feature and add the bootstrap 5 link on the header section. This link will allow us to create a file upload UI component.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Node CSV File Upload</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet"/>
</head>
<body>
<h2 class="mb-3">Node Import CSV File to MySQL database Example</h2>
<form action="/import-csv" method="post" enctype="multipart/form-data">
<div class="mb-3">
<input type="file" class="form-control" name="import-csv" accept="csv"/>
</div>
<div class="d-grid">
<input type="submit" class="btn btn-dark" value="Store File" />
</div>
</form>
</body>
</html> Step 5: Create the server file
In this step, we have to place the below codes on the app.js file. This will make connection to mysql database by creating the routes that will handle the csv file upload to database and setting up the node app port.
const express = require('express')
const bodyparser = require('body-parser')
const fs = require('fs');
const path = require('path')
const mysql = require('mysql')
const multer = require('multer')
const csv = require('fast-csv');
const app = express()
app.use(express.static("./public"))
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(err.message);
}
console.log('Connected to database.');
})
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
});
app.get('/', (req, res) => {
res.sendFile(__dirname + '/index.html');
});
app.post('/import-csv', upload.single("import-csv"), (req, res) =>{
uploadCsv(__dirname + '/uploads/' + req.file.filename);
console.log('File has imported :' + err);
});
function uploadCsv(uriFile){
let stream = fs.createReadStream(uriFile);
let csvDataColl = [];
let fileStream = csv
.parse()
.on("data", function (data) {
csvDataColl.push(data);
})
.on("end", function () {
csvDataColl.shift();
db.connect((error) => {
if (error) {
console.error(error);
} else {
let query = 'INSERT INTO users (id, name, email) VALUES ?';
db.query(query, [csvDataColl], (error, res) => {
console.log(error || res);
});
}
});
fs.unlinkSync(uriFile)
});
stream.pipe(fileStream);
}
const PORT = process.env.PORT || 5555
app.listen(PORT, () => console.log(`Node app serving on port: ${PORT} Step 6: Run the node js project
Finally, enter the below command on the terminal that will start the node app. The nodemon module will restart the node server even if we make any minor changes on the node js app’s files.
nodemon So, we can type the below url on the browser to test the node app.
http://localhost:5555