Using --columnsHaveTypes to import data from csv files into Mongodb specifying a specific field as date

[Kaushik Bose]

I am trying to import a CSV file with one specific column being of type date-time using mongoimport utility

As of MongoDB version 3.4 we can use --columnsHaveTypes which instructs mongoimport that the field list specified in --headerline are of specific type.

My CSV file look like this

TIME,TPS
2006-01-02 07:25:24,1
2006-01-02 07:25:25,2
2006-01-02 07:25:26,2
2006-01-02 07:25:27,2
2006-01-02 07:25:28,2
2006-01-02 07:25:29,2
2006-01-02 07:25:30,1
2006-01-02 07:25:31,3

commands the i have executed but have error-ed out

 >>mongoimport --db test1 --collection tpsformat --type csv --file C:\ARMS\TestTPS.csv --headerline TIME.date\(2006-01-02 15:04:05\),TPS.string\(\) --columnsHaveTypes --maintainInsertionOrder

2017-08-22T16:36:52.114+0530    error validating settings: only one positional argument is allowed
2017-08-22T16:36:52.119+0530    try 'mongoimport --help' for more information

---

>>mongoimport --db test1 --collection tpsformat --type csv --file C:\ARMS\TestTPS.csv --headerline TIME.string\(\),TPS.string\(\) --columnsHaveTypes --maintainInsertionOrder

2017-08-22T16:36:59.539+0530    error validating settings: incompatible options: --file and positional argument(s)
2017-08-22T16:36:59.543+0530    try 'mongoimport --help' for more information

---

However a simple import without --columnsHaveTypes executes fine

>>mongoimport --db test1 --collection tpsformat --type csv --file C:\ARMS\TestTPS.csv --headerline --maintainInsertionOrder

2017-08-22T16:37:07.928+0530    connected to: localhost
2017-08-22T16:37:07.936+0530    imported 8 documents

Can someone suggest what I am doing wrong here.

Note: Have also tried removing the first line of CSV and the tried importing using --fields, however got exactly same error.

Thanks

[Gabriel Russell]

Kaushik,

I suspect that the trouble you're having is the result of some combination of command line quoting and escaping problems. You need to, at least, quote the command line argument parameters that contain spaces, which I see you are not. Additionally, I don't know if you're escaping correctly.

It appears that you're on windows, and I'm not personally experienced with solving quoting and escaping command lines on any of the windows command shells. Can you try to remove all of the uses of '\' as escapes, and instead just wrap all of the arguments with quotes?

Thank you,

Gabriel

[Kaushik Bose]

As suggested removing \ and instead using " tried multiple options but no positive results got same errors

>>mongoimport --db test1 --collection tpsformat --type csv --file "C:\ARMS\TestTPS.csv" --headerline TIME.date_go"(2006-01-02 15:04:05)", TPS.string"()" --columnsHaveTypes --maintainInsertionOrder

2017-08-23T11:03:23.009+0530    error validating settings: only one positional argument is allowed

2017-08-23T11:03:23.014+0530    try 'mongoimport --help' for more information

>>mongoimport --db test1 --collection tpsformat --type csv --file C:\ARMS\TestTPS.csv --headerline TIME.date_go"(2006-01-02 15:04:05)", TPS.string"()" --columnsHaveTypes --maintainInsertionOrder

2017-08-23T11:03:47.120+0530    error validating settings: only one positional argument is allowed

2017-08-23T11:03:47.124+0530    try 'mongoimport --help' for more information

>>mongoimport --db test1 --collection tpsformat --type csv --file C:\ARMS\TestTPS.csv --headerline "TIME.date_go(2006-01-02 15:04:05), TPS.string()" --columnsHaveTypes --maintainInsertionOrder

2017-08-23T11:04:24.382+0530    error validating settings: incompatible options: --file and positional argument(s)

2017-08-23T11:04:24.388+0530    try 'mongoimport --help' for more information

>>mongoimport --db test1 --collection tpsformat --type csv --file C:\ARMS\TestTPS.csv --headerline "TIME.date_go(2006-01-02 15:04:05)","TPS.string()" --columnsHaveTypes --maintainInsertionOrder

2017-08-23T11:04:46.183+0530    error validating settings: incompatible options: --file and positional argument(s)

2017-08-23T11:04:46.187+0530    try 'mongoimport --help' for more information

[Gabriel Russell]

Kaushik,

You need to quote the option arguments completely, not just some subsections. The argument to --headerline needs to be quoted, not contain quotes.

Additionally, I think you're initial attempt to escape special characters was wrong. I don't think you can escape characters using the \ character.

I found some documentation on how to escape or quote command lines on windows that may be helpful to you: https://ss64.com/nt/syntax-esc.html

That seams to imply that the only way to pass arguments with spaces is them is to use quotes. It also says that commas ',' are option and argument separators, which may explain why your last attempt where you quoted everything but the comma didn't work.

Thank you,

Gabriel Russell