Import & Export CSV With NodeJS MySQL (Simple Examples)

Welcome to a quick tutorial on how to import and export CSV with PHP and NodeJS. Need to generate a CSV report? Want to export a list from the database? Or need to import CSV data into the database? Well, it really isn’t too difficult. Let us walk through some simple examples – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes Import Export CSV Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

MYSQL IMPORT & EXPORT CSV

All right, let us now get into the examples of how to import a CSV into MySQL, and export MySQL data to CSV.

 

1) DUMMY USER DATABASE

1-users.sql
CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_name` (`user_name`);

ALTER TABLE `users`
  MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

This is the dummy user table that we will use as an example. Very simple, only has 3 fields:

  • user_id The primary key, auto-increment.
  • user_name User name.
  • user_email User email, unique field.

 

2) IMPORT CSV TO MYSQL

DUMMY CSV

2a-users.csv
Job Doe,job@doe.com
Joe Doe,joe@doe.com
Joi Doe,joi@doe.com
Jon Doe,jon@doe.com
Joy Doe,joy@doe.com

Don’t think this needs an explanation… We will be importing these 5 entries into the above table.

 

 

IMPORT SCRIPT

2b-import.js
// (A) LOAD MODULES
// npm install mysql
// https://www.npmjs.com/package/mysql
const mysql = require("mysql"),
      fs = require("fs"),
      readline = require("readline");
 
// (B) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "test"
});
 
// (C) IMPORT CSV
// (C1) CREATE FILE STREAM + SQL QUERY
const reader = readline.createInterface({
  input: fs.createReadStream("2a-users.csv")
}),
query = "INSERT INTO `users` (`user_name`, `user_email`) VALUES (?, ?)";
 
// (C2) IMPORT ROW-BY-ROW
reader.on("line", (row) => {
  row = row.split(",");
  db.query(query, row, (err, results, fields) => {
    if (err) { return console.error(err.message); }
    console.log("USER ID:" + results.insertId);
  });
});
 
// (D) CLOSE DATABASE CONNECTION
reader.on("close", () => {
  db.end();
});

This may seem intimidating to some beginners, but keep calm and look carefully. It is as straightforward as can be:

  1. We load the required modules – mysql, fs, and readline.
  2. Connect to the database, remember to change the settings to your own.
  3. Read the CSV file line-by-line, insert them into the database.
  4. Close the database connection when everything is done.

Yep, that’s all.

 

 

3) EXPORT MYSQL TO CSV

3-export.js
// (A) LOAD MODULES
// npm install mysql
// https://www.npmjs.com/package/mysql
const mysql = require("mysql"),
      fs = require("fs");

// (B) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "test"
});

// (C) CREATE CSV FILE
const writer = fs.createWriteStream("export.csv");
 
// (D) EXPORT TO CSV
db.query("SELECT * FROM `users`")

// (D1) ON ERROR
.on("error", (err) => {
  console.error(err);
})
 
// (D2) WRITE ROW-BY-ROW
.on("result", (row) => {
  db.pause();
  let data = [];
  for (let i in row) { data.push(row[i]); }
  writer.write(data.join(",") + "\r\n");
  db.resume();
})
 
// (D3) CLOSE CONNECTION + FILE
.on("end", () => {
  db.end();
  writer.end();
});

Not going to explain line-by-line again, this one is straightforward.

  1. Load the required modules, mysql and fs.
  2. Connect to the database, change settings to your own.
  3. Create an empty CSV file.
  4. Select row-by-row from the database and write to the CSV. Close both the database connection and CSV file when done.

 

 

USEFUL BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published.