13

When importing data from file (csv in my case) mongoimport automatically choose data type for each field.

Is it possible to choose data type manually for specific field? I encountered situation, when in my file there are phone numbers, which I want and which I should treat as strings, but mongoimport (quite properly) treat those phone numbers as a numbers (NumberLong).

Jarema
  • 3,291
  • 2
  • 17
  • 30

6 Answers6

15

When importing CSV/TSV to mongodb, the option --columnsHaveTypes can help to define the columnstypes. But the document seems very unclear. I tried several times until finally did succeed. You should add option --columnsHaveTypes and change every column after --fields and remember using "\" before "(" and ")". for example, change:

mongoimport -h foohost -d bardb -c fooc --type tsv --fields col1,col2,col3 --file path/to/file.txt

into

mongoimport -h foohost -d bardb -c fooc --type tsv --fields col1.int32\(\),col2.double\(\),col3.string\(\) --columnsHaveTypes --file path/to/file.txt
Community
  • 1
  • 1
finetu
  • 163
  • 1
  • 5
  • To avoid the need to scape parenthesis, you can enclose the fields in double qoutes. This is a restriction of shell not the mongo import: mongoimport -h foohost -d bardb -c fooc --type tsv --fields "col1.int32(),col2.double(),col3.string()" --columnsHaveTypes --file path/to/file.txt – PyGuy May 25 '20 at 20:39
  • 1
    Is there anyway to do it using --fieldFile, I mean can we define these fields with their respective type into a file instead of passing inline argument in command line?? – Abdul Moiz May 29 '20 at 20:43
5

What you can do is import these data using CSV and then run the update statement on the existing data in mongo db to convert it into the format that you want.

Avneesh
  • 654
  • 4
  • 6
  • 1
    thanks. i tried: db.cdr.find({}, {dst: 1}).forEach(function(x) { x.dst = '' + x.dst; db.cdr.save(x)}) and it worked as expected. New String option didnt. It turned it into array. Is there more efficient way to do this? :) – Jarema Jun 14 '14 at 20:49
4

Now version 3.4 onward mongoimport supports specifying the field types explicitly while importing the data. See below link: https://docs.mongodb.com/manual/reference/program/mongoimport/#cmdoption--columnsHaveTypes

Ajinkya
  • 544
  • 4
  • 9
3

See the Type Fidelity section in the documentation:

mongoimport and mongoexport do not reliably preserve all rich BSON data types because JSON can only represent a subset of the types supported by BSON. As a result, data exported or imported with these tools may lose some measure of fidelity. See MongoDB Extended JSON for more information.

Use mongodump and mongorestore to preserve types.

Gergo Erdosi
  • 40,904
  • 21
  • 118
  • 94
  • i guess its impossible to mongorestore when i need to import data from postgres, or any other sql db. – Jarema Jun 14 '14 at 19:34
  • In that case I would do what @Avneesh suggests, import the data and run an update command. Probably that's the easiest. You can find examples here: http://stackoverflow.com/questions/4973095/mongodb-how-to-change-the-type-of-a-field – Gergo Erdosi Jun 14 '14 at 19:45
1

When I tried to import CSV into Mongo Atlas, I ran into a similar issue. Here's how I deal with it.

To avoid shell error you can enclose fields in double-quotes.

In the below example, I used two-column "Name, Barcode".You Can use whatever column you need also don't forget to update <connecttionString>,<collectionName>, <CSVpath> with your own values.

for more mongo types refer to mongoimport documentation.

mongoimport --uri <connecttionString> --collection <collectionName> --type csv --file <CSVpath> -f "Name.string(),Barcode.string()" --columnsHaveTypes
Asela Priyadarshana
  • 740
  • 5
  • 20
0

You can also choose to put the column types in a field file to make it easier. Just make sure you have specified all columns in your field file. In my case, I named it "field.txt".

In the field file, you write the columns with their types this way: <column>.<type>. To get the list of all types used in the mongoimport syntax, please visit https://www.mongodb.com/docs/database-tools/mongoimport/

field.txt

name.string()
usercode.int64()
city.string()
town.string()
address.string()
price.decimal()
date_created.date_go(2021-08-10 15:04:05)

You can choose to name it anything you want as long as you point the fieldFile to it. eg. fieldFile=myfieldname.txt

mongoimport --uri <connectionString> --collection <collectionName> --type csv --file <csv path> --columnsHaveTypes --fieldFile=field.txt --mode=insert

Manuel
  • 32
  • 1
  • 2