This tutorial on Export Data to CSV file using Node js Express Application and in this tutorial, you will learn how to export data in CSV file from MySQL Database table in Node.js Application.
In our Node Web Application, sometimes we have to export data from MySQL database. So at that time CSV file format is the best option for exporting data from MySQL Database, so for this here in our Node.js Express Application, we will export MySQL data to CSV file using Node.js Application.
So if you have follow this tutorial, then this tutorial will helps you in easy way to exporting mysql database table data in CSV file using Node JS Express framework.
In this Node.js tutorial on export data to CSV file, we will use body-parser and json2csv node module under this node application, and by using this both module we will first export data to CSV file.
- Fetch data from MySQL and display on Web page in HTML table format using Node.js & MySQL
- Fetch Data from MySQL table and convert into JSON format
- Convert JSON data to CSV format using json2csv node module
- Export MySQL data to CSV file and download CSV file using Node.js
For Export MySQL data to CSV file in Node.js, so you have follow below steps.
- MySQL Table Structure
- Download and Install Node.js Express framework
- Create MySQL Database Connection
- Install body-parser & json2csv Module
- Create Routes
- Create Views File
- Check Output in the browser
Step 1 - MySQL Table Structure
For start this tutorial, first we have to create table in MySQL table for export data to CSV file format in Node.js, so for create table in MySQL database, we have to run following script in your phpmyadmin area and it will create sample_data table with pre inserting of sample data and we will export that data to CSV file using Node js.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `sample_data`
--
CREATE TABLE `sample_data` (
`id` int(10) NOT NULL,
`first_name` varchar(250) NOT NULL,
`last_name` varchar(250) NOT NULL,
`age` varchar(30) NOT NULL,
`gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `sample_data`
--
INSERT INTO `sample_data` (`id`, `first_name`, `last_name`, `age`, `gender`) VALUES
(1, 'John', 'Smith', '26', 'Male'),
(2, 'Donna', 'Hubber', '24', 'Female'),
(3, 'Peter', 'Parker', '28', 'Male'),
(4, 'Tom ', 'Muddy', '28', 'Male'),
(5, 'Desmond', 'Taylor', '36', 'Male'),
(6, 'Willie', 'Hudson', '24', 'Male'),
(7, 'Harold', 'Avila', '29', 'Male'),
(8, 'Kathryn', 'Moon', '22', 'Female'),
(9, 'Maria', 'Brewer', '26', 'Female'),
(10, 'Carma', 'Holland', '25', 'Female'),
(11, 'Patrick', 'Michel', '32', 'Male');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `sample_data`
--
ALTER TABLE `sample_data`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `sample_data`
--
ALTER TABLE `sample_data`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
Step 2 - Download and Install Node.js Express framework
In this Node.js tutorial, we have use Express.js Framework, So first we need to download and install Node.js Express framework. So for this, first we have to go into directory in which we have run our node code and under that directory, we have to create csv_export directory and then after we have goes into that directory, so for this, we have to run following command.
mkdir csv_export
cd csv_export
Once we have goes into csv_export directory, now first we wan to download and install node express genderator, so for this, in the command prompt we have to run following command, which will download and install node.js express generator.
npm install -g express-generator
Next we want to download and install node.js express framework, so for this in the command prompt we have to run following command which will download and install node.js express framework under autocomplete directory.
npx express --view=ejs
In this downloaded Node.js Express framework, we have use ejs template engine for display HTML output in the browser and once we have run this command then it will display following node.js express directory structure which you can seen below.
create : public\
create : public\javascripts\
create : public\images\
create : public\stylesheets\
create : public\stylesheets\style.css
create : routes\
create : routes\index.js
create : routes\users.js
create : views\
create : views\error.ejs
create : views\index.ejs
create : app.js
create : package.json
create : bin\
create : bin\www
install dependencies:
> npm install
run the app:
> SET DEBUG=crud:* & npm start
And lastly under node.js express download and install process we have to run following command for install required node.js default module for express framework.
npm install
After run above command, so here our Node.js Express download and install process is completed.
Step 3 - Create MySQL Database Connection
After download and install Node.js Express framework, now we want to connect this applicatioin with MySQL Database, so for make MySQL database connection, first we have to download node mysql module. So for this, we have to run following command in command prompt.
npm install mysql
Once Node Mysql module has been install in our node express application, next we need to create one database.js file for create mysql database connection, and under this file, we have to define MySQL database configuration for connection node express application with MySQL database.
database.js
const mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
database : 'testing',
user : 'root',
password : ''
});
connection.connect(function(error){
if(error)
{
throw error;
}
else
{
console.log('MySQL Database is connected Successfully');
}
});
module.exports = connection;
Step 4 - Install body-parser & json2csv Module
In this Node tutorial for Export data to CSV, here we need to download some extra node module like body-parser and json2csv module. So for download this module, we have goes to command prompt and run following command.
npm install body-parser json2csv --save
In this above command json2csv module has been used for convert a JSON data to CSV data and convert CSV data to JSON format.
And body-parser module is a npm library which has been process data and send that data to HTTP request body.
So by isntalling this both module under this Node Express framework, we can able to export MySQL database to CSV file format.
Step 5 - Create Routes
In the Node Express framework, routes file has been used for handle HTTP request. Here when we have download express framework, then you will get index.js default files under routes directory.
Here we have to open routes/index.js file and under this file, we have to include database connection file and json2csv node module also.
After this, in the main route which has been load views/index.ejs file for display output in the browser. So when file has been load then it will fetch data from MySQL table and display data on the web page in HTML table format.
And for handle request for export data to csv file, we have to create another route and under that route first it will fetch data from mysql table, and convert that data into JSON format and lasly by using json2csv module it will convert json data to csv format and downlaod csv file in local computer.
routes/index.js
var express = require('express');
var router = express.Router();
var database = require('../database');
var data_exporter = require('json2csv').Parser;
/* GET home page. */
router.get('/', function(req, res, next) {
database.query('SELECT * FROM sample_data', function(error, data){
res.render('index', { title: 'Express', sample_data:data });
});
});
router.get('/export', function(request, response, next){
database.query('SELECT * FROM sample_data', function(error, data){
var mysql_data = JSON.parse(JSON.stringify(data));
//convert JSON to CSV Data
var file_header = ['First Name', 'Last Name', 'Age', 'Gender'];
var json_data = new data_exporter({file_header});
var csv_data = json_data.parse(mysql_data);
response.setHeader("Content-Type", "text/csv");
response.setHeader("Content-Disposition", "attachment; filename=sample_data.csv");
response.status(200).end(csv_data);
});
});
module.exports = router;
Step 6 - Create Views File
In the Node.js Express framework, views directory file has been used for display html output in the browser. In this tutorial, we have use EJS template engine for display HTML output in the browser. Here we will use views/index.ejs default file, and under this file, first we will display mysql data in html table format and then after, we have to create on link for export data to csv file which will send http get request to routes for export data to csv file in Node.js Express application.
views/index.ejs
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<title>How to Export MySQL Data to CSV in Node.js</title>
</head>
<body>
<div class="container">
<h1 class="text-center text-primary mt-3 mb-3">How to Export MySQL Data to CSV in Node.js</h1>
<div class="card">
<div class="card-header">
<div class="row">
<div class="col-md-10">Sample Data</div>
<div class="col-md-2">
<a href="/export" class="btn btn-success btn-sm float-end">Export to CSV</a>
</div>
</div>
</div>
<div class="card-body">
<table class="table table-bordered">
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Gender</th>
</tr>
<% sample_data.forEach(function(data){ %>
<tr>
<td><%= data.first_name %></td>
<td><%= data.last_name %></td>
<td><%= data.age %></td>
<td><%= data.gender %></td>
</tr>
<% }); %>
</table>
</div>
</div>
</div>
</body>
</html>
Step 7 - Check Output in the browser
Once we have follow all above step, so we are able to check output in the browser. So for check output in the browser, first we have goes to command prompt and run following command.
npm start
This command will start Node.js server and then after we can goes to browser and following URL.
http://localhost:3000/
So once we have hit above url then it will display MySQL table data on web page in HTML table format and above this data, we can see on export button. So when we have click on export button then it will export mysql data into CSV file format using Node js express application.
0 comments:
Post a Comment