I'm using APOC to import the contents of an Excel file. Where I'm having issues is when a NULL value is encountered during the import. For example if the data looked like this in Excel:
NAME DATE COMPANY
WIndows 10 2015-01-01 Microsoft
Windows 7 null (empty cell) Microsoft
RHEL 6 2016-05-01 Red Hat
When parsing the file, I perform a date conversion (using date()) but it will fail due to the null value in the second entry. I've tried various conditional attempts with CASE, NOT NULL, etc., but still have issues.
Basically, if the cell does NOT have a null value then process the contents otherwise process the next cell. Do I need to do a multiple pass to make this work instead of trying to handle things on a single pass?
You have a common issue a lot of people have; so usually what people will do is replace all nulls with some default value like an empty string -- this effectively eliminates nulls and lets you treat them as if they are some default string of your choosing.
Example:
UNWIND batch AS event
MERGE (r:Record { id: event.id })
SET r.date = date(coalesce(event.date, "1970-01-01"))
Here, event refers to any line of data you've got. If event.date is null, you'll get January 1st 1970
Thanks for the example. I haven't used the coalesce() function before but it looks to be very useful.
So for a follow-up on your solution. If the date was null, then the default is applied, could I then use the REMOVE function to then delete the properties where the default is applied, just to keep things clean?
So how can I trap a null in order to MERGE a new node and apply the relationship? For example, if the spreadsheet had a Jira ticket number in some of the rows, I would like to create a new node with the Jira info (ID, URL, etc.), but that needs to be done conditionally (only if a ticket entry is present). A ticket may apply to more than one row in this case.