CODEWITHSUNDEEP
mongodbcsvmongoimport
Prashant Prabhakar Singh
Prashant Prabhakar Singh

Reputation: 1190

Append to mongodb from csv

I have a mongodb with collection having fields email. fName, lName. And I have a csv with :

email | contactNo | countryName
a1    | 001       | USA        
a2    | 002       | UAE

I want to merge csv Data with mongoDB data with email as primary key. The final mongo db should have fields like:

{
email:a1,
fName: tom,
lname: cruise,
contactNo: 001,
county : USA
}

Can I do this using mongoimport? Or how can I write a node/python script for same. Any link/refrence will be helpful.

Upvotes: 3

Views: 1446

Answers (1)

David Makogon
David Makogon

Reputation: 71055

Starting with v3.4, mongoimport supports the --merge flag. By default, it matches on _id, upserting any matches and inserting new id's. You can use the --upsertFields flag to specify an alternate field to match on (such as email in your case).

So, as an example: here is a simple collection, based on the data you have, which I created (I edited the formatting to make it fit better here):

db.contacts.find()
{
    "_id" : ObjectId("5aa019b79259d12040e7107b"),
    "fname" : "tom", "lname" : "cruise", "email" : "a1"
}
{
    "_id" : ObjectId("5aa019bb9259d12040e7107c"),
    "fname" : "bob", "lname" : "smith", "email" : "a2"
}
{
    "_id" : ObjectId("5aa019be9259d12040e7107d"),
    "fname" : "jane", "lname" : "jones", "email" : "a3"
}

And here is my csv file, for import:

email,contactNo,countryName
a1,1,USA
a2,2,UAE
a5,5,CAN

I now import with the following, specifying the email property as the property to key on (not _id):

mongoimport --mode merge --headerline --upsertFields email
 --db stackoverflow --collection contacts --file ~/import.csv --type csv

Once done, my data now looks like this:

db.contacts.find().pretty()
{
    "_id" : ObjectId("5aa019b79259d12040e7107b"),
    "fname" : "tom", "lname" : "cruise", "email" : "a1",
    "contactNo" : 1, "countryName" : "USA"
}
{
    "_id" : ObjectId("5aa019bb9259d12040e7107c"),
    "fname" : "bob", "lname" : "smith", "email" : "a2",
    "contactNo" : 2, "countryName" : "UAE"
}
{
    "_id" : ObjectId("5aa019be9259d12040e7107d"),
    "fname" : "jane", "lname" : "jones", "email" : "a3"
}
{
    "_id" : ObjectId("5aa019d17cd320319146756d"),
    "email" : "a5",
    "contactNo" : 5, "countryName" : "CAN"
}

Notice that:

  • the first two documents have augmented to include data from the csv
  • the third document is untouched, as there was no matching data in the csv
  • the fourth document is brand-new, only containing properties from the csv

Also note that the _id property had nothing do do with the merge, because I specified the --upsertFields option when importing.

Read here for more information.

Upvotes: 3

Related Questions