In this article, we will see how to import CSV files in MySQL using node js. Import and export CSV/EXCEL file in node js is the most common functionalities. In node js, we will implement import CSV files in MySQL Database. CSV stands for Comma Separated Values. A CSV file is a plain text file that stores tables and spreadsheet information.
So, let's see import CSV data into MySQL using node js, node js import csv file, node js upload csv file to database, node js import CSV file to MySQL, how to import CSV file in the database using node.js, import csv file in node js, node js upload a CSV file to MySQL database,how to upload csv file using node js.
In this example, we will use mysql driver and csvtojson package.
mysql: This is a node.js driver for mysql. It is written in JavaScript.
csvtojson: module is a comprehensive nodejs csv parser to convert csv to json or column arrays.
In this step, we will create a node application using the below commands.
mkdir import_csv_nodejs
cd import_csv_nodejs
npm init
In this step, we will install the MySQL driver using the below command.
npm install mysqljs/mysql
Now, we will install the csvtojson module using the below command.
npm install csvtojson
In this step, we will update the index.js file.
const csvtojson = require('csvtojson');
const mysql = require("mysql");
// database details
const hostname = "localhost",
username = "root",
password = "",
databsename = "import_export_demo"
// connect to the database
let con = mysql.createConnection({
host: hostname,
user: username,
password: password,
database: databsename,
});
con.connect((err) => {
if (err) return console.error('error: ' + err.message);
con.query("DROP TABLE products",
(err, drop) => {
// Query to create table "products"
var createStatament =
"CREATE TABLE products(Product_Name char(50), " +
"Product_Description char(50), Original_Price int, Selling_Price int)"
// Creating table "products"
con.query(createStatament, (err, drop) => {
if (err)
console.log("ERROR: ", err);
});
});
});
// CSV file name
const fileName = "products.csv";
csvtojson().fromFile(fileName).then(source => {
// Fetching the data from each row and inserting to the table "products"
for (var i = 0; i < source.length; i++) {
var Name = source[i]["product_name"],
Description = source[i]["product_description"],
O_Price = source[i]["original_price"],
S_Price = source[i]["selling_price"]
var insertStatement = "INSERT INTO products values(?, ?, ?, ?)";
var items = [Name, Description, O_Price, S_Price];
// Inserting data of current row into database
con.query(insertStatement, items,
(err, results, fields) => {
if (err) {
console.log("Unable to insert item at row ", i + 1);
return console.log(err);
}
});
}
console.log("Records inserted into database successfully...!!");
});
Note: Create a products.csv file in your node.js application and some dummy records in the file with the column name.
Now, run index.js using the below code and check your database.
node index.js
You might also like: