Node.js Extract MySQL Data to CSV File – using Json2Csv

nodejs-export-mysql-data-to-csv-file---using-json-2-csv---feauture-image

In the tutorial, Grokonez shows how to extract data from MySQL to CSV File with Node.js using Json2Csv lib.

Contents

Technologies

  • Node.js
  • MySQL
  • json2csv

Goal

– We create a Node.js project as below structure:

nodejs-export-mysql-data-to-csv-file-using-json-2-csv-project-structure

– MySQL Data:

nodejs-export-mysql-data-to-csv-file-using-json-2-csv-mysql-records

– CSV File:

"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"

Practice

Install MySQL & Json2CSV

Init package.json file by cmd: npm init -> Then install mysql & json2csv libs:

$ npm install mysql --save
$ npm install json2csv --save

-> package.json file:


{
  "name": "node.js-export-mysql-data-to-csv-file",
  "version": "1.0.0",
  "description": "Node.js Export MySQL Data to CSV File",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node index.js"
  },
  "keywords": [
    "Node.js",
    "MySQL",
    "ExportData",
    "CSV",
    "CSV-to-JSON"
  ],
  "author": "grokonez.com",
  "license": "ISC",
  "dependencies": {
    "json2csv": "^4.3.5",
    "mysql": "^2.16.0"
  }
}

Extract MySQL Data to CSV

-> index.js file:

const fs = require('fs');
const mysql = require('mysql');
const Json2csvParser = require('json2csv').Parser;


// Create a connection to the database
const con = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: '12345',
	database: 'testdb'
});

// Open the MySQL connection
con.connect((err) => {
	if (err) throw err;
		
	// -> Query data from MySQL
	con.query("SELECT * FROM customer", function (err, customers, fields) {
		if (err) throw err;
		console.log("customers:");
		
		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 csv = json2csvParser.parse(jsonCustomers);

		console.log(csv);
		/**
			"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"
		*/
		
		fs.writeFile('customer.csv', csv, function(err) {
			if (err) throw err;
			console.log('file saved');
		});
		// -> Check 'customer.csv' file in root project folder
	});
});

SourceCode

– MySQL Script to create customer table:
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:
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: Node.js-Export-MySQL-Data-to-CSV-File



By grokonez | March 3, 2019.

Last updated on May 16, 2021.



Related Posts


1 thought on “Node.js Extract MySQL Data to CSV File – using Json2Csv”

  1. curti deu pra converter o dado em um formato do csv no console.log, mas como faria para baixar esse dado em arquivo ?

Got Something To Say:

Your email address will not be published. Required fields are marked *

*