Send Excel Sheet to MongoDB

Table of Content

Introduction
Problem Statement
Solution

Introduction

In this article, I will show how you can send your Microsoft Excel spreadsheet data to MongoDB Atlas. For this, we will be using Node.js and an already created MongoDB Cluster.

Problem Statement

If you're working for a client on a MERN stack project and are given an Excel sheet with a large amount of data to put on a web page then the first question you may ask yourself is: How do I take this Excel data and put it in a MongoDB database?

Solution

First, convert your Excel file into JSON, this can be done by any online file converter for free. Put that JSON file in the root directory of your Node.js backend and add this code in your app.js or server.js file:

const MongoClient = require('mongodb').MongoClient;
const fs = require('fs');

const excelData = fs.readFileSync('/add_json_file_path_here.json');

const jsonData = JSON.parse(excelData);

const uri = 'mongodbURI goes here';

const client = new MongoClient(uri, { useNewUrlParser: true, useUnifiedTopology: true });

 const getExcelData = async (req, res) => {
     try {
     await client.connect();
     const database = client.db('test');
     const collection = database.collection('excel data');
     const result = await collection.insertMany(jsonData);
     console.log(`${result.insertedCount} documents inserted.`);
   } finally {
     await client.close();
   }
 }

 getExcelData().catch(console.error);

Run the above code once and your data will be sent to MongoDB. Once you have checked your database and everything is there, you can remove this code.

In the getExcelData async function we establish a connection with the remote database using client.connect which accesses our URI and then we also create the collection which is where our Excel data will be stored in database.collection('excel data'); and then we use the insertMany() function to insert the contents from our JSON file to our remote MongoDB Database.

Send Excel Sheet to MongoDB Atlas

Introduction

Problem Statement

Solution