Null values in csv

Neo4j Graph Platform Cypher
1

I am trying to import a csv file with the following columns

  • Account
  • Opportunity_ID
  • Sales_Agent
  • Deal_Stage
  • Product
  • Close_Date
  • Close_Value
  • Created_On

Problem 1.
Account column has empty cells or null values so neo4j throws an error “Cannot merge node using null property value for Name”.

USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///sales-pipeline.csv" AS row
MERGE(a:Account {Name: row.Account})
SET a.name = row.name

Problem 2
I want to be able to skip empty cells and in addition add Column 8 Created_On as a property to the Account. It should then read as:

Account: ABCDEFGH
Created_On: 2017-04-01

MY EFFORTS SO FAR
I have come across different approaches but I am still not able to achieve my goal. This is the solution I have tried.

USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///sales-pipeline.csv" AS row
FOREACH (x IN CASE WHEN row.Account IS NULL THEN ELSE [1] END |MERGE(a:Account {Name:row.Account, Create_date: row.Created_On})
SET a.name = row.Name

Handling%20Null%20Values
Handling Null Values.JPG852×197 44.8 KB

1 Like
2

Hi,

Try replacing your FOR EACH statement with this:

FOREACH(ignoreMe IN CASE WHEN row.Account IS NOT NULL THEN [1] ELSE END |

-Kamal

2 Likes
3

The code now reads as follows

USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///sales-pipeline.csv" AS row
FOREACH(ignoreMe IN CASE WHEN row.Account IS NOT NULL THEN [1] ELSE END |
MERGE(a:Account {Name:row.Account, Create_date: row.Created_On})
SET a.name = row.Name

But I still get an error

Capture
Capture.JPG825×264 28.9 KB

4

Hi,

FOR EACH statement is missing the closing bracket ')' at the end:

USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///sales-pipeline.csv" AS row
FOREACH(ignoreMe IN CASE WHEN row.Account IS NOT NULL THEN [1] ELSE END |
MERGE(a:Account {Name:row.Account, Create_date: row.Created_On})
SET a.name = row.Name
)
-Kamal

5

you can just filter out rows with null values as merge keys

USING PERIODIC COMMIT 10000
LOAD CSV WITH HEADERS FROM "file:///sales-pipeline.csv" AS row
WITH row WHERE NOT row.Account IS null
MERGE(a:Account {Name: row.Account})
SET a.name = row.name
4 Likes
6

Thanks very much Michael. Its awesome!

7

Thanks for your help.

8

Don't merge on non-id-keys.

just merge on name in your case and also make sure that you have a constraint created for it:

create constraint on (a:Account) assert a.Name is unique;

MERGE(a:Account {Name:row.Account})
ON CREATE SET a.Create_date = row.Created_On;
1 Like
9

What if I have NULL for a field that isn't an index, just a property? I'd like to set the value to 'No' in this case, or FALSE.

10

you can use coalesce(row.key, false) there

In general we don't recommend that as it just wastes space in the db
and you can reconstitute default values if needed.

Michael

2 Likes