Import CSV Data

MongoDB Atlas App Services & Realm
1

I am attempting to import a CSV into an existing class but it fails to read one of the fields, which contains postcode data such as “0820”, as a string. The CSV file is similar to the following;

…csv
id,name,addressLine1,addressLine2,suburb,state,postcode,manager,managerPhone,managerEmail,active
“24a74f5f-d418-4c82-9185-6e0b3fb6742c”,"MX HALLAM Logistics “,“74-80 Melverton Drive”,“Unit 10”,“Hallam”,“Vic”,“3803”,“Barney Rubble”,“1300 116 339”,“Barney.Rubble@Med-Xsolutions.com.au”,1
“50f69c47-8e51-4448-97d6-23b67edc5b38”,“MX NEWCASTLE Logistics”,“194 Cormorant Rd”,” ",“Kooragang”,“NSW”,“2304”,“Fred Flintstone”,“1300 116 339”,“Fred.Flintstone@Med-XSolutions.com.au”,1
…

The error displayed is “Depot.postcode must be of type ‘string’, got ‘number’ (3803)”. How can I force the import procedure to recognise this as a string?

2

Hi @Raymond_Brack,

Step 1

Transform the CSV header line so it looks like this:

id.string(),name.string(),addressLine1.string(),addressLine2.string(),suburb.string(),state.string(),postcode.string(),manager.string(),managerPhone.string(),managerEmail.string(),active.boolean()
"24a74f5f-d418-4c82-9185-6e0b3fb6742c","MX HALLAM Logistics ","74-80 Melverton Drive","Unit 10","Hallam","Vic","3803","Barney Rubble","1300 116 339","Barney.Rubble@Med-Xsolutions.com.au",1
"50f69c47-8e51-4448-97d6-23b67edc5b38","MX NEWCASTLE Logistics","194 Cormorant Rd"," ","Kooragang","NSW","2304","Fred Flintstone","1300 116 339","Fred.Flintstone@Med-XSolutions.com.au",1

Step 2

mongoimport --drop -d test -c coll --type csv --headerline --columnsHaveTypes --file lol.csv 
2021-02-08T18:27:19.100+0100	connected to: mongodb://localhost/
2021-02-08T18:27:19.100+0100	dropping: test.coll
2021-02-08T18:27:19.133+0100	2 document(s) imported successfully. 0 document(s) failed to import.

Result

test:PRIMARY> db.coll.find().pretty()
{
	"_id" : ObjectId("6021747779ecf1295f3ab01c"),
	"id" : "24a74f5f-d418-4c82-9185-6e0b3fb6742c",
	"name" : "MX HALLAM Logistics ",
	"addressLine1" : "74-80 Melverton Drive",
	"addressLine2" : "Unit 10",
	"suburb" : "Hallam",
	"state" : "Vic",
	"postcode" : "3803",
	"manager" : "Barney Rubble",
	"managerPhone" : "1300 116 339",
	"managerEmail" : "Barney.Rubble@Med-Xsolutions.com.au",
	"active" : true
}
{
	"_id" : ObjectId("6021747779ecf1295f3ab01d"),
	"id" : "50f69c47-8e51-4448-97d6-23b67edc5b38",
	"name" : "MX NEWCASTLE Logistics",
	"addressLine1" : "194 Cormorant Rd",
	"addressLine2" : " ",
	"suburb" : "Kooragang",
	"state" : "NSW",
	"postcode" : "2304",
	"manager" : "Fred Flintstone",
	"managerPhone" : "1300 116 339",
	"managerEmail" : "Fred.Flintstone@Med-XSolutions.com.au",
	"active" : true
}

Note

Because I can handle any type I want with this, I can deal with the boolean “active” for example.

Another option:

If you are looking for an over engineered CSV Bazooka, you can give this a shot! I did this for a hackathon and it’s capable to handle any CSV you throw at it. It can regroup column together (if you have a date split on multiple columns day, month, year for example) and you can basically execute python code for each column so the parsing can be a lot smarter.

Cheers,
Maxime.

3

Is this a question about importing data using Realm Studio or something else?

I am asking because I took your data and condensed it to this to isolate the postal code

state,postcode,manager
“Vic”,“3803”,“Barney Rubble”
“NSW”,“2304”,“Fred Flintstone”

I then created a matching Realm object in my Swift project

class Depot: Object {
    @objc dynamic var state = ""
    @objc dynamic var postcode = ""
    @objc dynamic var manager = ""
}

Lastly, I ran the project with created the Realm file with no Depot objects, then opened Realm Studio and imported the file (named Depot.csv) and it worked correctly.

import
import2236×400 56.8 KB

So there may be some other issue in the file you’re importing

4

My bad - I should have noted that I am using Realm Studio on Windows to import the data.

5

Oops I didn’t notice the “MongoDB Realm” topic :smiley:.
That being said, if you can script with mongoimport, it will be faster I think.