Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Store CSV data into mongodb using python pandas
#!/usr/bin/env python
import sys
import pandas as pd
import pymongo
import json
def import_content(filepath):
mng_client = pymongo.MongoClient('localhost', 27017)
mng_db = mng_client['mongodb_name'] // Replace mongo db name
collection_name = 'collection_name' // Replace mongo db collection name
db_cm = mng_db[collection_name]
cdir = os.path.dirname(__file__)
file_res = os.path.join(cdir, filepath)
data = pd.read_csv(file_res)
data_json = json.loads(data.to_json(orient='records'))
db_cm.remove()
db_cm.insert(data_json)
if __name__ == "__main__":
filepath = '/path/to/csv/path' // pass csv file path
import_content(filepath)
@mmfall

This comment has been minimized.

Copy link

@mmfall mmfall commented Mar 15, 2016

You just simplified my job, thanks for sharing!

@AbhishekAmin

This comment has been minimized.

Copy link

@AbhishekAmin AbhishekAmin commented Oct 14, 2017

Thank you for sharing!

@4emkay

This comment has been minimized.

Copy link

@4emkay 4emkay commented Nov 23, 2017

Thanks For Sharing

@louspringer

This comment has been minimized.

Copy link

@louspringer louspringer commented Nov 28, 2017

Thanks!

@jmsv

This comment has been minimized.

Copy link

@jmsv jmsv commented Dec 5, 2017

I'd just like to point out a few things:

  • import sys is never used, but import os is required
  • Comments in python use # syntax, not //
  • Indentation is not consistent

Other than that, very helpful to get started with. Thanks for sharing 😃

@gugwadsantosh

This comment has been minimized.

Copy link

@gugwadsantosh gugwadsantosh commented Dec 19, 2017

How to read excel sheet using python and i need to store excel values in MongoDB in the form of web page. Would you please give an Idea How to do that ? i am new to python so.

@shreyanshu7101904

This comment has been minimized.

Copy link

@shreyanshu7101904 shreyanshu7101904 commented Apr 22, 2018

Thanks for sharing

@adnanmc

This comment has been minimized.

Copy link

@adnanmc adnanmc commented Aug 1, 2018

Just an FYI. This solution converts csv values like "0029" to 29.0 float and "" into null. In case anyone is interested to force it all to string, replace line 17 with this:
data = pd.read_csv(file_res,dtype=str,na_filter=False)

@monica-t-james

This comment has been minimized.

Copy link

@monica-t-james monica-t-james commented Aug 18, 2018

Thank you! This helped tons!

@afr-dt

This comment has been minimized.

Copy link

@afr-dt afr-dt commented Sep 11, 2018

Thanks for sharing 🎉 😃

@axbarrera

This comment has been minimized.

Copy link

@axbarrera axbarrera commented Oct 13, 2018

Thank you, this was very helpful!

@akamath03

This comment has been minimized.

Copy link

@akamath03 akamath03 commented Oct 31, 2018

I need to store values of each column in a different variable (parsing). So that modifications on particular column values are possible before loading to the DB. How can this be done?

@RileyMShea

This comment has been minimized.

Copy link

@RileyMShea RileyMShea commented Mar 24, 2019

thank you for this example, was killing myself to do this seemingly basic thing

@JiGGie145

This comment has been minimized.

Copy link

@JiGGie145 JiGGie145 commented Apr 8, 2019

@akamath03 did you ever get the solution to your question? 'twould help me out a lot right now.

@paulmckenna

This comment has been minimized.

Copy link

@paulmckenna paulmckenna commented Jul 6, 2019

I am getting an error NameError: name 'file' is not defined - what should I be defining here?

@Stephy77

This comment has been minimized.

Copy link

@Stephy77 Stephy77 commented Jul 22, 2019

need of a code
where one excel file is created which is uploaded as follow:
Capture

result should be:
result

generate the code for this in python
please help friends

@ItsCosmas

This comment has been minimized.

Copy link

@ItsCosmas ItsCosmas commented Aug 8, 2019

awesome, thanks

@Tim-CYLiao

This comment has been minimized.

Copy link

@Tim-CYLiao Tim-CYLiao commented Aug 31, 2019

Thanks for your help !

@seven-media-group

This comment has been minimized.

Copy link

@seven-media-group seven-media-group commented Jan 15, 2020

Capture

why is this happening Undefined variable 'os' ?

@ItsCosmas

This comment has been minimized.

Copy link

@ItsCosmas ItsCosmas commented Jan 15, 2020

Capture

why is this happening Undefined variable 'os' ?

Don't forget to import the os module at the top of your script:
import os

@seven-media-group

This comment has been minimized.

Copy link

@seven-media-group seven-media-group commented Jan 15, 2020

Capture
why is this happening Undefined variable 'os' ?

Don't forget to import the os module at the top of your script:
import os

yes, got it
Thank You so much

@Ayushi1504

This comment has been minimized.

Copy link

@Ayushi1504 Ayushi1504 commented Feb 20, 2020

i need to map a csv which has an id field already to be imported to mongo with the _id as this id field.How to do that?
(rhis csv file has an id field)
try:
client = MongoClient(mongouri)
database = client['automation']
database.create_collection('testTableMongo'+str(suffix))
coll = database['testTableMongo'+str(suffix)]
data = pd.read_csv(os.path.abspath('../data/' + file))
payload = json.loads(data.to_json(orient='records'))
coll.remove()
coll.insert(payload)
except OperationFailure as msg:
print("Command skipped: %s", msg)

@Roalpifi

This comment has been minimized.

Copy link

@Roalpifi Roalpifi commented Jun 9, 2020

I'm with the error "name 'file' is not defined"

@ItsCosmas

This comment has been minimized.

Copy link

@ItsCosmas ItsCosmas commented Jun 9, 2020

I'm with the error "name 'file' is not defined"

Hey, kindly post a snippet of your code for easier help

@nathaliadv

This comment has been minimized.

Copy link

@nathaliadv nathaliadv commented Jun 25, 2020

image

@DeepMhta07

This comment has been minimized.

Copy link

@DeepMhta07 DeepMhta07 commented Mar 4, 2021

image

when you use file in python interactive mode, this error occur.
So you should write this line os.path.dirname(__file__) in file.py.

for more reference consider this : https://stackoverflow.com/questions/16771894/python-nameerror-global-name-file-is-not-defined

@dasroja

This comment has been minimized.

Copy link

@dasroja dasroja commented Mar 15, 2021

I need help to load data into mongo database with collection creation from worksheet which contain multiple excel sheets

@hakymulla

This comment has been minimized.

Copy link

@hakymulla hakymulla commented Mar 16, 2021

I need help to load data into mongo database with collection creation from worksheet which contain multiple excel sheets

you can read a sheet with pandas with the sheet_name att.
df = pd.read_excel('my_excel.xlsx', sheet_name='Sheet 1')

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment