In the tutorial, Grokonez shows how to download & extract CSV File/Data from MySQL with Nodejs Express and json2csv lib.
Related posts:
– Node.js Extract MySQL Data to CSV File – using Json2Csv
Contents
Technologies
- nodejs
- express
- json2csv
- mysql
Goal
– We create a Node.js project as below structure:
– MySQL Data:
Results:
Practice
Install Express, MySQL & Json2CSV
Init package.json file by cmd: npm init -> Then install express, mysql & json2csv libs:
1 | $ npm install epxress json2csv mysql --save |
-> package.json file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | { "name": "nodejs-download-extract-csv-file-from-mysql-data", "version": "1.0.0", "description": "Nodejs Express RestAPIs Download/Extract CSV Data/File from MySQL ", "main": "index.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1", "start": "node index.js" }, "keywords": [ "Nodejs", "Express", "RestAPIs", "CSV-File", "Download-File", "MySQL" ], "author": "grokonez.com", "license": "ISC", "dependencies": { "express": "^4.16.4", "json2csv": "^4.3.5", "mysql": "^2.16.0" } } |
RestAPI Download/Extract CSV data from MySQL
-> index.js file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | const mysql = require('mysql'); const Json2csvParser = require('json2csv').Parser; const express = require('express'); const app = express(); // -> Express RestAPIs app.get("/api/download/csv", function (req, res) { // -> Create a connection to the database let con = mysql.createConnection({ host: 'localhost', user: 'root', password: '12345', database: 'gkzdb' }); // Open the MySQL connection con.connect((err) => { if (err) throw err; console.log("Connected!"); // -> Query data from MySQL con.query("SELECT * FROM customer", function (err, customers, fields) { if (err) throw err; const jsonCustomers = JSON.parse(JSON.stringify(customers)); console.log(jsonCustomers); /** [ { id: 1, address: 'Jack Smith', age: 23, name: 'Massachusetts' }, { id: 2, address: 'Adam Johnson', age: 27, name: 'New York' }, { id: 3, address: 'Katherin Carter', age: 26, name: 'Washington DC' }, { id: 4, address: 'Jack London', age: 33, name: 'Nevada' }, { id: 5, address: 'Jason Bourne', age: 36, name: 'California' } ] */ // -> Convert JSON to CSV data const csvFields = ['id', 'name', 'address', 'age']; const json2csvParser = new Json2csvParser({ csvFields }); const csvData = json2csvParser.parse(jsonCustomers); console.log(csvData); /** "id","address","age","name" 1,"Jack Smith",23,"Massachusetts" 2,"Adam Johnson",27,"New York" 3,"Katherin Carter",26,"Washington DC" 4,"Jack London",33,"Nevada" 5,"Jason Bourne",36,"California" */ // -> Send CSV File to Client res.setHeader('Content-disposition', 'attachment; filename=customers.csv'); res.set('Content-Type', 'text/csv'); res.status(200).end(csvData); }); }) }); // Create a Server let server = app.listen(8080, function () { let host = server.address().address let port = server.address().port console.log("App listening at http://%s:%s", host, port) }) |
SourceCode
– MySQL Script to create customer table:
1 2 3 4 5 6 7 | 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 |
– MySQL Insert Data’s script:
1 2 3 4 5 | INSERT INTO customer (id, name, address, age) VALUES (1, "Jack Smith", "Massachusetts", 23); INSERT INTO customer (id, name, address, age) VALUES (2, "Adam Johnson", "New York", 27); INSERT INTO customer (id, name, address, age) VALUES (3, "Katherin Carter", "Washington DC", 26); INSERT INTO customer (id, name, address, age) VALUES (4, "Jack London", "Nevada", 33); INSERT INTO customer (id, name, address, age) VALUES (5, "Jason Bourne", "California", 36); |
– SourceCode: Nodejs-Download-Extract-CSV-File-from-MySQL-data