Convert to date MongoDB via mongoimport

Question

I have downloaded huge chunks of data in the format in csv. I am using mongoimport to enter the data into MongoDB for processing. How do I get the date into date format recognized by MongoDB?

sample data with header

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

Solution 1

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

But this is achievable by importing the data and then running the following script (note that there is no point of all this hastle with a monthes as in Neil's Lunn script, because mongo can properly 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 timezone is so important (I assume that it is not, if there are only dates without time information), you can just change timezone on your local machine and then run the query. (Thanks to Neil Lunn for clarification regarding this)

Solution 2

As of Mongo version 3.4, you can use --columnsHaveTypes option to specify the type of your field while using mongoimport to import your data. here is the link for reference.

Sample mongoimport syntax below:

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

Solution 3

You basically have three options here as though you can import CSV directly using mongoimport, it has no idea how to convert dates from this format.

1. Convert your CSV input to JSON format by whatever means. For your date values you can use the extended JSON syntax form that will be recognized by the tool. The resulting JSON you produce can then be passed to mongoimport.

2. Write your own program to import the data by reading your CSV input and doing the correct conversions.

3. Import the CSV content as is, and then manipulate the data directly in your MongoDB collection using your language of choice.

One take on the third option would be to loop 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 that would make sure your dates are then converted to the correct BSON date format with the same matching date values you are expected.

Beware of "local" timezone conversions, you will want to be storing as UTC time.