Conditionally add a property to a node when importing CSV with empty spaces

[shanna]

I am importing a CSV file with empty spaces. My aim is to conditionally add a value as a property to a node. The condition is that the cell in the CSV is not empty. I want to keep the CSV file as it is, so I am looking here for solutions that apply to the code.

I tried the following code with the FOREACH clause. My aim is to merge the corop20_loc3 value in the CSV (in this case a .txt, see attachment) with the o:Onderneming node, but only if that cell is not empty. If it is empty, nothing needs to be done.

//Load dn-datamodel-test1.7
LOAD CSV WITH HEADERS FROM "file:///dn-datamodel-test5.txt" AS row FIELDTERMINATOR ";"
MERGE (o:Onderneming {Onderneming:row.ondernemingKort,
	   FOREACH (x IN CASE WHEN row.corop20_loc3 IS NULL THEN [] ELSE [1] END |
		         MERGE (o:Onderneming {COROPgebied:row.corop20_loc3}))})
FOREACH (x IN CASE WHEN row.sw1 IS NULL THEN [] ELSE [1] END | 
		 MERGE (k1:Kernwaarde1 {Kernwaarde:row.sw1})
		 MERGE (o)-[:BEDRIJFT]->(k1))
FOREACH (x IN CASE WHEN row.sw2 IS NULL THEN [] ELSE [1] END |
		 MERGE (k2:Kernwaarde2 {Kernwaarde:row.sw2})
		 MERGE (o)-[:BEDRIJFT]->(k2))
FOREACH (x IN CASE WHEN row.sw3 IS NULL THEN [] ELSE [1] END |
		 MERGE (k3:Kernwaarde3 {Kernwaarde:row.sw3})
		 MERGE (o)-[:BEDRIJFT]->(k3))
FOREACH (x IN CASE WHEN row.sw4 IS NULL THEN [] ELSE [1] END |
		 MERGE (k4:Kernwaarde4 {Kernwaarde:row.sw4})
		 MERGE (o)-[:BEDRIJFT]->(k4))    
FOREACH (x IN CASE WHEN row.sw5 IS NULL THEN [] ELSE [1] END |
		 MERGE (k5:Kernwaarde5 {Kernwaarde:row.sw5})
		 MERGE (o)-[:BEDRIJFT]->(k5))
FOREACH (x IN CASE WHEN row.sw6 IS NULL THEN [] ELSE [1] END |
		 MERGE (k6:Kernwaarde6 {Kernwaarde:row.sw6})
		 MERGE (o)-[:BEDRIJFT]->(k6))
FOREACH (x IN CASE WHEN row.sw7 IS NULL THEN [] ELSE [1] END |
		 MERGE (k7:Kernwaarde7 {Kernwaarde:row.sw7})
		 MERGE (o)-[:BEDRIJFT]->(k7))
FOREACH (x IN CASE WHEN row.sw8 IS NULL THEN [] ELSE [1] END |
		 MERGE (k8:Kernwaarde8 {Kernwaarde:row.sw8})
		 MERGE (o)-[:BEDRIJFT]->(k8))    
FOREACH (x IN CASE WHEN row.sw9 IS NULL THEN [] ELSE [1] END |
		 MERGE (k9:Kernwaarde9 {Kernwaarde:row.sw9})
		 MERGE (o)-[:BEDRIJFT]->(k9))   
FOREACH (x IN CASE WHEN row.sw10 IS NULL THEN [] ELSE [1] END |
		 MERGE (k10:Kernwaarde10 {Kernwaarde:row.sw10})
		 MERGE (o)-[:BEDRIJFT]->(k10))   
FOREACH (x IN CASE WHEN row.sw11 IS NULL THEN [] ELSE [1] END |
		 MERGE (k11:Kernwaarde11 {Kernwaarde:row.sw11})
		 MERGE (o)-[:BEDRIJFT]->(k11))  
FOREACH (x IN CASE WHEN row.sw12 IS NULL THEN [] ELSE [1] END |
		 MERGE (k12:Kernwaarde12 {Kernwaarde:row.sw12})
		 MERGE (o)-[:BEDRIJFT]->(k12))  
FOREACH (x IN CASE WHEN row.sw13 IS NULL THEN [] ELSE [1] END |
		 MERGE (k13:Kernwaarde13 {Kernwaarde:row.sw13})
		 MERGE (o)-[:BEDRIJFT]->(k13))  
FOREACH (x IN CASE WHEN row.sw14 IS NULL THEN [] ELSE [1] END |
		 MERGE (k14:Kernwaarde14 {Kernwaarde:row.sw14})
		 MERGE (o)-[:BEDRIJFT]->(k14))  
FOREACH (x IN CASE WHEN row.sw15 IS NULL THEN [] ELSE [1] END |
		 MERGE (k15:Kernwaarde15 {Kernwaarde:row.sw15})
		 MERGE (o)-[:BEDRIJFT]->(k15))
FOREACH (x IN CASE WHEN row.loc1 IS NULL THEN [] ELSE [1] END |
		 MERGE (l1:Locatie1 {Locatie:row.loc1})
		 MERGE (o)-[:LOKATIE]->(l1))
FOREACH (x IN CASE WHEN row.loc2 IS NULL THEN [] ELSE [1] END |
		 MERGE (l2:Locatie2 {Locatie:row.loc2})
		 MERGE (o)-[:LOKATIE]->(l2))

[mdfrenchman]

@shanna have you tried using a WITH CASE pattern to do the conditional?
That would work for picking the different row field. However, the different label would require an additional OPTIONAL MATCH.

Performance wise, the recommendation in the past was to LOAD CSV multiple times instead of the FOREACH due to memory usage. That may have changed in 4.x

[john_allen]

slightly off-topic.. but any clues how on the Cyhper to transpose the relationships in mongo.. arrays of objectids.. into a relationship map in neo ?

here is a many to 1.. example.. welcome suggestions ? thanks guys

_id,_t,name,description,classifies,categorizedBy,issourcedFrom
5f77a307e74f033e954fa441,DataHolonType,Geospatial,Land surface mappings,"[{""$oid"":""5f77a307e74f033e954fa440""}]","[{""$oid"":""5f77a308e74f033e954fa447""},{""$oid"":""5f77a308e74f033e954fa448""},{""$oid"":""5f77a308e74f033e954fa449""},{""$oid"":""5f77a308e74f033e954fa44a""},{""$oid"":""5f77a308e74f033e954fa44b""}]","[{""$oid"":""5f77a307e74f033e954fa442""},{""$oid"":""5f77a308e74f033e954fa443""}]"
5f7cbe9b4db2373d3ce40223,DataHolonType,Enterprise,Human business actvity,"[{""$oid"":""5f875ee96c28101dc2104b15""},{""$oid"":""5f89b33a37f31b0752229f8c""}]",,
5f7cbe9c4db2373d3ce40224,DataHolonType,Audio,Auditory streams,,,
5f7cbe9c4db2373d3ce40225,DataHolonType,Visual,Visual streams,,,
5f7cbe9d4db2373d3ce40226,DataHolonType,Weather,Bio-sphere activity,,,
5f7cbe9d4db2373d3ce40227,DataHolonType,Sensor,Machine sensory,,,
5f7cbe9d4db2373d3ce40228,DataHolonType,Cosmological,Scientific,,,
5f7cbe9d4db2373d3ce40229,DataHolonType,Socio Culture,Society across cultures,,,
5f7cbe9d4db2373d3ce4022a,DataHolonType,Contracts,Legal framework of Societies,,,
5f7cbe9d4db2373d3ce4022b,DataHolonType,Cognitive,Cognitive Science.. HCI/perceptional,,,
5f7cbe9d4db2373d3ce4022c,DataHolonType,Human Experience,Psychology / NLP,,,

related by object id to ...

_id,_t,name,description,inactive,exhibitedIn,classifiesTo
5f77a308e74f033e954fa447,DataHolonTypeProperty,Scope-Scale,"Categories, levels, etc ..",false,"[{""$oid"":""5f77a307e74f033e954fa441""}]","[{""$oid"":""5f77a309e74f033e954fa44c""},{""$oid"":""5f77a309e74f033e954fa44d""},{""$oid"":""5f77a309e74f033e954fa44e""},{""$oid"":""5f77a309e74f033e954fa44f""},{""$oid"":""5f77a309e74f033e954fa450""},{""$oid"":""5f77a309e74f033e954fa451""},{""$oid"":""5f77a309e74f033e954fa452""}]"
5f77a308e74f033e954fa448,DataHolonTypeProperty,Models,"Arrangements, Structures, etc ..",false,"[{""$oid"":""5f77a307e74f033e954fa441""}]","[{""$oid"":""5f8462dff785c30b717cb132""},{""$oid"":""5f8462dff785c30b717cb133""},{""$oid"":""5f8462dff785c30b717cb134""},{""$oid"":""5f8462dff785c30b717cb135""},{""$oid"":""5f8462dff785c30b717cb136""},{""$oid"":""5f8462dff785c30b717cb137""},{""$oid"":""5f8462dff785c30b717cb138""}]"
5f77a308e74f033e954fa449,DataHolonTypeProperty,Algorithms,"Operations, Processes, etc ..",false,"[{""$oid"":""5f77a307e74f033e954fa441""}]","[{""$oid"":""5f77a309e74f033e954fa453""},{""$oid"":""5f77a309e74f033e954fa454""},{""$oid"":""5f77a309e74f033e954fa455""},{""$oid"":""5f77a309e74f033e954fa456""},{""$oid"":""5f77a309e74f033e954fa457""},{""$oid"":""5f77a309e74f033e954fa458""}]"
5f77a308e74f033e954fa44a,DataHolonTypeProperty,Results,"Data, Outputs, Snapshots, etc ..",false,"[{""$oid"":""5f77a307e74f033e954fa441""}]","[{""$oid"":""5f84563a472781763f2548fe""},{""$oid"":""5f84563a472781763f2548ff""},{""$oid"":""5f84563a472781763f254900""},{""$oid"":""5f84563a472781763f254901""},{""$oid"":""5f84563a472781763f254902""},{""$oid"":""5f84563a472781763f254903""},{""$oid"":""5f84563a472781763f254904""}]"
5f77a308e74f033e954fa44b,DataHolonTypeProperty,Transactions,"Connections, Interactions ..",false,"[{""$oid"":""5f77a307e74f033e954fa441""}]","[{""$oid"":""5f8458d18b4129711c7de847""},{""$oid"":""5f8458d18b4129711c7de848""},{""$oid"":""5f8458d18b4129711c7de849""},{""$oid"":""5f8458d18b4129711c7de84a""},{""$oid"":""5f8458d18b4129711c7de84b""}]"

used this to load one of the above collections.. fine so 2 nodes appeaered.. BUT how to load the objectids as the relationship ?? I'm 2 weeks into CYHPER

CREATE CONSTRAINT ON (dht:Dataholontype) ASSERT dht.id IS UNIQUE;
LOAD CSV WITH HEADERS FROM 'file:///dataholontypes.csv' AS row
WITH row.name AS name, row.description AS description, row._id AS _id
MERGE (dht:Dataholontype {_id:_id})
SET dht.name = name, dht.description = description
RETURN count(dht)

[mdfrenchman]

The array is the relationships start and end node ids?

or something else?

Typically splitting the array and looping over those in a separate load after creating the nodes. For a cypher only approach.

If you use a bolt-driver it'd probably be much easier to create a transaction function. OR create a user procedure if you know Java to take each row and create the necessary items.

[shanna]

Hi Mike,

Thank you so much for your help. I have decided to contact a rdbms developer that can help me set up the proper conditions for the database and making it easy to import into Neo4j. I have tried uploading several CSV files, but this became very messy and I'm pretty sure there are more elegant solutions.

I am leaving this topic open though, so that other users might benefit from your help.

Thanks again, ciao ciao!

[john_allen]

amazed me there is no command to load all the CSV files from import, stipulating they are from mongodb export.. ie suck in the whole directory .. instead it looks like we have click through clunky CLI and name each field.. create.. merge.. etc

of course an easier way is to use the connector.. which I have running mongo-connector with py2neo v5 .. but as usual there is no documented indication of in neo4j desktop of HOW to 'see' the replica set I just connected to ? do we create a new graph db w/password (despite dbms security being turned off in the config).. does the mongo pretty nodes diagram just 'appear' ?? or do we have to click some thing or CLI command ?? nobody seems to know.. Lyons video a few years old.. just magically appears the diagram when he starts the mongo-connector... nothing magically appears when we follow to the letter.. sad to say

[mdfrenchman]

Is importing from mongoDB a common thing for the community?

I'd consider writing something for it if it was. I haven't used mongo but do something similar with a number of other data stores.

[john_allen]

mongo-connector had been around for a few years and someone wrote a doc-manager for it for neo.. We are just hung up on why o why doesnt our mongo changes.. that appear in the mongo clog.. do NOT appear automagically in Neo.. ? or if we need a new graph db with same name as the mongo db or the replica set ? unlcear at this stage.. we have mongo connector working and talking with neo BUT fails to show a graph thus far. working on it.. and hoping for help from the NEO NINJAS !

[john_allen]

yes those objectids in the array are the relationships .. to other mongo objects.. its how to transpose them into CYHPHER so the relationship is mirrored in Graph.

really we just want to get the darn mongo-connector to show our mogodb as a graph.. read-only.. its a one way from mongo to neo via the connector and neo4j-doc-manager. know anyone who has done that ? as we simply cant get the mongo to display as a graph.. a real headache yet so simple. and so close