Neo4j LOAD CSV..when CSV columns contains a list of properties

561 views Asked by At

This is regarding neo4j csv import using LOAD csv. Suppose my csv file format is as following.

Id, OID, name, address, Parents , Children
1, mid1, ratta, hello@aa, ["mid250","mid251","mid253"], ["mid60","mid65"]
2, mid2, butta, ado@bb, ["mid350","mid365","mid320", "mid450","mid700"], ["mid20","mid25","mid30"]
3, mid3, natta, hkk@aa, ["mid50","mid311","mid543"], []

So the parents and children columns consists of mids basically..while importing csv into neo4j using LOAD CSV.. I want to create following nodes and relationships.

  1. NODES for each rows (for each id column in csv)

  2. [:PARENT] relationship by matching the OID property in each row and OID properties inside parents column. So as a example when processing the first row...there should be four nodes (mid1, mid250,mid 251 and mid 253) and 3 PARENT relationship between mid1 and other 3 nodes.

  3. [: CHILD ] relationship by matching the OID property in each row and OID properties inside children column.

Please help!!

Tried doing it with for each function but the results didn't come correctly. Im doing it through a python script. just need to edit the cypher query.

def create_AAA(tx):
    tx.run(
        "LOAD CSV WITH HEADERS FROM 'file:///aaa.csv' AS row MERGE (e:AAA {id: row._id,OID: row.OID,address: row.address,name: row.name})"
    )

def create_parent(tx):
    tx.run(
        "LOAD CSV WITH HEADERS FROM 'file:///aaa.csv' AS row MERGE (a:AAA {OID: row.OID}) FOREACH (t in row.parents | MERGE (e:AAA {OID:t}) MERGE (a)-[:PARENT]->(e) )"
    )

def create_child(tx):
    tx.run(
        "LOAD CSV WITH HEADERS FROM 'file:///aaa.csv' AS row MERGE (a:AAA {OID: row.OID}) FOREACH (t in row.children | MERGE (e:AAA {OID:t}) MERGE (a)-[:CHILD]->(e) )"
    )

with driver.session() as session:
    session.write_transaction(create_AAA)
    session.write_transaction(create_parent)
    session.write_transaction(create_child)
2

There are 2 answers

4
jose_bacoy On BEST ANSWER

Please follow the instructions below:

  1. Change the column names of Parents and Children into parents and children since neo4j is case sensitive.
  2. Remove the spaces in your csv file so that you don't need to do trim () on each columns in the csv.
  3. In your parents and children columns, remove the commas on the string list because it is causing an error. OR use another delimiter and not comma. In my example, I used space as delimiter.
  4. Below script will remove the quotes and [] characters then convert the string list into a list (using split() function)
  5. Do the same for create child function.
LOAD CSV WITH HEADERS FROM 'file:///aaa.csv' AS row 
MERGE (a:AAA {OID: row.OID}) 
FOREACH (t in split(replace(replace(replace(row.parents,'[', ''),']', ''),'"', ''), ' ') | 
           MERGE (e:AAA {OID:t}) MERGE (a)-[:PARENT]->(e) )

See sample csv here:

Id,OID,name,address,parents,children
1,mid1,ratta,hello@aa,["mid250" "mid251" "mid253"],["mid60" "mid65"]
2,mid2,butta,ado@bb,["mid350" "mid365" "mid320" "mid450" "mid700"],["mid20" "mid25" "mid30"]
3,mid3,natta,hkk@aa,["mid50" "mid311" "mid543"],[]

See sample result here: enter image description here

5
Nirmana On
LOAD CSV WITH HEADERS FROM 'file:///aaa.csv' AS row
WITH row WHERE row.children <>"[]"  
MERGE (a:AAA {OID: row.OID}) 
FOREACH (t in split(replace(replace(replace(row.children,'[', ''),']', ''),'"', ''), ' ') | 
MERGE (e:AAA {OID:t}) MERGE (a)-[:CHILD]->(e) )

Now it works fine.

enter image description here