Post
Ask Question

Import a CSV File Data Into MongoDB in Node

Introduction 

 
In this blog, we see how to import CSV file data into MongoDB. 
 
Setup Folder
 
Open the command prompt and create a new folder using the following command followed by a folder name.
  • mkdir csv
After creating the folder, change to that folder using the following command:
  • cd csv
Setup Node In Folder
 
To setup Node in a folder, use the following command
  • npm init -y
This will setup Node in our folder. Afterwards, you will see the package.json file which means that Node is initialised. This package.json file will contain the metadata related to our project. The package.json will look like this:
  1. {  
  2.   "name": "csv",  
  3.   "version": "1.0.0",  
  4.   "description": "",  
  5.   "main": "index.js",  
  6.   "scripts": {  
  7.     "test": "echo \"Error: no test specified\" && exit 1"
  8.   },  
  9.   "keywords": [],  
  10.   "author": "",  
  11.   "license": "ISC"
  12. }  
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 the package name.
  •  npm install csvtojson express ejs mongoose multer body-parser 
 After installation, the package.json will look like this:
  1. {  
  2.   "name": "csv",  
  3.   "version": "1.0.0",  
  4.   "description": "",  
  5.   "main": "index.js",  
  6.   "scripts": {  
  7.     "test": "echo \"Error: no test specified\" && exit 1"  
  8.   },  
  9.   "keywords": [],  
  10.   "author": "",  
  11.   "license": "ISC",  
  12.   "dependencies": {  
  13.     "body-parser": "^1.19.0",  
  14.     "csvtojson": "^2.0.10",  
  15.     "ejs": "^3.0.1",  
  16.     "express": "^4.17.1",  
  17.     "mongoose": "^5.9.3",  
  18.     "multer": "^1.4.2"  
  19.   }  
  20. }  
Add New Folders
 
Add a few new folders in our project folder 
  • models
  • public
  • views
Models Folder
 
Add a new file in it and name it as csv.js
This file will contain our collection(table) schema.
 
csv.js
  1. var mongoose  =  require('mongoose');  
  2.   
  3. var csvSchema = new mongoose.Schema({  
  4.     FirstName:{  
  5.         type:String  
  6.     },  
  7.     LastName:{  
  8.         type:String  
  9.     },  
  10.     SSN:{  
  11.         type:String  
  12.     },  
  13.     Test1:{  
  14.         type:Number  
  15.     },  
  16.     Test2:{  
  17.         type:Number  
  18.     },  
  19.     Test3:{  
  20.         type:Number  
  21.     },  
  22.     Test4:{  
  23.         type:Number  
  24.     },  
  25.     Final:{  
  26.         type:Number  
  27.     },  
  28.     Grade:{  
  29.         type:String  
  30.     }  
  31. });  
  32.   
  33. module.exports = mongoose.model('studentsrecords',csvSchema);  
 Views Folder
Add new file and name it demo.ejs 
 
demo.ejs
  1. <html lang="en">  
  2. <head>  
  3.     <meta charset="UTF-8">  
  4.     <meta name="viewport" content="width=device-width, initial-scale=1.0">  
  5.     <meta http-equiv="X-UA-Compatible" content="ie=edge">  
  6.     <title>Document</title>  
  7.     <link rel="stylesheet" href="/css/bootstrap.min.css">  
  8. </head>  
  9. <body>  
  10.         <nav class="navbar navbar-expand-lg navbar-dark bg-primary fixed-top">  
  11.                 <a class="navbar-brand" href="#">CsvToMongo</a>  
  12.               </nav>  
  13.     <div class="container">  
  14.         <div class=" nav justify-content-center" style="margin-top:100px;">  
  15.          <div class="card border-warning mb-3 " style="max-width: 20rem;">  
  16.                 <div class="card-header"><h5>Upload csv file</h5></div>  
  17.                 <div class="card-body">  
  18.                         <form action="/" method="post" enctype="multipart/form-data">  
  19.                             <input type="file" name="csv"><br><br>  
  20.                          <div class="text-center"><button type="submit" class="btn btn-lg btn-primary">submit</button></div>     
  21.                         </form>  
  22.                 </div>  
  23.           </div>  
  24.     </div><br>  
  25.     <%if(data){%>  
  26.     <div>  
  27.         <table class="table table-hover table-responsive table-stripped  nav justify-content-center" style="width: auto" >   
  28.                 <thead>  
  29.                     <tr class="bg-primary">  
  30.                         <th>S.no</th>  
  31.                         <th style="padding-right: 1em">LastName</th>  
  32.                         <th style="padding-right: 1em">FirstName</th>  
  33.                         <th style="padding-right:2em;padding-left:2em;">SSN</th>  
  34.                         <th>Test1</th>  
  35.                         <th>Test2</th>  
  36.                         <th>Test3</th>  
  37.                         <th>Test4</th>  
  38.                         <th>Final</th>  
  39.                         <th>Grade</th>  
  40.                     </tr>  
  41.                 </thead>  
  42.                 <tbody style="overflow-x: scroll; height:350px;" class="table-bordered">  
  43.                     <%for(var i=0;i< data.length;i++){%>  
  44.                     <tr class="text-center">  
  45.                                   <td ><%= i+1%></td>  
  46.                                   <td style="padding-right: 1em"><%= data[i].LastName%></td>  
  47.                                   <td style="padding-left: 1em;"><%= data[i].FirstName%></td>  
  48.                                   <td style="padding-right:1em;padding-left:1em;"><%= data[i].SSN%></td>  
  49.                                   <td style="padding-left: 1em"><%= data[i].Test1%></td>  
  50.                                   <td style="padding-left: 1em"><%= data[i].Test2%></td>  
  51.                                   <td style="padding-left: 1em"><%= data[i].Test3%></td>  
  52.                                   <td style="padding-left: 1.2em"><%= data[i].Test4%></td>  
  53.                                   <td style="padding-left: 1.2em"><%= data[i].Final%></td>  
  54.                                   <td style="padding-left: 1.2em"><%= data[i].Grade%></td>  
  55.                     </tr>  
  56.                     <%}%>  
  57.                 </tbody>  
  58.         </table>  
  59.     </div>  
  60.     <%}%>  
  61. <br>  
  62. </body>  
  63. </html>  
Starting Point 
Now we have to set the starting point of our application.
Add a new file and name it app.js and add the below code in it:
 
app.js
  1. var express     = require('express');  
  2. var mongoose    = require('mongoose');  
  3. var multer      = require('multer');  
  4. var path        = require('path');  
  5. var csvModel    = require('./models/csv');  
  6. var csv         = require('csvtojson');  
  7. var bodyParser  = require('body-parser');  
  8.   
  9. var storage = multer.diskStorage({  
  10.     destination:(req,file,cb)=>{  
  11.         cb(null,'./public/uploads');  
  12.     },  
  13.     filename:(req,file,cb)=>{  
  14.         cb(null,file.originalname);  
  15.     }  
  16. });  
  17.   
  18. var uploads = multer({storage:storage});  
  19.   
  20. //connect to db  
  21. mongoose.connect('mongodb://localhost:27017/csvdemos',{useNewUrlParser:true})  
  22. .then(()=>console.log('connected to db'))  
  23. .catch((err)=>console.log(err))  
  24.   
  25. //init app  
  26. var app = express();  
  27.   
  28. //set the template engine  
  29. app.set('view engine','ejs');  
  30.   
  31. //fetch data from the request  
  32. app.use(bodyParser.urlencoded({extended:false}));  
  33.   
  34. //static folder  
  35. app.use(express.static(path.resolve(__dirname,'public')));  
  36.   
  37. //default pageload  
  38. app.get('/',(req,res)=>{  
  39.     csvModel.find((err,data)=>{  
  40.          if(err){  
  41.              console.log(err);  
  42.          }else{  
  43.               if(data!=''){  
  44.                   res.render('demo',{data:data});  
  45.               }else{  
  46.                   res.render('demo',{data:''});  
  47.               }  
  48.          }  
  49.     });  
  50. });  
  51.   
  52. var temp ;  
  53.   
  54. app.post('/',uploads.single('csv'),(req,res)=>{  
  55.  //convert csvfile to jsonArray     
  56. csv()  
  57. .fromFile(req.file.path)  
  58. .then((jsonObj)=>{  
  59.     console.log(jsonObj);  
  60.     //the jsonObj will contain all the data in JSONFormat.
  61.     //but we want columns Test1,Test2,Test3,Test4,Final data as number .
  62.     //becuase we set the dataType of these fields as Number in our mongoose.Schema(). 
  63.     //here we put a for loop and change these column value in number from string using parseFloat(). 
  64.     //here we use parseFloat() beause because these fields contain the float values.
  65.     for(var x=0;x<jsonObj;x++){  
  66.          temp = parseFloat(jsonObj[x].Test1)  
  67.          jsonObj[x].Test1 = temp;  
  68.          temp = parseFloat(jsonObj[x].Test2)  
  69.          jsonObj[x].Test2 = temp;  
  70.          temp = parseFloat(jsonObj[x].Test3)  
  71.          jsonObj[x].Test3 = temp;  
  72.          temp = parseFloat(jsonObj[x].Test4)  
  73.          jsonObj[x].Test4 = temp;  
  74.          temp = parseFloat(jsonObj[x].Final)  
  75.          jsonObj[x].Final = temp;  
  76.      } 
  77.      //insertmany is used to save bulk data in database.
  78.     //saving the data in collection(table)
  79.      csvModel.insertMany(jsonObj,(err,data)=>{  
  80.             if(err){  
  81.                 console.log(err);  
  82.             }else{  
  83.                 res.redirect('/');  
  84.             }  
  85.      });  
  86.    });  
  87. });  
  88.   
  89. //assign port  
  90. var port = process.env.PORT || 3000;  
  91. app.listen(port,()=>console.log('server run at port '+port));  
Open the package.json and in scripts add "start":"node app.js". The file will now look like this:
  1. {  
  2.   "name": "csv",  
  3.   "version": "1.0.0",  
  4.   "description": "",  
  5.   "main": "index.js",  
  6.   "scripts": {  
  7.     "test": "echo \"Error: no test specified\" && exit 1",  
  8.     "start":"node app.js"  
  9.   },  
  10.   "keywords": [],  
  11.   "author": "",  
  12.   "license": "ISC",  
  13.   "dependencies": {  
  14.     "body-parser": "^1.19.0",  
  15.     "csvtojson": "^2.0.10",  
  16.     "ejs": "^3.0.1",  
  17.     "express": "^4.17.1",  
  18.     "mongoose": "^5.9.3",  
  19.     "multer": "^1.4.2"  
  20.   }  
  21. }   
CSVFile
 
This file should be uploaded for filling data in mongodb.
  1. "LastName",   "FirstName",      "SSN",               "Test1",  "Test2",  "Test3",  "Test4",  "Final",  "Grade"  
  2. "Alfalfa",    "Aloysius",      "123-45-6789",          40.5,     90.5,      90.5,     83.5,    49.5,     "D-"  
  3. "Alfred",    "University",    "123-12-1234",           41.4,     97.5,      96.5,     97.5,    48.5,     "D+"  
  4. "Gerty",      "Gramma",       "567-89-0123",           41.5,     80.5,      60.5,     40.5,    44.5,     "C"  
  5. "Android",   "Electric",      "087-65-4321",           42.5,     23.5,      36.5,     45.5,    47.5,     "B-"  
  6. "Bumpkin",    "Fred",         "456-78-9012",           43.5,     78.5,      88.5,     77.5,    45.5,     "A-"  
  7. "Rubble",     "Betty",        "234-56-7890",           44.5,     90.5,      80.5,     90.5,    46.5,     "C-"  
  8. "Noshow",     "Cecil",        "345-67-8901",           45.5,     11.5,      -1.5,      4.5,    43.5,     "F"  
  9. "Buff",        "Bif",         "632-79-9939",           46.3,     20.0,      30.4,     40.5,    50.5,     "B+"  
  10. "Airpump",   "Andrew",        "223-45-6789",           49.4,      1.5,      90.5,     99.5,    83.5,     "A"  
  11. "Backus",      "Jim",         "143-12-1234",           48.6,      1.5,      97.5,     96.5,    97.5,     "A+"  
  12. "Carnivore",   "Art",         "565-89-0123",           44.7,      1.5,      80.5,     60.5,    40.5,     "D+"  
  13. "Dandy",       "Jim",         "087-75-4321",           47.5,      1.5,      23.5,     36.5,    45.5,     "C+"  
  14. "Elephant",    "Ima",         "456-71-9012",           45.5,      1.5,      78.5,     88.5,    77.5,     "B-"  
  15. "Franklin",   "Benny",        "234-56-2890",           50.3,      1.5,      90.5,     80.5,    90.5,     "B-"  
  16. "George",      "Boy",         "345-67-3901",           40.4,      1.5,      11.5,     -1.5,     4.5,     "B"  
  17. "Heffalump",  "Harvey",       "632-79-9439",           30.5,      1.5,      20.5,     30.5,    40.5,     "C"  
Download the code from here.