importing CSV file MongoDB with ISODate

Question

When I export my data from mongoDB I obtain the following file:

Everything is a string in the mongoDB except for the date that is ISODate.

[email protected],sha1:64000:18:BTJnM903gIt5FNlSsZIRx1tLC9ErPJuB:9YVs800sgRPr1aaLj73qqnJ6,123,123,[email protected],2017-04-28T09:20:07.480Z,cus_AYcVXIUf68nT52

If I import this file into MongoDB it import each value as String value. I need to parse the date as Date format, the rest can be string.

I've seen that there's an argument for MongoImport --columnsHaveTypes. I've tryed it without any result:

mongoimport -u test-p test --authenticationDatabase test -h localhost:30158 --db test--collection users --type csv --file users.csv --upsert --upsertFields username --fields username.string\(\),password.string\(\),cname.string\(\),sname.string\(\),mail.string\(\),creation.date\(\),validation.auto\(\),clients.string\(\),customer.string\(\) --columnsHaveTypes

I get this error:

Failed: type coercion failure in document #0 for column 'creation', could not parse token '2017-04-28T09:20:07.480Z' to type date

What I could do?

Kind regards.

Plexiglas

Answer 1

Summary: you need to provide the format in which the date will be presented.

From the mongoimport documentation on the columnsHaveTypes parameter, you can't just say created.date\(\) - you need to provide an argument, which is a template for the way the date is represented in the CSV. Apparently the way you do this is, in accordance with the Go Language time.Parse function, by rendering a particular reference date in the format of your choice.

I think the reference date should be formatted like this:

2006-01-02T15:04:05.000Z

So you need to change your mongoimport call to specify the date with the format template, like this:

creation.date\(2006-01-02T15:04:05.000Z\)

Duenas

For inattentive readers like me: the date has to be January 2nd, 2006, as shown above. It can't be the first date in your file or any other date. – Osuna

dates in my csv file look like 2018-01-08T00:00:00.000Z the corresponding field in mongoimport defined as Gmttime.date\(2006-01-02T15:04:05.000Z\) Still getting Failed: type coercion failure in document #0 for column 'Gmttime', could not parse token 'Gmttime' to type date – Gaynell