A bit of help with mongoimport and /columnshavetypes

[Jose Romero Rodriguez]

Hello, i've been trying to use mongoimport and the /columnshavetypes option but i cannot make it work, i am getting stuck on date column type. below you can find one command example and the csv example.

mongoimport --db=Monitoring --collection=CPU --type=csv --columnsHaveTypes --fields="Servername.string(),CPU.int32(),Date.date(10-24-2010 22:55:33)" --file="C:\Users\whateveruser\Desktop\Powershell\Data\Get-CPU_Usage.csv"

server1,10,01/30/2020 13:38:28
server2,2,01/30/2020 13:38:28
server1,37,01/30/2020 13:39:15
server2,2,01/30/2020 13:39:15

could someone lend me hand with this?? i getting crazy with this.

THANKS

[Prasad Saya]

Hello Jose,

I believe that only some formats are compatible with the date fields of mongoimport. For example, the following date format worked

2020-01-09

using the import command:

mongoimport -d=test -c=dates --type=csv --columnsHaveTypes --fields="mydate.date(2006-01-31)" --file="test.csv"

Also, the following date/time data imports fine using the appropriate date format:

2006-01-02T15:04:05  formatted as  --fields="qdate.date(2006-01-31T15:15:15)"

2006-01-31 06:04:05  formatted as  --fields="qdate.date(2006-01-31 15:15:15)"

You can refer the following post for additional details: Error import a date (DD-MM-YYY) from CSV file to mongoDB with mongo import 

- Prasad.

[Kevin Adistambha]

Hi Jose,

The Date.date() in mongoimport follows Golang’s date formatting scheme, which is quite different from the typical strftime format that is used in Unix. There are numerous tutorials regarding this unique format, and how to interpret them.

For your specific case, I gathered that the date format should be Date.date(01/02/2006 15:04:05), so the full command line is (I renamed everything as test for testing purposes):

mongoimport --db test --collection test --type csv --columnsHaveTypes --fields="Servername.string(),CPU.int32(),Date.date(01/02/2006 15:04:05)" test.csv

The result of the import:

> db.test.find()
{ "_id" : ObjectId("5e38ee0d5892fa7d14952cbc"), "Servername" : "server1", "CPU" : 10, "Date" : ISODate("2020-01-30T13:38:28Z") }
{ "_id" : ObjectId("5e38ee0d5892fa7d14952cbe"), "Servername" : "server2", "CPU" : 2, "Date" : ISODate("2020-01-30T13:38:28Z") }
{ "_id" : ObjectId("5e38ee0d5892fa7d14952cbb"), "Servername" : "server1", "CPU" : 37, "Date" : ISODate("2020-01-30T13:39:15Z") }
{ "_id" : ObjectId("5e38ee0d5892fa7d14952cbd"), "Servername" : "server2", "CPU" : 2, "Date" : ISODate("2020-01-30T13:39:15Z") }

Best regards,
Kevin

​

[Prasad Saya]

Hello Jose,

I have additional information and using the Microsoft SQLServer format you can do the date formatting as follows. I used your CSV data as it is and the date formatting is specified using the syntax Date.date_ms(MM/dd/yyyy H:mm:ss). I think this is convenient, where you can specify the days, months, year, hours, minutes and seconds in terms of format strings dd, MM, yyyy, H, mm and ss.

In the mongoimport documentation the --columnsHaveTypes specifies different ways of date formatting, including the date(<args>), date_go(<args>), date_ms(<args>) and date_oracle(<args>). This example is using the date_ms(<>).

The import command:

mongoimport -d=test -c=dates --type=csv --columnsHaveTypes --fields="Servername.string(),CPU.int32(),Date.date_ms(MM/dd/yyyy H:mm:ss)" --file="test.csv"

The result documents in the dates collection:

{ "_id" : ObjectId("5e3948049f019a87e2d66273"), "Servername" : "server2", "CPU" : 2, "Date" : ISODate("2020-01-30T13:38:28Z") }

{ "_id" : ObjectId("5e3948049f019a87e2d66274"), "Servername" : "server1", "CPU" : 37, "Date" : ISODate("2020-01-30T13:39:15Z") }

{ "_id" : ObjectId("5e3948049f019a87e2d66275"), "Servername" : "server2", "CPU" : 2, "Date" : ISODate("2020-01-30T13:39:15Z") }

{ "_id" : ObjectId("5e3948049f019a87e2d66276"), "Servername" : "server1", "CPU" : 10, "Date" : ISODate("2020-01-30T13:38:28Z") }

Hope this is helpful.

- Prasad.

On Friday, January 31, 2020 at 6:18:16 AM UTC+5:30, Jose Romero Rodriguez wrote: