Reading csv with comparing different rows

[Michel]

Hi,

There is a graph with nodes "City" :

CREATE (a:City {DS: 'AAA'})
CREATE (b:City {DS: 'BBB'})
CREATE (c:City {DS: 'CCC'})
CREATE (d:City {DS: 'DDD'})
CREATE (e:City {DS: 'EEE'})
CREATE (f:City {DS: 'FFF'})
CREATE (g:City {DS: 'GGG'})

The relatioships should be extracted from a  csv, content:

route, DS
66,AAA
66,BBB
66,CCC
66,DDD
77,EEE
77,FFF
77,GGG

and creted the relationships as follows:

The question: How to build relationships based on different (x, x+1) rows ?

Thanks a lot in advance,
Michel

The code for creating the relationships above:

MATCH (p1:City {DS: 'AAA'}), (p2:City {DS: 'BBB'})
CREATE (p1)-[:NB {route: '66'}]->(p2)

MATCH (p1:City {DS: 'BBB'}), (p2:City {DS: 'CCC'})
CREATE (p1)-[:NB {route: '66'}]->(p2)

MATCH (p1:City {DS: 'CCC'}), (p2:City {DS: 'DDD'})
CREATE (p1)-[:NB {route: '66'}]->(p2)

MATCH (p1:City {DS: 'EEE'}), (p2:City {DS: 'FFF'})
CREATE (p1)-[:NB {route: '77'}]->(p2)

MATCH (p1:City {DS: 'FFF'}), (p2:City {DS: 'GGG'})
CREATE (p1)-[:NB {route: '77'}]->(p2)

[glilienfield · Accepted solution]

load csv with headers from "file:///Book1.csv" as row
with collect(row) as rows
unwind range(0,size(rows)-2) as i
match(a:City{DS:rows[i].DS})
match(b:City{DS:rows[i+1].DS})
merge(a)-[r:REL]->(b)
set r.route = rows[i].route

[Michel · Accepted solution]

Hi @glilienfield ,

many thanks !   The relationship betwen DDD and  EEE  doesn't exist.  Have you any idea, how to avoid creating of such relationships ?

Thanks,

Michel

[glilienfield · Accepted solution]

You could place a '-' character in the route column to indicate to skip the relationship.  

[glilienfield · Accepted solution]

load csv with headers from "file:///Book1.csv" as row
with collect(row) as rows
unwind range(0,size(rows)-2) as i
match(a:City{DS:rows[i].DS})
match(b:City{DS:rows[i+1].DS})
merge(a)-[r:REL]->(b)
set r.route = rows[i].route

[Michel · Accepted solution]

Hi @glilienfield ,

many thanks !   The relationship betwen DDD and  EEE  doesn't exist.  Have you any idea, how to avoid creating of such relationships ?

Thanks,

Michel

[glilienfield · Accepted solution]

You could place a '-' character in the route column to indicate to skip the relationship.  

[Michel]

Hi @glilienfield ,  for that I have to write a VBA code (nodes > 20000, routes > 1000) .  In the meanwhile I did it. The only open question for further similary tasks: How can I compare   and reference values from different rows (i, i+1)

Something like this:

CASE
WHEN rows[i].DS = rows[i+1].DS THEN ...

END AS ..

[glilienfield · Accepted solution]

You can alter the code I provided to do that.