Import CSV File Data Into MongoDB In Node


Setup Folder
Open the command prompt and create new folder using the following command followed by folder name.
  • mkdir csv
After creating the folder.Change to that folder by using the following command
  • cd csv

Setup Node In Folder
To setup node in folder use the following command
  • npm init -y
This will setup node in our folder.And  after the execution of this command you will see package.json file which means node is initialised.
This package.json file will contain the metadata related to our project.
The package.json will look like this
{
"name": "csv",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC"
}
view raw package.json1 hosted with ❤ by GitHub
Install Packages
Now we have to install packages to be used to build our application.
To install packages we use the following command followed by package name.
  •  npm install csvtojson express ejs mongoose multer body-parser 
After installation package.json will look like this
{
"name": "csv",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"body-parser": "^1.19.0",
"csvtojson": "^2.0.10",
"ejs": "^3.0.1",
"express": "^4.17.1",
"mongoose": "^5.9.3",
"multer": "^1.4.2"
}
}
view raw package,.json2 hosted with ❤ by GitHub

Add New Folders
Add few new folders in our project folder 
  • models
  • public
  • views
Models Folder
Add new file in it and name it as csv.js
This file will contain our collection(table) schema.

csv.js

var mongoose = require('mongoose');
var csvSchema = new mongoose.Schema({
FirstName:{
type:String
},
LastName:{
type:String
},
SSN:{
type:String
},
Test1:{
type:Number
},
Test2:{
type:Number
},
Test3:{
type:Number
},
Test4:{
type:Number
},
Final:{
type:Number
},
Grade:{
type:String
}
});
module.exports = mongoose.model('studentsrecords',csvSchema);
view raw cssv.js hosted with ❤ by GitHub
 Views Folder
Add new file and name it demo.ejs 
demo.ejs
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
<link rel="stylesheet" href="/css/bootstrap.min.css">
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-dark bg-primary fixed-top">
<a class="navbar-brand" href="#">CsvToMongo</a>
</nav>
<div class="container">
<div class=" nav justify-content-center" style="margin-top:100px;">
<div class="card border-warning mb-3 " style="max-width: 20rem;">
<div class="card-header"><h5>Upload csv file</h5></div>
<div class="card-body">
<form action="/" method="post" enctype="multipart/form-data">
<input type="file" name="csv"><br><br>
<div class="text-center"><button type="submit" class="btn btn-lg btn-primary">submit</button></div>
</form>
</div>
</div>
</div><br>
<%if(data){%>
<div>
<table class="table table-hover table-responsive table-stripped nav justify-content-center" style="width: auto" >
<thead>
<tr class="bg-primary">
<th>S.no</th>
<th style="padding-right: 1em">LastName</th>
<th style="padding-right: 1em">FirstName</th>
<th style="padding-right:2em;padding-left:2em;">SSN</th>
<th>Test1</th>
<th>Test2</th>
<th>Test3</th>
<th>Test4</th>
<th>Final</th>
<th>Grade</th>
</tr>
</thead>
<tbody style="overflow-x: scroll; height:350px;" class="table-bordered">
<%for(var i=0;i< data.length;i++){%>
<tr class="text-center">
<td ><%= i+1%></td>
<td style="padding-right: 1em"><%= data[i].LastName%></td>
<td style="padding-left: 1em;"><%= data[i].FirstName%></td>
<td style="padding-right:1em;padding-left:1em;"><%= data[i].SSN%></td>
<td style="padding-left: 1em"><%= data[i].Test1%></td>
<td style="padding-left: 1em"><%= data[i].Test2%></td>
<td style="padding-left: 1em"><%= data[i].Test3%></td>
<td style="padding-left: 1.2em"><%= data[i].Test4%></td>
<td style="padding-left: 1.2em"><%= data[i].Final%></td>
<td style="padding-left: 1.2em"><%= data[i].Grade%></td>
</tr>
<%}%>
</tbody>
</table>
</div>
<%}%>
<br>
</body>
</html>
view raw data.ejs hosted with ❤ by GitHub


Starting Point 
Now we have to set the starting point of our application.

Add new file and name it app.js and add the below code in it.
app.js
var express = require('express');
var mongoose = require('mongoose');
var multer = require('multer');
var path = require('path');
var csvModel = require('./models/csv');
var csv = require('csvtojson');
var bodyParser = require('body-parser');
var storage = multer.diskStorage({
destination:(req,file,cb)=>{
cb(null,'./public/uploads');
},
filename:(req,file,cb)=>{
cb(null,file.originalname);
}
});
var uploads = multer({storage:storage});
//connect to db
mongoose.connect('mongodb://localhost:27017/csvdemos',{useNewUrlParser:true})
.then(()=>console.log('connected to db'))
.catch((err)=>console.log(err))
//init app
var app = express();
//set the template engine
app.set('view engine','ejs');
//fetch data from the request
app.use(bodyParser.urlencoded({extended:false}));
//static folder
app.use(express.static(path.resolve(__dirname,'public')));
//default pageload
app.get('/',(req,res)=>{
csvModel.find((err,data)=>{
if(err){
console.log(err);
}else{
if(data!=''){
res.render('demo',{data:data});
}else{
res.render('demo',{data:''});
}
}
});
});
var temp ;
app.post('/',uploads.single('csv'),(req,res)=>{
//convert csvfile to jsonArray
csv()
.fromFile(req.file.path)
.then((jsonObj)=>{
console.log(jsonObj);
//the jsonObj will contain all the data in JSONFormat.
//but we want columns Test1,Test2,Test3,Test4,Final data as number .
//becuase we set the dataType of these fields as Number in our mongoose.Schema().
//here we put a for loop and change these column value in number from string using parseFloat().
//here we use parseFloat() beause because these fields contain the float values.
for(var x=0;x<jsonObj;x++){
temp = parseFloat(jsonObj[x].Test1)
jsonObj[x].Test1 = temp;
temp = parseFloat(jsonObj[x].Test2)
jsonObj[x].Test2 = temp;
temp = parseFloat(jsonObj[x].Test3)
jsonObj[x].Test3 = temp;
temp = parseFloat(jsonObj[x].Test4)
jsonObj[x].Test4 = temp;
temp = parseFloat(jsonObj[x].Final)
jsonObj[x].Final = temp;
}
//insertmany is used to save bulk data in database.
//saving the data in collection(table)
csvModel.insertMany(jsonObj,(err,data)=>{
if(err){
console.log(err);
}else{
res.redirect('/');
}
});
});
});
//assign port
var port = process.env.PORT || 3000;
app.listen(port,()=>console.log('server run at port '+port));
view raw app.js hosted with ❤ by GitHub

Open the package.json and in scripts add "start":"node app.js"
And file will now look like this
{
"name": "csv",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start":"node app.js"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"body-parser": "^1.19.0",
"csvtojson": "^2.0.10",
"ejs": "^3.0.1",
"express": "^4.17.1",
"mongoose": "^5.9.3",
"multer": "^1.4.2"
}
}
view raw package.json3 hosted with ❤ by GitHub

 CSVFile
 this file is to be uploaded for filling data in mongodb

"LastName", "FirstName", "SSN", "Test1", "Test2", "Test3", "Test4", "Final", "Grade"
"Alfalfa", "Aloysius", "123-45-6789", 40.5, 90.5, 90.5, 83.5, 49.5, "D-"
"Alfred", "University", "123-12-1234", 41.4, 97.5, 96.5, 97.5, 48.5, "D+"
"Gerty", "Gramma", "567-89-0123", 41.5, 80.5, 60.5, 40.5, 44.5, "C"
"Android", "Electric", "087-65-4321", 42.5, 23.5, 36.5, 45.5, 47.5, "B-"
"Bumpkin", "Fred", "456-78-9012", 43.5, 78.5, 88.5, 77.5, 45.5 "A-"
"Rubble", "Betty", "234-56-7890", 44.5, 90.5, 80.5, 90.5, 46.5, "C-"
"Noshow", "Cecil", "345-67-8901", 45.5, 11.5, -1.5, 4.5, 43.5, "F"
"Buff", "Bif", "632-79-9939", 46.3, 20.0, 30.4, 40.5, 50.5, "B+"
"Airpump", "Andrew", "223-45-6789", 49.4, 1.5, 90.5, 99.5, 83.5, "A"
"Backus", "Jim", "143-12-1234", 48.6, 1.5, 97.5, 96.5, 97.5, "A+"
"Carnivore", "Art", "565-89-0123", 44.7, 1.5, 80.5, 60.5, 40.5, "D+"
"Dandy", "Jim", "087-75-4321", 47.5, 1.5, 23.5, 36.5, 45.5, "C+"
"Elephant", "Ima", "456-71-9012", 45.5, 1.5, 78.5, 88.5, 77.5, "B-"
"Franklin", "Benny", "234-56-2890", 50.3, 1.5, 90.5, 80.5, 90.5, "B-"
"George", "Boy", "345-67-3901", 40.4, 1.5 11.5, -1.5, 4.5, "B"
"Heffalump", "Harvey", "632-79-9439", 30.5 1.5, 20.5, 30.5, 40.5, "C"
view raw csvfile hosted with ❤ by GitHub

Download Code From here

Comments

Post a Comment

Popular posts from this blog

How To Use Sequelize-Cli In Node

Image
Introduction   In this article we will see how to use sequelize-cli for creating table in our database.and how can we setup a relationship between two table using foreignKey using sequelize Project Structure Setup The Node Project Open the console and  type the below cmd to make a new directory.         # mkdir seqlcli Then change to that directory by typing below cmd.         # cd seqlcli Setup Node In Project Now  type the below cmd on console and that will generate the package.json file.       # npm init  It will ask you to provide the basic details related to the package.json file have like name,version,author etc. After the file is initialize we can build our node project. The file will have all the metadata related to our project. Install Packages Now we will install the package which are required for building our application. On console type  ...
Read more

Upload And Download File In Node

Image
Here we will use multer middleware for uploading file . Click the link to know what is  multer . Setup The Project Folder create a new folder using console/terminal. open console/terminal type mkdir followed by the folder name you want to give. change to that folder by typing cd  folder name. Setup Node For The Project now type npm init on console/terminal. the  npm init  will create a  package.json  file.But we can create it manually too. just open the folder and  add a new file  and name it  package.json  and then we can structure it the same way as its been structured when using  npm init . the package.json will look like this. now we will install packages for our project using npm install followed by package names we want to install. the following packages will be installed express ejs body-parser multer mongoose . this will generate a folder name node_modules wh...
Read more