Convert to date MongoDB via mongoimport

Question

I downloaded huge chunks of data in csv format. I use mongoimport to enter data into MongoDB for processing. How to get date to date format recognized by MongoDB?

header data examples

Date, Open Price, High Price, Low Price, Last Traded Price , Close Price, Total Traded Quantity, Turnover (in Lakhs) 04-Apr-2014,901,912,889.5,896.75,892.85,207149,1867.08 03-Apr-2014,908,918,897.65,900,900.75,156260,1419.9 02-Apr-2014,916,921.85,898,900.7,900.75,175990,1591.97 

Answer 1

As far as I know, there is no way to do this using mongoimport .

But this can be achieved by importing the data and then running the following script (note that there is no point to all of this hastle with monthes, as in the Neil Lunn script, because mongo can correctly convert your date by doing this new Date('04-Apr-2014') ):

 db.collName.find().forEach(function(el){ el.dateField = new Date(el.dateField); db.collName.save(el) }); 

PS If the time zone is so important (I assume that it is not, if there are only dates without time information), you can simply change the time zone on your local machine, and then run the query. (Thanks to Neil Lunn for clarifying this)

Answer 2

Basically you have three options, as if you could import CSV directly using mongoimport, it has no idea how to convert dates from this format.

• Convert CSV input to JSON format by any means. For your date values, you can use the extended JSON syntax that the tool will recognize. The resulting JSON that you produce can then be passed to mongoimport.

• Write your own data import program by reading your CSV input and making the right conversions.

• Import the CSV content as is, and then process the data directly in your MongoDB collection using your choice language.

One of three options is to copy the results and update the dates accordingly:

 var months = [ "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" ]; db.collection.find({ },{ "Date": 1 }).forEach(function(doc){ var splitDate = doc.Date.split("-"); var mval = months.indexOf( splitDate[1] ); mval = ( mval < 10 ) ? "0" + mval : mval var newDate = new Date( splitDate[2] + "-" + mval + "-" + splitDate[0] ); db.collection.update( { _id: doc._id }, { "$set": { "Date": newDate } } ); }) 

And this will make sure that your dates are then converted to the correct BSON date format with the same corresponding date values ​​that you expect.

Beware of “local” time zone conversions, you will want to store them as UTC time.

Answer 3

Starting with Mongo 3.4, you can use - columnsHaveTypes to indicate the type of your field using mongoimport to import your data. here is the link for the link.

The mongoimport syntax example below:

 mongoimport --db XYZ --collection abc --type tsv --fields id.int32(),client_name.string(),app_name.auto(),date.date() --columnsHaveTypes --file "abc.tsv" --verbose