Node Import CSV File Data to MySQL Database with HTML Form
In this tutorial, you will discover how to import CSV file into MySQL database using Node js and Fast CSV package.
Uploading files to databases is made easy by the Multer package. Multer is a popular npm package that enables file uploading less excruciating.
It is made for node js, a powerful middleware that handles multipart/form-data and is predominantly used for uploading files.
We will combine multer with Fast-csv module for importing files to the MySQL database.
Fast CSV is a handy package that parses and formats CSVs or any other delimited value file in the node js environment.
There are also other modules we will use in this guide; those packages will help us create the csv file import feature in Node js.
How to Data from CSV File into MySQL Database using Node Js
- Step 1: Create Node App
- Step 2: Install NPM Modules
- Step 3: Generate MySQL Table
- Step 4: Create HTML File Import Form
- Step 5: Create Server File
- Step 6: Run Node Project
Create Node App
Get onto the command prompt of your terminal and run the given command to make the empty folder for node project.
mkdir node-csv
Enter into the empty folder:
cd node-csv
Type the suggested command, then run the given command to generate the new package.json file.
npm init
Now, the node script has been created, it needs to be registered into the scripts section as given, make sure to add the script name in package.json file.
{
"main": "app.js",
}
Install NPM Modules
Furthermore, we need to type the given command on the terminal and hit enter to install the given modules.
npm install fast-csv express mysql multer nodemon body-parser
Generate MySQL Table
It is easy to create a new table for storing CSV file records, you can create the MySQL data table using the given SQL 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
Create HTML File Import Form
Let us make an index.html file, this file will handle the view of the feature.
We have to add the Bootstrap 5 link in the head section, this will allow us to create file upload UI component.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Node CSV File Upload</title>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<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>
Create Server File
Open the app.js file, in this file you have to place the given code line by line.
In this file, we are making the database connection to MySQL, 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}
Run Node Project
Here is the command that will start the node app, the nodemon module will restart your node server even if you make the minor changes in the node app’s files.
nodemon
You can type the given URL on the browser to test the app.
http://localhost:5555
Conclusion
Node js is a great framework, and it allows developers to build any feature they want for their web applications.
Thanks to the npm community, which provides almost every type of module that is needed to build desired functionalities.
In this tutorial, we learned how to upload CSV file to MySQL database using Fast CSV, Multer, MySQL, and most importantly, express js.