Using mongoimport to import a CSV file is it possible to import NULLs?

Question

I'm trying to import from CSV into mongodb 3.4 using mongoimport, and I would like for empty columns to be imported as null values for the fields.

I was under the impression from the mongoimport documentation, that if --ignoreBlanks was not specified that I would get the behavior I wanted.

--ignoreBlanks

Ignores empty fields in csv and tsv exports. If not 
specified, mongoimport creates fields without values in imported
documents.

However, when I try to load this sample data without --ignoreblanks:

field_1.string(),field_2.int32(),field_3.string()
A,5,B
C,,D
E,7,F

then I get an error on any field that is not a string.

mongoimport --collection null_test --type csv --headerline --columnsHaveTypes --file null_test.csv --verbose 

2017-08-08T16:55:42.989+0000    filesize: 67 bytes
2017-08-08T16:55:42.989+0000    using fields: field_1,field_2,field_3
2017-08-08T16:55:43.001+0000    connected to: localhost:27017
2017-08-08T16:55:43.001+0000    ns: DEV.null_test
2017-08-08T16:55:43.001+0000    connected to node type: standalone
2017-08-08T16:55:43.001+0000    using write concern: w='1', j=false, fsync=false, wtimeout=0
2017-08-08T16:55:43.001+0000    using write concern: w='1', j=false, fsync=false, wtimeout=0
2017-08-08T16:55:43.001+0000    Failed: type coercion failure in document #1 for column 'field_2', could not parse token '' to type int32
2017-08-08T16:55:43.001+0000    imported 0 documents

For fields that are strings it loads an empty string rather than a null.

What am I doing wrong here? Is it possible to load fields as NULL using mongoimport with CSV or TSV files?

For what it's worth, if I use mongoimport to import a json file with NULLs, it imports them just fine as actual NULLs.

[
    {
        "field1": "A",
        "field2": null,
        "field3": "C"
    },
    {
        "field1": 1,
        "field2": 5,
        "field3": null
    }
]

Athanasian

Answer 1

MongoDB will never import null values from CSV data.

I'm guessing that's because it doesn't make too much sense given that querying for "field": null will return all documents where "field" is missing or null.

The -ignoreBlanks option will simply prevent the import from creating empty string ("") values for missing fields which would otherwise be the default.

You can get what you want, though, by post-processing your imported documents using the following update:

collection.update({'field_2': {$exists: false}}, {$set: {'field_2': null}})

Stafani

"MongoDB will never import null values for you." But this is not true. If I use mongoimport to import json rather than csv or tsv it definitely imports nulls. – Athanasian

Nice catch, I rephrased my answer a little. Ok like that? – Stafani

Better, but I wish it was an answer I wanted to hear :) . The part that makes me hesitate is that the docs for --ignoreblanks says "If not specified, mongoimport creates fields without values in imported documents." Perhaps this is my RDB background showing through, but what is a "field without value" if not a null? If it was supposed to not create the field at all, why would it use this wording? – Athanasian

And, I will discuss with my dev in more detail on the necessity of having nulls in the first place. – Athanasian

I tend to agree - the importers should somehow work identically. Plus, when you export data with null values to a file using mongoexport you get the structure you described. So when reading it in again, you'd want to get the same result. But then again... How? There would need to be some magic way to differentiate between <missing> and null which doesn't really exist in CSV. Also, I agree that the flag you mentioned a) indicates some different funtionality as per its documentation and b) importing empty strings for missing values doesn't seem to make an awful lot of sense in the first place. – Stafani

@Athanasian Can you help me with how to import null values from CSV to Mongodb? I have used Mongo Compass and the fields containing Null are considered as string and are imported with value "Null" in them. – Narvaez

@Athanasian Okay, I missed 'JSON'. I tried with importing JSON and it worked. Thanks. – Narvaez