How to check empty strings for all property values when loading csv?

Is there a generic solution to omit all node properties that are empty strings? Each node type has different properties, and it would be hard to explicitly check the null/empty of each property. Any property except ID value could be absent. Currently, my code create empty string property value like this when loading from csv:
"foreign_name": ""

This is my code:

CALL apoc.load.csv('coach.csv') 
yield map as row 
MERGE (m:Coach {ID: row.ID}) ON CREATE SET m += row ON MATCH SET m += row
RETURN count(m) as mcount

How to omit empty string property before this MERGE statement?

Given the example in another question:
SET c += row { .*, emailAddress: CASE trim(row.Email) WHEN "" THEN null ELSE row.Email END }
I need a way to somehow create a new row which sets empty strings as null, not only for an explicit property, i.e. row.Email.

I tried adding a WITH clause before the MERGE, but it reports an error:

WITH = "\nWITH [(key, value) in row WHERE value = '' | row.key=NULL] as row"

I want to do something like this, but the cypher syntax not right:

WITH [key in keys(row) where p=CASE trim(row[key]) WHEN "" THEN null ELSE row[key] END | p] as row
return row

what do you mean by "omit"? you mean skip?
"it reports an error" - which error?
"cypher syntax not right:" - what is not right?
"\nWITH" why are you using \n? If this part of the APOC case.do? if yes, you need to quote all your special characters, \ ' " included

If there a reason why you would want to do this at load?

As from my experience, trying to do stuff on load is not the fastest and actually the easiest way.
I would say to actually load add nodes from the CSV as they are and process them afterwards - it's faster. And easier, as you already have all the values and you are on the right track using keys(row) and row[key]. But i don't think you can set the value of a property as null


"Likewise, the following two queries result in the removal of the property:

MATCH (p:Person)
WHERE p.id = $personId
SET p.email = null

MATCH (p:Person)
WHERE p.id = $personId
REMOVE p.email"

Also check: https://neo4j.com/docs/cypher-manual/current/syntax/working-with-null/

So, instead of null, you could set it to "mynullstringvalue" or "-1" or something which you would be sure you won't find in your file and check versus this constant.

I just found that this actually worked:

CALL apoc.load.csv('coach.csv', {nullValues:['','na',false]})) 
yield map as row

The nullValues parameter makes sure that no empty strings value are created for the properties.

1 Like