how to push a csv data to mongodb using python

Question

Trying to push csv data in to mongodb using python.i'm a beginner to python & mongodb..i used the following code

import csv
import json
import pandas as pd
import sys, getopt, pprint
from pymongo import MongoClient
#CSV to JSON Conversion
csvfile = open('C://test//final-current.csv', 'r')
jsonfile = open('C://test//6.json', 'a')
reader = csv.DictReader( csvfile )
header= [ "S.No", "Instrument Name", "Buy Price", "Buy Quantity", "Sell Price", "Sell Quantity", "Last Traded Price", "Total Traded Quantity", "Average Traded Price", "Open Price", "High Price", "Low Price", "Close Price", "V" ,"Time"]
#fieldnames=header
output=[]
for each in reader:
    row={}
    for field in header:
        row[field]=each[field]
    output.append(row)

json.dump(output, jsonfile, indent=None, sort_keys=False , encoding="UTF-8")
mongo_client=MongoClient() 
db=mongo_client.october_mug_talk
db.segment.drop()
data=pd.read_csv('C://test//6.json', error_bad_lines=0)
df = pd.DataFrame(data)
records = csv.DictReader(df)
db.segment.insert(records)

but the output is given in this format

/* 0 */
{
  "_id" : ObjectId("54891c4ffb2a0303b0d43134"),
  "[{\"AverageTradedPrice\":\"0\"" : "BuyPrice:\"349.75\""
}

/* 1 */
{
  "_id" : ObjectId("54891c4ffb2a0303b0d43135"),
  "[{\"AverageTradedPrice\":\"0\"" : "BuyQuantity:\"3000\""
}

/* 2 */
{
  "_id" : ObjectId("54891c4ffb2a0303b0d43136"),
  "[{\"AverageTradedPrice\":\"0\"" : "ClosePrice:\"350\""
}

/* 3 */
{
  "_id" : ObjectId("54891c4ffb2a0303b0d43137"),
  "[{\"AverageTradedPrice\":\"0\"" : "HighPrice:\"0\""
}

Actually i want the output to like for single id all the other fields should be showed as subtypes eg:

 _id" : ObjectId("54891c4ffb2a0303b0d43137")
    AveragetradedPrice :0
    HighPrice:0
    ClosePrice:350
    buyprice:350.75

Please help me Out.Thanks in advance

Solution 1

Thank you for the suggestion.This one is the corrected code:

import csv
import json
import pandas as pd
import sys, getopt, pprint
from pymongo import MongoClient
#CSV to JSON Conversion
csvfile = open('C://test//final-current.csv', 'r')
reader = csv.DictReader( csvfile )
mongo_client=MongoClient() 
db=mongo_client.october_mug_talk
db.segment.drop()
header= [ "S No", "Instrument Name", "Buy Price", "Buy Quantity", "Sell Price", "Sell Quantity", "Last Traded Price", "Total Traded Quantity", "Average Traded Price", "Open Price", "High Price", "Low Price", "Close Price", "V" ,"Time"]

for each in reader:
    row={}
    for field in header:
        row[field]=each[field]

    db.segment.insert(row)

Solution 2

Why do you insert data one by one? Take a look at this one.

import pandas as pd
from pymongo import MongoClient

client = MongoClient(<your_credentials>)
database = client['YOUR_DB_NAME']
collection = database['your_collection']

def csv_to_json(filename, header=None):
    data = pd.read_csv(filename, header=header)
    return data.to_dict('records')

collection.insert_many(csv_to_json('your_file_path'))

Please be aware of that it might crash your app when the file is too big.

Solution 3

The easiest way is by using pandas my code is

import json
import pymongo
import pandas as pd
myclient = pymongo.MongoClient()

df = pd.read_csv('yourcsv.csv',encoding = 'ISO-8859-1')   # loading csv file
df.to_json('yourjson.json')                               # saving to json file
jdf = open('yourjson.json').read()                        # loading the json file 
data = json.loads(jdf)                                    # reading json file 

now you can insert this json in your mangodb database :-]

Comments

[anhlc]

output.append(row) => db.segment.insert(row)

[Viswanathan]

but if i'm pushing directly to mongodb ,it produces InvalidDocument: key 'S.No' must not contain '.'

[anhlc]

Make header as a dict to map s.no as s_no so it will be accetable as json key

[Markus W Mahlberg]

Is there a special reason not to use mongoimport?

[Viswanathan]

Finally i got it done.Thank you

[mLstudent33]

er, showing that part you leave at is what the question asks.