I'm trying to import Excel to Mongodb from Meteor, How can I get every _id to my documents?

Question

For each document in MongoDB should have _id, so I'm trying to import Excel to my MongoDB from Meteor. But Everything has only one _id, the result looks beautiful but it's not as I expect, please help me to solve this.

My code:

import { Template } from 'meteor/templating';
import { ReactiveVar } from 'meteor/reactive-var';
import { Excels } from '../imports/api/excels.js';

import './main.html';

const XLSX = require('xlsx');

Template.read.events({
    'change input' (evt, instance) {
        /* "Browser file upload form element" from SheetJS README */
        const file = evt.currentTarget.files[0];
        const reader = new FileReader();
        reader.onload = function(e) {
            const data = e.target.result;
            const name = file.name;
            /* Meteor magic */
            Meteor.call('upload', data, name, function(err, wb) {
                if(err) console.error(err);
                else {
                    /* do something here -- this just dumps an array of arrays to console */
                    console.log(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {header:1}));
                    document.getElementById('out').innerHTML = JSON.stringify(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]),2,2);
                    Excels.insert({ "spreadsheet": XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]) });
                }
            });
        };
        reader.readAsBinaryString(file);
    },
});

And the result:

meteor:PRIMARY> db.excels.find()
{ "_id" : "Pc3nXFpGuTdwe4psf", "spreadsheet" : [ { *** All information here ***

Expected result:

meteor:PRIMARY> db.excels.find()
{ 
   {
     "_id" : "Pc3nXFpGuTdwe4psf", "first" : "first" 
   },
   {
     "_id" : "Pc3nXFpGuTdwe4psg", "second" : "second" 
   },
.....

server/main.js

import { Meteor } from 'meteor/meteor';
import '../imports/api/excels.js';

const XLSX = require('xlsx');

Meteor.methods({
        upload: (bstr, name) => {
                /* read the data and return the workbook object to the frontend */
                return XLSX.read(bstr, {type:'binary'});
        },
        download: () => {
                /* generate a workbook object and return to the frontend */
                const data = [
                        ["a", "b", "c"],
                        [ 1 ,  2 ,  3 ]
                ];
                const ws = XLSX.utils.aoa_to_sheet(data);
                const wb = {SheetNames: ["Sheet1"], Sheets:{Sheet1:ws }};
                return wb;
        }
});

Meteor.startup(() => { });

imports/api/excels.js

import { Mongo } from 'meteor/mongo';

export const Excels = new Mongo.Collection('excels');

MongoDB collection:

{ "_id" : "BHvkSWWQFrJHcWYc2", "spreadsheet" : [ { "Line Number" : "1", "Item Name" : "MS425-32-HW", "Description" : "Meraki MS425-32 L3 Cld-Mngd 32x 10G SFP+ Switch", "Quantity" : "2", "Installed" : "2", "Spare" : "0" }, { "Line Number" : "2", "Item Name" : "MS225-48-HW", "Description" : "Meraki MS225-48 L2 Stck Cld-Mngd 48x GigE Switch", "Quantity" : "2", "Installed" : "2", "Spare" : "0" }, { "Line Number" : "3", "Item Name" : "MS225-48FP-HW", "Description" : "Meraki MS225-48FP L2 Stck Cld-Mngd 48x GigE 740W PoE Switch", "Quantity" : "14", "Installed" : "12", "Spare" : "2" }, { "Line Number" : "4", "Item Name" : "MR72-HW", "Description" : "Meraki MR72 Cloud Managed AP", "Quantity" : "14", "Installed" : "13", "Spare" : "1" }, { "Line Number" : "4.1", "Item Name" : "MA-ANT-20", "Description" : "Meraki Dual Band Omni Antennas", "Quantity" : "56", "Installed" : "52", "Spare" : "4" }, { "Line Number" : "5", "Item Name" : "MR33-HW", "Description" : "Meraki MR33 Cloud Managed AP", "Quantity" : "20", "Installed" : "18", "Spare" : "2" }, { "Line Number" : "6", "Item Name" : "MX400-HW", "Description" : "Meraki MX400 Cloud Managed Security Appliance", "Quantity" : "2", "Installed" : "2", "Spare" : "0" }, { "Line Number" : "7", "Item Name" : "N3K-C3524P-10GX", "Description" : "Nexus 3524x, 24 10G Ports", "Quantity" : "2", "Installed" : "2", "Spare" : "0" }, { "Line Number" : "8", "Item Name" : "MA-SFP-10GB-LRM", "Description" : "Meraki 10G Base LRM", "Quantity" : "56", "Installed" : "48", "Spare" : "8" }, { "Line Number" : "9", "Item Name" : "MA-SFP-10GB-SR", "Description" : "Meraki 10G Base SR Multi-Mode", "Quantity" : "16", "Installed" : "16", "Spare" : "0" }, { "Line Number" : "10", "Item Name" : "SFP-10G-SR=", "Description" : "10GBASE-SR SFP Module", "Quantity" : "8", "Installed" : "8", "Spare" : "0" } ] }

document.getElementById('out').innerHTML = JSON.stringify(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]),2,2);

    [
      {
        "Line Number": "1",
        "Item Name": "MS425-32-HW",
        "Description": "Meraki MS425-32 L3 Cld-Mngd 32x 10G SFP+ Switch",
        "Quantity": "2",
        "Installed": "2",
        "Spare": "0"
      },
      {
        "Line Number": "2",
        "Item Name": "MS225-48-HW",
        "Description": "Meraki MS225-48 L2 Stck Cld-Mngd 48x GigE Switch",
        "Quantity": "2",
        "Installed": "2",
        "Spare": "0"
      },
      {
        "Line Number": "3",
        "Item Name": "MS225-48FP-HW",
        "Description": "Meraki MS225-48FP L2 Stck Cld-Mngd 48x GigE 740W PoE Switch",
        "Quantity": "14",
        "Installed": "12",
        "Spare": "2"
      },
    ....
    ]

Hi Styx, thanks for your reply, I have edited the topic and add server side code. — sirisakc, Aug 20 '17 at 14:55
Could you also show result of `XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]])` call (on client)? — Styx, Aug 20 '17 at 15:22

Nelson Yeung · Accepted Answer · 2017-08-20T23:05:15.733

1

You are inserting the array into the collection with the key spreadsheet on this line:

Excels.insert({ "spreadsheet": XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]) });

This is why you see the key spreadsheet:

{ "_id" : "BHvkSWWQFrJHcWYc2", "spreadsheet" : ...

But I take it that you actually want to insert every element of the array as their own document, so that every row of the spreadsheet will produce their own ID. Normally you'd just remove the spreadsheet and insert the array as a whole:

Excels.insert(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]))

However, from [1, 2], it seems like that Meteor does not support inserting an array as the root document, therefore you must loop through each row and insert them individually:

XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]).forEach(r => Excels.insert(r));

edited Aug 20 '17 at 23:05

answered Aug 20 '17 at 16:09

Nelson Yeung

3,262
3
19
29

Hi Nelson, I think like you as it should be like this but it alert that wrong syntax. – sirisakc Aug 20 '17 at 22:37
@sirisakc Can you give me your exact error? Although I think I know what the problem is, but I'm surprised it's a syntax error. – Nelson Yeung Aug 20 '17 at 22:52
@sirisakc I have updated the answer with a solution that should work. – Nelson Yeung Aug 20 '17 at 23:06
Hi Nelson, there is an unexpected error as below. => Errors prevented startup: While processing files with ecmascript (for target web.browser): client/main.js:25:6: Unexpected token (25:6) => Your application has errors. Waiting for file change. – sirisakc Aug 21 '17 at 02:21
Hi Nelson, your solution is working. => Excels.insert(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]])) But all everything has 1 _id – sirisakc Aug 21 '17 at 02:28
@sirisakc As I've said "Meteor does not support inserting an array as the root document..." can you please try the bottom solution: `XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]).forEach(r => Excels.insert(r));` – Nelson Yeung Aug 21 '17 at 09:03
Brilliant !!!!!! Thank you very much, Nelson. Maybe I did something wrong before. Now It's working very well!!! – sirisakc Aug 22 '17 at 01:29
Hi Nelson, How can I convert to React and insert on server side. I have tried some solutions but cannot. – sirisakc Aug 22 '17 at 06:49
@sirisakc They have this brilliant tutorial here: https://www.meteor.com/tutorials/react/creating-an-app I myself uses it each time when I move stuff to react and server side code for smooth transition. Just go to "Security with methods" link for server side database code. – Nelson Yeung Aug 22 '17 at 07:47
Hi Nelson, thank you very much for your kindly help. – sirisakc Aug 22 '17 at 08:55

I'm trying to import Excel to Mongodb from Meteor, How can I get every _id to my documents?

1 Answers1