Loop through CSV and match/create a relationship based on a variable

[laurence_bentle]

Hello all,

I've just (this weekend) found Neo4j so thanks in advance for considering my remedial question, and while I have searched I've not been able to find an answer but also realise that I may be asking the wrong question, so sorry if I am.

Anyhow, I'm trying to use it to visualise some information to show who's accessed/interacted with systems via a simple CSV log. The CSV has the following columns:-

1. Source
2. Destination
3. User
4. Protocol
5. Count
6. Time
7. Date

Now I've gotten the basic Nodes created (s:Source), (d:Destination), (u:User) and have been able to build the relationship for the user to source and user to destination; but I can't seem to get it to work for the source to destination as the match is not able to match on both (source and destination). So I was wondering if there was a way to loop through the csv and for each source and destination pairing and automatically build the relationship?

I've included some example data incase its not clear:-

Source | Destination | User | Protocol | Count | Time | Date
PC-1. |. SRV-1. | Bob. | NTLM |. 10. | 1010 | 010120
PC-2. |. SRV-1. | Alice| ICMP. | 10. |.1010 | 010120
PC-1. |. SRV-2. | Bob. | NTLM |. 1. | 1110 | 010120
PC-1. |. PC-2 . | Bob. | NTLM |. 3. | 1111 | 010120

So as I've said what I would like to do is create a relationship between all the sources and destinations but I'v not been able to find a way. I've look at using FOREACH and trying to read the csv into a loop/array but don't have the needed knowledge so would very much welcome any help.

[ganesh_sharma67]

This may help..

USING PERIODIC COMMIT 500
LOAD CSV WITH HEADERS FROM "file:///access_data_log.csv" AS csvLine
MATCH(s:Source{id:csvLine.src_id}),
(d:Destination{id:csvLine.dest_id)})
MERGE(s)-[:ACCESS{count:csvLine.count, user: csvLine.user}]->(d)

[intouch_vivek]

Create Nodes
USING PERIODIC COMMIT 500
LOAD CSV WITH HEADERS FROM "file:///test.csv" AS csvLine
CREATE(s:Source{id_1:csvLine.Source})
CREATE(d:Destination{id_1:csvLine.Destination})
CREATE(u:User{name:csvLine.User})

Merge Nodes : To manage duplicacy
MATCH (source:Source)
WITH source.id_1 as id, collect(source) as nodes
CALL apoc.refactor.mergeNodes(nodes, {properties: "combine"}) YIELD node
RETURN node;

MATCH (destination:Destination)
WITH destination.id_1 as id, collect(destination) as nodes
CALL apoc.refactor.mergeNodes(nodes, {properties: "combine"}) YIELD node
RETURN node;

MATCH (user:User)
WITH user.name as name, collect(user) as nodes
CALL apoc.refactor.mergeNodes(nodes, {properties: "combine"}) YIELD node
RETURN node;

Add relationship:
MERGE(u:User)-[:SOURCE]->(s:Source);
MERGE(u:User)-[:DESTINATION]->(d:Destination)

[laurence_bentle]

Many thanks for taking the time to look at my issue and the respond. I was wondering if you were able to explain the syntax and usage.

Thanks once again.

[intouch_vivek]

//Will commit the transaction after every 500 records
USING PERIODIC COMMIT 500
//Load CSV. I saved your sample file into test.csv https://neo4j.com/developer/guide-import-csv/
//I create Node Label Source and Destination with property as id_1 and node User with property as Name
LOAD CSV WITH HEADERS FROM "file:///test.csv" AS csvLine
CREATE(s:Source{id_1:csvLine.Source})
CREATE(d:Destination{id_1:csvLine.Destination})
CREATE(u:User{name:csvLine.User})

//Merge Nodes : To manage duplicacy
//Merge all the nodes of Source type having same ID. http://neo4j-contrib.github.io/neo4j-apoc-procedures/3.5/graph-refactoring/merge-nodes/
MATCH (source:Source)
WITH source.id_1 as id, collect(source) as nodes
CALL apoc.refactor.mergeNodes(nodes, {properties: "combine"}) YIELD node
RETURN node;

//Merge all the nodes of User type having same ID
MATCH (destination:Destination)
WITH destination.id_1 as id, collect(destination) as nodes
CALL apoc.refactor.mergeNodes(nodes, {properties: "combine"}) YIELD node
RETURN node;

//Merge all the nodes of User type having same Name
MATCH (user:User)
WITH user.name as name, collect(user) as nodes
CALL apoc.refactor.mergeNodes(nodes, {properties: "combine"}) YIELD node
RETURN node;

//Add relationship:
//Upsert Relationship between User and Source
MERGE(u:User)-[:SOURCE]->(s:Source)
//Upsert Relationship between User and Destination
MERGE(u:User)-[:DESTINATION]->(d:Destination)