I have some Cypher code (test code) that uses APOC to load an Excel file and then process each row. What I want to do is this:
call apoc.load.xls('Test File.xlsx', 'Company',
// the file has a header row
{header:true})
yield map as row
with row
MERGE (c:Company)
ON CREATE
SET
c.name = coalesce(ltrim(rtrim(toString(row.Company))), "UKNOWN"),
c.created = datetime()
ON MATCH
SET c.lastUpdated = datetime()
RETURN c
The problem is I only get 1 node, but have 5 distinct rows of data. It seems to be only processing the first row in the Excel file. Any ideas?
It is normal because you didn't specify an id property in the MERGE clause so your query always MERGE on the same node. Do you have a unique id for each row?
No. Each row may or may not have the same information I'm looking for, hence the need to split out the ON MATCH vs. ON CREATE function. If I don't use those, the script will iterate through each row just fine so I'm a little confused about how to proceed.
MERGE (c:Company {name: coalesce(ltrim(rtrim(toString(row.`Company`))), "UKNOWN"))
ON CREATE
SET c.created = datetime()
ON MATCH
SET c.lastUpdated = datetime()
RETURN c
One follow up question. For another node, there are multiple properties but the name property has a constraint. Should I only use the name property as the id and then set the others using the ON MATCH and ON CREATE?