How to combine these statements into one?

[fgksgf]

I try to import a CSV file to Neo4j and create some relationships.
The headers of the file are: id1, id2, VIN1, VIN2.

The statements i wrote:

load csv with headers from "file:///file.csv" as line
match (p1:Person{id:line.ID1}), (c1:Car{id:line.VIN1}) 
merge (p1)-[:DRIVE]->(c1)

load csv with headers from "file:///file.csv" as line
match (p2:Person{id:line.ID2}), (c2:Car{id:line.VIN2}) 
merge (p2)-[:DRIVE]->(c2)

load csv with headers from "file:///file.csv" as line
match (c3:Car{id:line.VIN1}), (c4:Car{id:line.VIN2}) 
merge (c3)-[:HIT]->(c4)

I think it's too wordy, so i wonder if there is a way to combine those into one statement ?

[ameyasoft]

Here is the solution:

load csv with headers from "file:///file.csv" as line

//Match persons and cars...............
MATCH (p1:Person{id:line.ID1}), (c1:Car{id:line.VIN1})
MATCH (p2:Person{id:line.ID2}), (c2:Car{id:line.VIN2})

//Add relationships.............
MERGE (p1)-[:DRIVE]->(c1)
MERGE (p2)-[:DRIVE]->(c2)
MERGE (c1)-[:HIT]->(c2)
;

You can create nodes and relationships all in one shot:

load csv with headers from "file:///file.csv" as line

//Create nodes.....................
MERGE (p1:Person{id:line.ID1})
MERGE (c1:Car{id:line.VIN1})
MERGE (p2:Person{id:line.ID2})
MERGE(c2:Car{id:line.VIN2})

//Add relationships...........
MERGE (p1)-[:DRIVE]->(c1)
MERGE (p2)-[:DRIVE]->(c2)
MERGE (c1)-[:HIT]->(c2)
;

[fgksgf]

Thanks for your reply. It's very kind of you.

Your statement can run but does not achieve the effect I want. It does not add any nodes and relationships, which confused me.

The content of the file like this:

VIN1	VIN2	ID1	ID2
43B6PK910A	(null)	(null)	(null)
L47DE59170	LA8LC2B867	(null)	15010319
L4CB162015	L0000000000	(null)	72010075
D9CB162015	FM03265775	(null)	(null)

The record of each line means that a car whose id is VIN1 hit another car whose id is VIN2. The person whose id is ID1 is the driver of the first car, and the person whose id is ID2 drove the second car when the accident happened.

There are some missing values, so i can't create any relationships based on the first row (no DRIVE and no HIT).

Do you have any idea about that ?

[ameyasoft · Accepted solution]

Here is how you treat null values.I created a csv file with your data and imported into Neo4j. Here is the Cypher script:

LOAD CSV WITH HEADERS FROM "file:/fgksgf.csv" As line
WITH line

//Case: no null values.....................

FOREACH(ignoreMe IN CASE WHEN line.VIN1 IS NOT NULL AND line.ID1 IS NOT NULL THEN [1] ELSE END |

FOREACH(ignoreMe IN CASE WHEN line.VIN2 IS NOT NULL AND line.ID2 IS NOT NULL THEN [1] ELSE END |

MERGE (p1:Person{id:line.ID1})
MERGE (c1:Car{id:line.VIN1})
MERGE (p2:Person{id:line.ID2})
MERGE(c2:Car{id:line.VIN2})

MERGE (p1)-[:DRIVE]->(c1)
MERGE (p2)-[:DRIVE]->(c2)
MERGE (c1)-[:HIT]->(c2)

)

)

//Case:ID1 is null...lines 1, 2 and 3..........................

FOREACH(ignoreMe IN CASE WHEN line.VIN1 IS NOT NULL AND line.ID1 IS NULL THEN [1] ELSE END |

MERGE (c1:Car{id:line.VIN1})

FOREACH(ignoreMe IN CASE WHEN line.VIN2 IS NOT NULL AND line.ID2 IS NOT NULL THEN [1] ELSE END |

MERGE (p2:Person{id:line.ID2})
MERGE(c2:Car{id:line.VIN2})
MERGE (p2)-[:DRIVE]->(c2)

)

)

//Case:ID1 and ID2 are null...................line 4................
FOREACH(ignoreMe IN CASE WHEN line.VIN1 IS NOT NULL AND line.ID1 IS NULL THEN [1] ELSE END |

FOREACH(ignoreMe IN CASE WHEN line.VIN2 IS NOT NULL AND line.ID2 IS NULL THEN [1] ELSE END |

MERGE (c1:Car{id:line.VIN1})
MERGE (c2:Car{id:line.VIN2})

)
)
;

Result: