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

mmfall commented Mar 15, 2016

You just simplified my job, thanks for sharing!

@AbhishekAmin
Copy link

AbhishekAmin commented Oct 14, 2017

Thank you for sharing!

@4emkay
Copy link

4emkay commented Nov 23, 2017

Thanks For Sharing

@louspringer
Copy link

louspringer commented Nov 28, 2017

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

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

shreyanshu7101904 commented Apr 22, 2018

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

monica-t-james commented Aug 18, 2018

Thank you! This helped tons!

@afr-dt
Copy link

afr-dt commented Sep 11, 2018

Thanks for sharing 🎉 😃

@axbarrera
Copy link

axbarrera commented Oct 13, 2018

Thank you, this was very helpful!

@akamath03
Copy link

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

RileyMShea commented Mar 24, 2019

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

@JiGGie145
Copy link

JiGGie145 commented Apr 8, 2019

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

@paulmckenna
Copy link

paulmckenna commented Jul 6, 2019

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

@Stephy77
Copy link

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

ItsCosmas commented Aug 8, 2019

awesome, thanks

@Tim-CYLiao
Copy link

Tim-CYLiao commented Aug 31, 2019

Thanks for your help !

@seven-media-group
Copy link

seven-media-group commented Jan 15, 2020

Capture

why is this happening Undefined variable 'os' ?

@ItsCosmas
Copy link

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

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

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

nathaliadv commented Jun 25, 2020

image

@0xdeepmehta
Copy link

0xdeepmehta 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
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

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