Head's Up! These forums are read-only. All users and content have migrated. Please join us at community.neo4j.com.
07-29-2020 10:04 AM
Hi All,
I'm new to Neo4j and trying to figure this out. Any help is appreciated:
Problem: Have two tables: 1) Systems 2) Users. Trying to load the two csv files and create relationships.
Systems table: System ID, System name, Owner, etc., (Ex: System1, SomeSystem, 'Jon Snow'
Users/Access table: System ID, Users, No. of users, etc.,(Ex: System1, (user1, user2, user3), 3)
The issue I'm having is when trying to split the users and create user node and connect via a relationships saying (user)-[:HAS_ACCESS)->(system), it's creating duplicate user nodes or relationship nodes:
Here's what I tries
:auto USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "filepath" as element
MERGE (sys: System {SystemID = element.SystemID})
ON CREATE SET sys += element
LOAD CSV WITH HEADERS FROM "filepath" as usernode
MATCH (sys:System {SystemID:usernode.SystemID})
WITH SPLIT(usernode.UserID,", ") AS users
UNWIND range(0, size(users)-1) as i
MERGE (us:USER {UserID: users[i]})
ON CREATE SET us += usernode
CREATE (us)-[:HAS_ACCESS_TO]->(sys)
Please help
07-29-2020 02:07 PM
It looks like you're using MERGE correctly. Do you have unique ID constraints placed on your instance?
07-29-2020 03:35 PM
Yes I have defined unique constraints on System ID and User ID. I can't use Merge and on create in loading user node, it throws me an error saying usernode is not available, it has something to do with 'WITH' i believe
07-30-2020 05:47 AM
Ok. If possible, do you think you can copy and paste the complete error it's giving you? Also, are you running both of the upload from CSV operations at once? If so you may benefit from running them in separate operations and verifying that your nodes are being created in the way that you expect them to be.
07-30-2020 10:07 AM
I am running them separately and nodes are created. This is the error I'm getting. It has to do with 'WITH' clause, but I couldn't figure out how to resolve
07-30-2020 02:13 PM
You should add usernode to WITH clause as:
WITH SPLIT(usernode.UserID,", ") AS users, user node
This should work,
07-30-2020 02:46 PM
Thank you, tried that as well. It gave very weird output:
07-30-2020 01:08 PM
Hi Rajeev,
Kindly correct your syntax as
Hope you are giving filepath correctly. Your csv shud be placed in <Neo4j_Home>/import folder and for an example file name is a.csv then
LOAD CSV WITH HEADERS FROM "file:///a.csv" as element
MERGE (sys: System {SystemID = element.SystemID}), instead of equal sign use colon. = sign should be using in set as you have done
Also it is not not clear what are you trying with second load
07-30-2020 02:11 PM
Thank you Vivek.
07-30-2020 02:17 PM
could you please provide sample data
07-30-2020 02:44 PM
Code: step1: :auto USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:///System.csv" as element
MERGE (sys: System {SystemID : element.SystemID})
ON CREATE SET sys += element
Step2: LOAD CSV WITH HEADERS FROM "file:///Users.csv" as usernode
MATCH (sys:System {SystemID:usernode.SystemID})
WITH SPLIT(usernode.UserID,", ") AS users
UNWIND range(0, size(users)-1) as i
MERGE (us:USER {UserID: users[i]})
ON CREATE SET us += usernode
CREATE (us)-[:HAS_ACCESS_TO]->(sys)
Error:
07-30-2020 03:25 PM
try
LOAD CSV WITH HEADERS FROM "file:///System.csv" as element
MERGE (sys: System {id : element.SystemID})
LOAD CSV WITH HEADERS FROM "file:///Users.csv" as usernode
WITH SPLIT(usernode.UserID,"|") AS users,usernode
unwind users as usr
Merge(user:USER{id:usr})
with user,usernode
Match (sys:System {id:usernode.SystemID})
Create(user)-[:HAS_ACCESS_TO]->(sys)
07-30-2020 05:17 PM
Thank you Vivke, I tried that, it didn't split the users into different nodes. This is the output:
07-30-2020 08:10 PM
Try this:
LOAD CSV WITH HEADERS FROM "file:/system.csv" AS row
with row
merge (a:System {systemid: row.SystemID, systemname: row.SystemName, owner: row.Owner})
LOAD CSV WITH HEADERS FROM "file:/users.csv" AS row
with row
with split(row.UserID, ',') as u1, row
UNWIND range(0, size(u1)-1) as i
merge (u:User {id: u1[i], usercount: row.`No. of users`})
with u, row
match (s:System) where s.systemid = row.SystemID
merge (u)-[:HAS_ACCESS_To]->(s)
Result:
07-30-2020 09:07 PM
Thank you so much! This is amazing! I tweaked it a little bit to give the exact output I wanted:
//Execure in steps
//Step1
LOAD CSV WITH HEADERS FROM "file:///System.csv" as element
MERGE (sys: System {SystemID : element.SystemID})
ON CREATE SET sys += element
//Step2
LOAD CSV WITH HEADERS FROM "file:///users.csv" AS row
MATCH (sys:System {SystemID:row.SystemID})
with row
with split(row.UserID, ', ') as u1, row
UNWIND range(0, size(u1)-1) as i
merge (u:User {id: u1[i]})
on create set u += row
with u, row
match (sys:System) where sys.SystemID = row.SystemID
merge (u)-[:HAS_ACCESS_To]->(sys)
and Got my desired result:
Appreciate all your help!
All the sessions of the conference are now available online