Null values in csv


(Strato Bayitaa) #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


(Ameyasoft) #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


(Strato Bayitaa) #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


(Ameyasoft) #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


(Michael Hunger) #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


(Strato Bayitaa) #6

Thanks very much Michael. Its awesome!


(Strato Bayitaa) #7

Thanks for your help.


(Michael Hunger) #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;