Importing XLSX and CSV file contents in cloud firestore using Reactjs

Hello all, I am going to write about import XLSX or CSV files to firestore. Please smile! I won’t only show how it works but I will give the source code and demo link in this article. 

Let’s get started, Before begin it would be nice if you are familiar with the following terms.

Let’s create a react project using the create-react-app by using the following command.

npx create-react-app 
import-demo
cd import-demo 
npm start

Well done, now you have new react application running in port 3000. Now it’s time to install other npm dependencies. Those dependencies are following with the install command and their purpose.

  1. React Bootstrap
    This package is required so the we can use reusable components in bootstrap css. For this run the following: 
    npm install react-bootstrap bootstrap
  2. Firebase
    This package is useful to use the firebase in our react application and can be installed by following command.
    npm i firebase
  3. XLSX
    This package is useful to read the xlsx or csv file so that we can insert data to cloud firestore. 
    npm i xlsx

After all the dependencies installation, it’s time to create a project in cloud firestore. If you are not logged in for the firebase then start a sign up and go to the console. There you would find the add project button click to it and you can now create a new project. Which is something like this

https://cdn-images-1.medium.com/max/800/1*_RTnkKL9h6hzuwYuUx-mlA.png
CloudStore

Continue to it and you can create a project from there. After creating a project you can see the something like this page.

https://cdn-images-1.medium.com/max/800/1*J7KoLX31IGtqbh5TggQqSA.png

Just after a Project Overview button there is a setting icon click there and click project settings. And then there will appear the another page where you can create your apps.

https://cdn-images-1.medium.com/max/800/1*bRJJhceR1X1u-WvkGxHGXw.png

Just choose the third button which is for web application. Just give the name of the application and click register app then application will be created.

https://cdn-images-1.medium.com/max/800/1*81JfkzXK9HWRZkeCUkHuDQ.png

After creating app then you will be able to get the Firebase SDK snippet config. Which is looks something like this. 

const firebaseConfig = {
   apiKey: "XXXXXXXX",
   authDomain: "XXXXXXXX",
   projectId: "XXXXXXXX",
   storageBucket: "XXXXXXXX",
   messagingSenderId: "XXXXXXXX",
   appId: "XXXXXXXX",
   measurementId: "XXXXXXXX"
 }

Uff we done most of the parts! We are close to our workable XLSX or CSV import application. Let’s create a .env file inside root folder and then add the firebaseConfig related details in a variables. 

 REACT_APP_FIREBASE_API_KEY = "XXXXXXXX"
 REACT_APP_FIREBASE_AUTH_DOMAIN = "XXXXXXXX"
 REACT_APP_FIREBASE_DATABASE_URL = "XXXXXXXX"
 REACT_APP_FIREBASE_PROJECT_ID = "XXXXXXXX"
 REACT_APP_FIREBASE_STORAGE_BUCKET = "XXXXXXXX"
 REACT_APP_FIREBASE_MESSAGING_SENDER_ID = "XXXXXXXX"
 REACT_APP_FIREBASE_APP_ID = "XXXXXXXX"

Now, inside the src folder let’s create the firebase.js file and add the following contents.

import firebase from 'firebase'
 const firebaseConfig = {
   apiKey: process.env.REACT_APP_FIREBASE_API_KEY,
   authDomain: process.env.REACT_APP_FIREBASE_AUTH_DOMAIN,
   databaseURL: process.env.REACT_APP_FIREBASE_DATABASE_URL,
   projectId: process.env.REACT_APP_FIREBASE_PROJECT_ID,
   storageBucket: process.env.REACT_APP_FIREBASE_STORAGE_BUCKET,
   messagingSenderId: process.env.REACT_APP_FIREBASE_MESSAGING_SENDER_ID,
   appId: process.env.REACT_APP_FIREBASE_APP_ID
 };
 // Initialize Firebase
 firebase.initializeApp(firebaseConfig);
 const db=firebase.firestore();
 export default db;

I am sure you’re late now so just to make it easier and quick I am prepared all the code is the App.js file. simply you can replace the content of App.js with the following

import { useEffect, useState } from "react";
 import {
   Navbar,
   Nav,
   Form,
   Button,
   Table,
   Container,
   Modal,
 } from "react-bootstrap";
 import "./App.css";
 import * as XLSX from "xlsx";
 import db from "./firebase";
 function App() {
   const [users, setUsers] = useState([]);
   const [show, setShow] = useState(false);
   const [headers, setHeaders] = useState([]);
   const [rows, setRows] = useState([]);
 useEffect(() => {
     fetchUsers();
   }, []);
 const handleClose = () => {
     resetModal();
   };
   const handleShow = () => setShow(true);
 const processSheetData = (excelData) => {
     const wsname = excelData.SheetNames[0];
     const ws = excelData.Sheets[wsname];
 const dataParse = XLSX.utils.sheet_to_json(ws, { header: 1 }); let csv = []; let headers = []; for (let i = 0; i < dataParse.length; i++) {   if (dataParse[i] === "") continue;   let fields = dataParse[i];   if (i === 0) {     headers = fields;   } else {     let csvRow = [];     for (let field of fields) {       if (!isNaN(field)) field = Number(field);       csvRow.push(field);     }     csv.push(csvRow);   } } setHeaders(headers); setRows(csv);
 };
 const handleFileUpload = (event) => {
     let file = event.target.files[0];
     const reader = new FileReader();
     reader.onload = function (e) {
       const data = e.target.result;
       let readedData = XLSX.read(data, { type: "binary" });
   processSheetData(readedData); }; reader.readAsBinaryString(file);
 };
 const resetModal = () => {
     setShow(false);
     setHeaders([]);
     setRows([]);
   };
 const handleSubmit = () => {
     let batch = db.batch();
 const formattedValues = rows.map((row) => {   return {     name: row[0],     age: row[1],   }; }); formattedValues.forEach((userRow) => {   batch.set(db.collection("users").doc(), userRow); }); batch.commit().then(function () {   fetchUsers();   resetModal(); });
 };
 const fetchUsers = () => {
     db.collection("users")
       .get()
       .then((querySnapshot) => {
         const data = querySnapshot.docs.map((doc) => doc.data());
         setUsers(data);
       });
   };
 return (
     <>
                                        Firebase Firestore Import Demo           
                                   

                            Import             
                           
                                        Import a CSV or XLSX           
                        {headers.length < 1 || rows.length < 1 ? (                                 handleFileUpload(event)}                 />               
             ) : (                                    {headers.map((item, index) => (                                            ))}                                      {rows.length &&                     rows.map((itemArray, itemIndex) => (                                                {itemArray.map((item, index) => (                                                    ))}                                            ))}                                                                          #
{item}                 
                                          {itemIndex + 1}
{item}                            )}           
                                       Close             
                            Save Changes                                 
                      {users.length < 1 && (                            )}             {users &&               users.map((user, index) => (                                ))}                                                  #
               Name
               Age                        
                            No users found.               
                   {index + 1}
                   {user.name}
                   {user.age}                                 

     
   );
 }
 export default App;

Isn’t it nice? Now you can create a xlsx file with the something like this content.

https://cdn-images-1.medium.com/max/800/1*gdcEB-lO1im8N2Nkww6OMQ.png

Run your application using npm start, And you can simply import that file and records of file will be stored in cloud firestore. I know you are really want to see how our page design look alike so I am sharing here.

https://cdn-images-1.medium.com/max/800/1*y6RlcoW1CAmJ9bgegU_-LQ.png
https://cdn-images-1.medium.com/max/800/1*iCVpuc5sWu6S5EYG_DHspw.png

Well, that’s all about it, you can see the our source code in github and live demo here. 

Thanks for reading this article if you have any queries please let me know from the comment section below. : Authored By: Madhu Sudan Subedi

Leave a Reply

Your email address will not be published. Required fields are marked *