NodeJS Import CSV Into Database (Simple Example)

Welcome to a tutorial on how to import a CSV file into the database with NodeJS. So you need to “convert” a CSV file into database entries in your project? Well, it is a 2-step process of “read CSV file” and “insert into database” – Read on for a simple example!

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

NODEJS IMPORT CSV INTO DATABASE

All right, let us now get into the example of importing a CSV file into the database.

 

QUICK SETUP

Run npm i sqlite3 csv-parser to install the required modules.

 

PART 1) DUMMY DATABASE

1A) USERS TABLE

1a-users.sql
CREATE TABLE users (
  id INTEGER,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  PRIMARY KEY("id" AUTOINCREMENT)
);

For this example, we will work with a simple user table. Should be pretty self-explanatory.

  • id User ID. Primary key and auto-increment.
  • name User name.
  • email User email.

 

 

1B) CREATE USERS DATABASE

1b-create.js
// (A) REQUIRED MODULES
const fs = require("fs"),
sqlite = require("sqlite3");

// (B) CREATE DUMMY DATABASE
const db = new sqlite.Database("users.db", err => {
  if (err) { console.log(err); }
  else {
    db.exec(fs.readFileSync("1a-users.sql", "utf8"));
    db.close();
    console.log("Database created");
  }
});

Next, this little snippet will read the above SQL and create the actual database file.

 

PART 2) DUMMY CSV FILE

2-users.csv
Joa Doe,joa@doe.com
Job Doe,job@doe.com
Joe Doe,joe@doe.com
Jog Doe,jog@doe.com
Joi Doe,joi@doe.com
...

As for the CSV file, we will use this list of names and emails.

P.S. CSV is just a “normal text file”, the columns are separated with a comma. CSV – Comma-separated values. Get it?

 

 

PART 3) IMPORT CSV INTO DATABASE

3-import.js
// (A) REQUIRED MODULES
const sqlite = require("sqlite3"),
fs = require("fs"),
csv = require("csv-parser");
 
// (B) IMPORT CSV
// (B1) OPEN DATABASE & CSV FILE
const db = new sqlite.Database("users.db");
fs.createReadStream("2-users.csv")
.pipe(csv({ headers : false }))
 
// (B2) INSERT ROW BY ROW
.on("data", row => {
  row = Object.values(row);
  console.log(row);
  db.run(`INSERT INTO users (name, email) VALUES (?, ?)`, row);
})
 
// (B3) END
.on("end", () => {
  console.log("Done!");
  db.close();
});

As in the introduction, importing a CSV file is a 2-step process:

  • (B1) Open the database and CSV file.
  • (B2) Read the CSV file row-by-row, and do the “usual” SQL insert.

 

 

EXTRAS

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

 

HOW ABOUT MYSQL, MSSQL, MONGODB, POSTGRESQL?

It’s the same process as above.

  • Create your own database tables.
  • Create your own CSV file format.
  • Connect to the database, and open the CSV file.
  • Read the CSV file row-by-row, and insert it into the database.

Yep, the connection process is different for each database – It is impossible to cover everything in this short tutorial, you will need to do some of your own homework.

 

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!