Skip to content

Instantly share code, notes, and snippets.

@mprajwala
Last active July 23, 2023 20:07
Show Gist options
  • Save mprajwala/849b5909f5b881c8ce6a to your computer and use it in GitHub Desktop.
Save mprajwala/849b5909f5b881c8ce6a to your computer and use it in GitHub Desktop.
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
Copy link

mmfall commented Mar 15, 2016

You just simplified my job, thanks for sharing!

@AbhishekAmin
Copy link

Thank you for sharing!

@4emkay
Copy link

4emkay commented Nov 23, 2017

Thanks For Sharing

@louspringer
Copy link

Thanks!

@jmsv
Copy link

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
Copy link

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
Copy link

Thanks for sharing

@adnanmc
Copy link

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
Copy link

Thank you! This helped tons!

@afr-dt
Copy link

afr-dt commented Sep 11, 2018

Thanks for sharing 🎉 😃

@axbarrera
Copy link

Thank you, this was very helpful!

@akamath03
Copy link

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
Copy link

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

@JiGGie145
Copy link

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

@paulmckenna
Copy link

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

@Stephy77
Copy link

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
Copy link

awesome, thanks

@Tim-CYLiao
Copy link

Thanks for your help !

@seven-media-group
Copy link

Capture

why is this happening Undefined variable 'os' ?

@ItsCosmas
Copy link

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
Copy link

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
Copy link

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
Copy link

Roalpifi commented Jun 9, 2020

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

@ItsCosmas
Copy link

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

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

@nathaliadv
Copy link

image

@0xdeepmehta
Copy link

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
Copy link

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
Copy link

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