Import a spreadsheet with multiple columns that may not have data for every row into Neo4j?

Projects & Collaboration
1

Objectives - To add each values in the columns as a node. Each column represents a label type. The colored column is a property of Field column. The query I used to ingest this into Neo4j Aura is,

LOAD CSV WITH HEADERS FROM "https://docs.google.com/spreadsheets/d/e/2PACX-1vTlOK8lOIzMR1E6YB-KDqMwsCSSrd/pub?output=csv" AS line MERGE (m:Module {name: line.Module}) WITH m, line MERGE (m)-[:CONTAINS_SUBMODULE]->(s:SubModule {name: line.SubModule}) WITH s, line MERGE (s)-[:CONTAINS_MENU]->(m:Menu {name: line.Menu}) WITH m, line WHERE line.SubMenu IS NOT NULL MERGE (m)-[:CONTAINS_SUB_MENU]->(sm:SubMenu{name:line.SubMenu}) WITH sm, line WHERE line.Screen IS NOT NULL MERGE (sm)-[:LAUNCHES]->(s:Screen{name:line}) WITH s, line WHERE line.Panel IS NOT NULL MERGE (s)-[:CONTAINS_PANEL]->(p:Panel{name:line}) WITH p, line WHERE line.SubScreen IS NOT NULL MERGE (p)-[:CONTAINS_SUBSCREEN]->(ss:SubScreen{name:line}) WITH ss, line WHERE line.Field IS NOT NULL MERGE (ss)-[:CONTAINS_FIELD]->(f:Field{name:line}) WITH f, line WHERE line.Button IS NOT NULL MERGE (f)-[:CONTAINS_BUTTON]->(b:Button{name:line})

It worked fine till I attempted to map the SubMenu with the Screen column. It threw the error, Property values can only be of primitive types or arrays thereof. Encountered: Map{Panel -> String("Search"), Menu -> String("Block Status"), SubModule -> String("Booking"), SubMenu -> String("Status Codes"), Button -> NO_VALUE, Field -> NO_VALUE, SubScreen -> NO_VALUE, Mandatory Field -> NO_VALUE, Screen -> String("Status Codes"), NodeID -> String("115"), Module -> String("Administration")}.

Is there a more efficient way to add this spreadsheet into Neo4j Aura?

image.png
image.png997×266 151 KB

2

As a general trick that works really well in these kinds of import functions, check out the coalesce() function in cypher. It returns the first non-null value.

I use this all the time in LOAD CSV in order to remove missing values and populate with "defaults" if something is missing.

In your LOAD CSV you're filtering rows out where certain things are null, but you could also do something like this (greatly simplified example)

LOAD CSV FROM 'foo.csv' AS line
MERGE (r:Record { id: line.id, value: coalesce(line.value, "DEFAULT") })